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??
crazycat503 7 Newbie Poster
BitBlt 452 Practically a Master Poster Featured Poster
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 7 Newbie Poster
thanks a lot buddy! it indeed was helpful!
BitBlt 452 Practically a Master Poster Featured Poster
Glad to help. Be sure to mark the thread "Solved".
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.