Need to relink ODBC table at startup.

[Jordan]

I have an Access 2000 database called RMA.MDB in my M:\Databse folder. My
database contains 4 tables linked tables from another Access 2000 database
file called RMA_BE.MDB also located at M:\Database. The tables are:

Quote Master
Quote Details
RMA Master
RMA Details

I have 4 more linked tables connecting via a system ODBC data source called
MYDATA.

Customers
Sales Order Master
Sales Order Details
Part Master

My problem is that I have several clients that want to use my database. The
Access 2000 files are not a problem, but the ODBC data source is giving me a
lot of headaches. I cannot just run the database on the different networks
even though the drive mappings are the same and the ODBC system datasource
has the same name. The problem seems to be Access 2000 is not just looking
at the ODBC name. It is also looking at the settings in the ODBC setup.

As an example, I have three customers:

Customer 1 has ODBC datasource MYDATA connecting to engine on SERVER1
Customer 2 has ODBC datasource MYDATA connecting to engine on SERVER2
Customer 3 has ODBC datasource MYDATA connecting to engine on SERVER3

I work on my Access database at Customer 1's location where SERVER1 is the
database server that MYDATA points to. When I bring the database to
Customer 2 or Customer 3 I get an error that the Access cannot connect to
the ODBC source MYDATA pointing to SERVER1 even though when I check the
MYDATA datasource in the ODBC manager at Customer 2 and 3 the datasource is
pointing to Server2 and Server3 respectively.

In order to get past this I have to open the Access database at each
location I want to use it at and refresh the links on the ODBC tables. Is
there a way I can automatically refresh the links on my ODBC tables using
just the ODBC connection name?

 

[Joe Fallon]

I use this procedure to re-create links to SQL Server.
(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

 

[A Dubey]

If I understand you question, You are trying to use a
common linked MS Access 2k back end Data and Local Front
End MS Access 2000.

If this is correct, all you have to do is to locate the
back end mdb in a location that all users will have
network connectivity and read/write permissions.

Next, link a copy of the Front end to the back end tables
using the full UNC path.

ie:
\\servername\c$\commonuser\data\RMA_data\RMA_BE.MDB

By using the full UNC path, you eliminate any problems
created by the users individual logon scripts and
mappings.

AD

 

[Jordan]

No. the back end MDB is only one of two places the data is stored and read.
The front end is a MDB file that has the forms and code. It links to a back
end MDB where I store my data that I am collecting and it also links to a
Pervasive P.SQL 2000i database which has a lot of data that I need to read
and perform the calculations on to store in the MDB back end.

The problem is not the front end MDB linking to the backend MDB because the
drive mappings are the same. The problem is the ODBC connections to the
P.SQL database. When I set up the ODBC connection to the original server at
my company "Server1" I called the data source "MYDATA". I thought that all
I had to do at another location with the same database is create an ODBC
source on their computer and call it "MYDATA" and I would be all set. This
is not the case because when I go to the second location whose server is
"Server2" I get an ODBC message that basically says that it cannot connect
to MYDATA and something about "Server1". This shows that my MDB front end
is not only storing the data source name, it is also storing the server name
for some reason which is screwing the whole thing up.

Now if I merely open the front end MDB and use the Link Table Manager to
refresh the links the ODBC linked tables work fine. I need this to be done
automatically.