"Lisa Herrmann" <li******@yahoo .com> wrote in message
news:3f******** *************@n ews.frii.net...
I upsized the database to SQL using the upsizing wizard. So their seems
to be a DSN-less connection. So when you go to:
File> Get External Data....there is no DSN for the database. How do you
get around that?
You could write a more generalised function, but if you simply need the
table re-linked, then create a form with a button on it named cmdLink and
paste the following code in the click event for the button. You just need
to replace 'MyServer', 'MyDatabase', etc with the appropriate values and
also decide whether you use a trusted connection or not. (In case you don't
already have it, you will have to set a reference to the DAO object library)
Private Sub cmdLink_Click()
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
strConnect = "ODBC;Driver={S QL Server};"
strConnect = strConnect & "Server=MyServe r;"
strConnect = strConnect & "Database=MyDat abase;"
'Either this:
'strConnect = strConnect & "Trusted_Connec tion = Yes"
'Or this:
strConnect = strConnect & "UID=sa;"
strConnect = strConnect & "PWD=MyPassword ;"
Set dbs = CurrentDb
Set tdf = dbs.CreateTable Def("tblMyTable ")
tdf.Connect = strConnect
tdf.SourceTable Name = "tblMyTable "
dbs.TableDefs.A ppend tdf
Exit_Handler:
If Not tdf Is Nothing Then
Set tdf = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Sub
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
HTH
Fletcher