ODBC Table Relinking Code

[thart21]

I got this code from the MS Knowledge Base. I have a SQL back end and an Access front end. I need to zip the FE and email it to the users. I discovered that I needed to created a DSN on each user's machine to link the SQL tables and am trying to use this code to automate it. I am getting a "type mismatch" error and cannot figure out why.
I created the sample database and got it to work but, using the same code, I can't get it to work in my db.

Has anyone had experience with this code and error issue?


Function DoesTblExist(strTblName As String) As Boolean
On Error Resume Next
Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db.TableDefs(strTblName)
If Err.Number = 3265 Then ' Item not found.
DoesTblExist = False
Exit Function
End If
DoesTblExist = True
End Function



Function CreateODBCLinkedTables() As Boolean
On Error GoTo CreateODBCLinkedTables_Err
Dim strTblName As String, strConn As String
Dim db As Database, rs As Recordset, tbl As TableDef
' ---------------------------------------------
' Register ODBC database(s)
' ---------------------------------------------
Set db = CurrentDb
Set rs = db.OpenRecordset("tblODBCDataSources")
With rs
While Not .EOF
DBEngine.RegisterDatabase rs("DSN"), _
"SQL Server", _
True, _
"Description=VSS - " & rs("DataBase") & _
Chr(13) & "Server=" & rs("Server") & _
Chr(13) & "Database=" & rs("DataBase")
' ---------------------------------------------
' Link table
' ---------------------------------------------
strTblName = rs("LocalTableName")
strConn = "ODBC;"
strConn = strConn & "DSN=" & rs("DSN") & ";"
strConn = strConn & "APP=Microsoft Access;"
strConn = strConn & "DATABASE=" & rs("DataBase") & ";"
' strConn = strConn & "UID=" & rs("UID") & ";"
' strConn = strConn & "PWD=" & rs("PWD") & ";"
strConn = strConn & "TABLE=" & rs("ODBCTableName")
If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs("ODBCTableName"), _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If

rs.MoveNext
Wend
End With
CreateODBCLinkedTables = True
MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.description, vbCritical, "Problem"
Resume CreateODBCLinkedTables_End

End Function

Thanks.

 

[WayneRyan]

Toni,

I use this so that when the front-end s/w changes servers/databases
it will relink the tables.

Set tdf = dbs.TableDefs("tblSomething")
tdf.Connect = "Driver={SQL Server};" & _
              "Server=" & Me.txtServerName & ";" & _
              "Database=" & Me.txtDatabase & ";" & _
              "Trusted_Connection=Yes;" & _
              "Table=tblSomething"
tdf.RefreshLink
Set tdf = Nothing

Wayne

 

[thart21]

Wayne,

Thanks for the code - could you please clarify a couple of things for me?

This looks like you have the code in a form (Me.txtServerName).

Set tdf = dbs.TableDefs("tblODBCDataSources")
tdf.Connect = "Driver={SQL Server};" & _
"Server=" & Me.txtServerName & ";" & _
"Database=" & Me.txtDatabase & ";" & _
"Trusted_Connection=Yes;" & _
"Table=tblODBCDataSources"
tdf.RefreshLink
Set tdf = Nothing

tblODBCDataSources is the table I have listed all of my SQL tables in.
txtServerName (USAWVAS27) and txtDatabase (SQL db name?) are fields in my table. Am I using this correctly?

Thanks

 

[WayneRyan]

Toni,

In my example, the form has two textboxes, one to hold the server name,
the other for the database. In the AfterUpdate event for both of them
I have the following:

Set tdf = dbs.TableDefs("tblSomething")
tdf.Connect = "Driver={SQL Server};" & _
              "Server=" & Me.txtServerName & ";" & _
              "Database=" & Me.txtDatabase & ";" & _
              "Trusted_Connection=Yes;" & _
              "Table=tblSomething"
tdf.RefreshLink
Set tdf = Nothing

which in your case translates to:

Set tdf = dbs.TableDefs("tblSomething")
tdf.Connect = "Driver={SQL Server};" & _
              "Server=USAWVAS27;" & _
              "Database=SqlDatabaseName;" & _
              "Trusted_Connection=Yes;" & _
              "Table=tblSomething"
tdf.RefreshLink
Set tdf = Nothing

I don't like long run-on code, but your connect string should look something like this,
with you supplying the Server, Database and Table.

tdf.Connect = "Driver={SQL Server};Server=USAWVAS27;Database=SqlDatabaseName;Trusted_Connection=Yes;Table=tblSomething"

hth,
Wayne

 

[KeithG]

what line of code is giving the error?

 

[thart21]

Not getting an error message on this one (the "short" version of the code) but nothing happens at all. When I try to run the one in my first post I get a message "Item not found in this collection". I've verified the table names in both FE and BE and can't figure out what the problem is.

I've tried to use the code in Sam's "MS Access Developers Guide to SQL Server" and I get an "Argument Not Optional" error, which I can't resolve either.

I've checked my references and they are correct - any ideas would be appreciated.

Thanks

Toni

 

[WayneRyan]

Toni,

Sounds like the s/w is looking for an ADO reference.

Try getting your code in Design View.

Dim tdf As DAO.TableDef

and check the "Microsoft DAO 3.6" reference.

Wayne