Hey there,
I've been builing this database for some time and now it is time to use sql server as a back end as opposed to an mdb back end. I have the database upsized to sql server and all of my forms and code work well with it; But I have two major hurdles that I have been struggling with for many hours this week. I'm sure these are not new to some of you and hope you can help with advice or samples. I'm sure I'm not the only one struggling with this.
1. What is the best, and hopefully most automated way, to get my database into an sql server without me being there. Do I need to use a .dts package? Can Access transfer tables and relationships from a back end mdb to a back end sql server and connect that? Is there a wizard that the end user can run that will make this practical?
I have two big problems with where I am stuck. -The loss of the identity key or autonumber when importing from mdb. -I cannot trust the Upsizing Wizard because of the dreaded "overflow" and the number of steps the end user needs to take to correct it.
2. How do I relink my tables to the new server? I really like the linked table manager with its choices of back ends but the tables all have dbo_ in their name and the user has to pick them all.
I do have a bit of code that I think is a step in the right direction, but I cannot get it to work. Maybe my connect is wrong? Here's what I've got. The problem with this one is that you have to go through the dsn choose dialog for every table.
Any help would be greatly appreciated.
Matthew.
[tt] Dim tbf As TableDef
Dim i As Byte
For Each tbf In CurrentDb.TableDefs
If Left(tbf.Connect, 4) = "ODBC" Then
tbf.Connect = "Provider=MSDASQL; Driver={SQL Server}; Server=xx.xx.xx.xx; Database=TESTdb1; UID=sa; PWD=goodpass; "
tbf.RefreshLink
End If
Next tbf
Err_LinksRefresh:
If Err.Number = 3151 Then
MsgBox Err.Description & " ODBC--connection failed.." & vbLf & _
"Inform your DB administrator about them.", vbCritical
DoCmd.Quit
End If[/tt]
I've been builing this database for some time and now it is time to use sql server as a back end as opposed to an mdb back end. I have the database upsized to sql server and all of my forms and code work well with it; But I have two major hurdles that I have been struggling with for many hours this week. I'm sure these are not new to some of you and hope you can help with advice or samples. I'm sure I'm not the only one struggling with this.
1. What is the best, and hopefully most automated way, to get my database into an sql server without me being there. Do I need to use a .dts package? Can Access transfer tables and relationships from a back end mdb to a back end sql server and connect that? Is there a wizard that the end user can run that will make this practical?
I have two big problems with where I am stuck. -The loss of the identity key or autonumber when importing from mdb. -I cannot trust the Upsizing Wizard because of the dreaded "overflow" and the number of steps the end user needs to take to correct it.
2. How do I relink my tables to the new server? I really like the linked table manager with its choices of back ends but the tables all have dbo_ in their name and the user has to pick them all.
I do have a bit of code that I think is a step in the right direction, but I cannot get it to work. Maybe my connect is wrong? Here's what I've got. The problem with this one is that you have to go through the dsn choose dialog for every table.
Any help would be greatly appreciated.
Matthew.
[tt] Dim tbf As TableDef
Dim i As Byte
For Each tbf In CurrentDb.TableDefs
If Left(tbf.Connect, 4) = "ODBC" Then
tbf.Connect = "Provider=MSDASQL; Driver={SQL Server}; Server=xx.xx.xx.xx; Database=TESTdb1; UID=sa; PWD=goodpass; "
tbf.RefreshLink
End If
Next tbf
Err_LinksRefresh:
If Err.Number = 3151 Then
MsgBox Err.Description & " ODBC--connection failed.." & vbLf & _
"Inform your DB administrator about them.", vbCritical
DoCmd.Quit
End If[/tt]
