access 2003 to sql server 2008 converter

[crazycat503]

Is there anyway to do it? I find a msoft tool that does it for sql server 2005. There is an import feature in sql server 2008 but it doesn't do it correctly. Anyway to convert my database with all its relationships to sql server automatically??

[BitBlt]

Not until you upgrade to Sql2008R2. However, if you want a quick-and-dirty, I have a little utility routine that works in Access2003 to pull out most of the table structure, keys, indexes and relationships. It isn't perfect, but it will get you a long way toward the structure.

Paste this into a module in your Access database and execute it. It only does "debug.print" but you can easily modify it to write to a text file or something else. Again, it isn't particularly pretty but it will get you SQL DDL that you can use to create your target database. Keep in mind that MSAccess is a LOT more forgiving than MSSQL when it comes to foreign keys, indexes, etc. You may have to do some tweaking to get the script to execute cleanly.
Then you can use SSIS or Import Wizard to migrate the data.

Sub DebugPrintTableStructure()

On Error Resume Next

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim z As Integer
Dim myDb As Database
Dim mySqlStmt As String
Dim myType As String
Dim myNullable As String
Dim myDefault As String
Dim myCols As String
Dim myTarget As String

Set myDb = CurrentDb

For i = 0 To myDb.TableDefs.Count - 1
    If myDb.TableDefs(i).Properties("Attributes") = 0 Then
        Debug.Print "-- Table: " & myDb.TableDefs(i).Name
        mySqlStmt = "create table dbo." & myDb.TableDefs(i).Name & "(" & vbCrLf
        For j = 0 To myDb.TableDefs(i).Fields.Count - 1
            Select Case myDb.TableDefs(i).Fields(j).Properties("Type").Value
            Case 3
                myType = "Integer"
            Case 4                     ' LongInteger
                myType = "BigInt"
            Case 20
                myType = "Decimal(18,10)"
            Case 6                     ' Single
                myType = "real"
            Case 7                     ' Double
                myType = "float"
            Case 10
                myType = "VarChar" & "(" & myDb.TableDefs(i).Fields(j).Properties("Size").Value & ")"
            Case 12                     ' Memo
                myType = "Text"
            Case 8
                myType = "Datetime"
            Case 5                      ' Currency
                myType = "Money"
            Case 1                      ' Yes/No
                myType = "Bit"
            Case Else
                myType = "Varchar(50)"
            End Select
            
            ' set NULL option
            Select Case myDb.TableDefs(i).Fields(j).Properties("Required").Value
            Case False
                myNullable = "NULL"
            Case Else
                myNullable = "NOT NULL"
            End Select
            myDefault = myDb.TableDefs(i).Fields(j).Properties("DefaultValue").Value
            
            ' set DEFAULT option
            If IsNumeric(myDefault) Then
                myDefault = " default " & myDefault
            Else
                If myDefault = "" Then
                Else
                    myDefault = " default '" & myDefault & "'"
                End If
            End If
            mySqlStmt = mySqlStmt & myDb.TableDefs(i).Fields(j).Name & " " & myType & " " & myNullable & " " & myDefault
            If j < myDb.TableDefs(i).Fields.Count - 1 Then
                mySqlStmt = mySqlStmt & "," & vbCrLf
            End If
        Next j
        
        ' build primary key constraint and indexes
        myIndexes = ""
        If myDb.TableDefs(i).Indexes.Count >= 1 Then
            For j = 0 To myDb.TableDefs(i).Indexes.Count - 1
                If myDb.TableDefs(i).Indexes(j).Primary = True Then
                    mySqlStmt = mySqlStmt & "," & vbCrLf & " constraint pk_" & myDb.TableDefs(i).Name & " primary key ("
                    For k = 0 To myDb.TableDefs(i).Indexes(j).Fields.Count - 1
                        mySqlStmt = mySqlStmt & myDb.TableDefs(i).Indexes(j).Fields(k).Name
                        If k = myDb.TableDefs(i).Indexes(j).Fields.Count - 1 Then
                            mySqlStmt = mySqlStmt & ")" & vbCrLf & ")"
                            Exit For
                        Else
                            mySqlStmt = mySqlStmt & ","
                        End If
                    Next k
                Else
                    myOptions = ""
                    If myDb.TableDefs(i).Indexes(j).Unique = True Then
                        myOptions = " Unique "
                    End If
                    If myDb.TableDefs(i).Indexes(j).Clustered = True Then
                        myOptions = myOptions & " clustered "
                    Else
                        myOptions = myOptions & " nonclustered "
                    End If
                    myIndexes = myIndexes & " create " & myOptions & " index ix_" & myDb.TableDefs(i).Indexes(j).Name
                    myIndexes = myIndexes & " on dbo." & myDb.TableDefs(i).Name & vbCrLf
                    myIndexes = myIndexes & "("
                    For k = 0 To myDb.TableDefs(i).Indexes(j).Fields.Count - 1
                        myIndexes = myIndexes & myDb.TableDefs(i).Indexes(j).Fields(k).Name
                        If k = myDb.TableDefs(i).Indexes(j).Fields.Count - 1 Then
                            myIndexes = myIndexes & ")" & vbCrLf
                            Exit For
                        End If
                    Next k
                End If
            Next j
        Else
            mySqlStmt = mySqlStmt & vbCrLf & ")"
        End If
        Debug.Print mySqlStmt
        Debug.Print myIndexes
    End If

Next i
' build foreign key constraints...
For i = 0 To myDb.Relations.Count - 1
    mySqlStmt = "alter table dbo." & myDb.Relations(i).Properties("ForeignTable").Value & vbCrLf
    mySqlStmt = mySqlStmt & "add constraint fk_" & myDb.Relations(i).Name
    mySqlStmt = mySqlStmt & " foreign key "
    myCols = "("
    myTarget = "("
    For j = 0 To myDb.Relations(i).Fields.Count - 1
        myCols = myCols & myDb.Relations(i).Fields(j).Name
        myTarget = myTarget & myDb.Relations(i).Fields(j).ForeignName
        If j = myDb.Relations(i).Fields.Count - 1 Then
            myCols = myCols & ")"
            myTarget = myTarget & ")"
        Else
            myCols = myCols & ","
            myTarget = myTarget & ","
        End If
    Next j
    mySqlStmt = mySqlStmt & " " & myTarget & " references dbo." & myDb.Relations(i).Properties("Table").Value & " " & myCols
    Debug.Print mySqlStmt
Next i

End Sub

Hope it helps, and good luck!

[crazycat503]

thanks a lot buddy! it indeed was helpful!

[BitBlt]

Glad to help. Be sure to mark the thread "Solved".