Linking to ODBC Database using a Macro or VB

[Len]

I am trying to connect to a table using ODBC. When I am
in my tables area I can do it by clicking on File, Get
External Data, Link Files, Files of Type = ODBC Databases,
and my Tables appear. I can select any of them under the
Machine Data Source tab and they will Link.

The school wants to do it by clicking on a button in a
form.

When I try it using a Macro I Get Could Not Find Instable
ISAM message.

In my Database Name Field of my Macro I Have
DSN=TestManagerElPaso;UID=User2;PWD=www;LANGUAGE=us_english
;"Database=Pubs"

The Message is it cannot find dbo.SaTest

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=SATManagerElPaso;UID=User2;PWD=www;LANGUAGE=us_en
glish;" _
& "DATABASE=pubs", acTable, "dbo_SaTest", "SaTests"

I only did this once before, so I do not have too much
experience with ODBC's.

I do not want to double post so please respond if you know
what I am doing wrong. I have been trying to make it work
since last Friday.

Thank You in Advance

 

[Joe Fallon]

I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub