Some one created Access program in 2002, there are a few linked ODBC tables.
Now we need to change the Server Name and Database Name. How can I do that?
Any related article, book to help? Thanks in advance!
Hi,
I am giving you solution I am using. I store ODBC server name and path
in one table and list of tables I am linking to database in second
table. When you need to change server/tables, you need to change data
in tables and run function which will do the rest. Note that
"ACCOUNTING_SYSTEM" prefix is hard coded which makes function "dirty",
but I hope this will help.
'This procedure links tables from Sage database
'depending on company for which is user logged on
Public Function LinkODBCServerTables()
On Error GoTo Err_LinkODBCServerTables
Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String, strSelectQry As String
Dim path As String
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Tbl_ODBC")
strSelectQry = "select ODBCPath from Tbl_ODBC"
Set rs = dbs.OpenRecordset(strSelectQry)
path = rs(0)
If path = "" Then
MsgBox "You must supply a DSN in order to link tables.",
vbExclamation, "Error"
Exit Function
Else
strConnect = path
End If
SysCmd acSysCmdSetStatus, "Connecting to ODBC Server..."
Call DeleteODBCTableNames
Set rs = dbs.OpenRecordset("Tbl_ODBCTables")
'Open table in Read-Only mode (True parameter)
Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![ODBCTablename],
dbAttachSavePWD)
Set dbsODBC = OpenDatabase("", False, True, strConnect)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName =
dbsODBC.TableDefs("ACCOUNTING_SYSTEM." & rs![ODBCTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing
Exit_LinkODBCServerTables:
SysCmd acSysCmdClearStatus
Exit Function
Err_LinkODBCServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " &
Err.Source & Chr(13) & Err.Description)
LinkODBCServerTables = -1
Resume Exit_LinkODBCServerTables
End Function
'This procedure deletes all linked ODBC table names in 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
-------
In my case:
Table Tbl_ODBC, field ODBCPath = "ODBC;DSN=Sage_Eng;;"
Table Tbl_ODBCTables, field ODBCTableName = "SALES_LEDGER"
I hope you can use this to automatize ODBC re-connection.
Regards,
Branislav Mihaljev
