Relinking SQL Tables

[Jeff Garrison]

Hello all

I have a database that uses quite a few tables from SQL server. The problem
is that when I post a new version of the database, I have to manually relink
the SQL tables via the Linked Table Manager.

Does anyone know of a way to either automatically relink the tables or code
to put behind a button to do the same?

Any help would be appreciated!

Thanks...

JG

 

[Jeff Boyce]

Jeff

There are several different approaches you can use. You can get a start on
(look at) these by searching on-line for MS Access Relink Tables.

One source is the mvps.org/access website.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dirk Goldgar]

Hello all

I have a database that uses quite a few tables from SQL server. The
problem is that when I post a new version of the database, I have to
manually relink the SQL tables via the Linked Table Manager.

Does anyone know of a way to either automatically relink the tables or
code to put behind a button to do the same?

Have a look here:

http://www.mvps.org/access/tables/tbl0009.htm
Tables: Relink Access tables from code

That's a great site for all kinds of Access tips.

 

[Douglas J. Steele]

Have a look here:

http://www.mvps.org/access/tables/tbl0009.htm
Tables: Relink Access tables from code

Actually, for SQL Server you'd want
http://www.mvps.org/access/tables/tbl0010.htm

Another option is the DSN-less approach I outline in
http://www.accessmvp.com/djsteele/DSNLessLinks.html

 

[Dirk Goldgar]

Actually, for SQL Server you'd want
http://www.mvps.org/access/tables/tbl0010.htm

Another option is the DSN-less approach I outline in
http://www.accessmvp.com/djsteele/DSNLessLinks.html

Thanks for the correction. I thought the link I posted handled ODBC tables
as well, but I see I was mistaken.

 

[Jeff Garrison]

Doug -

If I use the option used from the mvps.org site it comes back saying that
there are no User DSN's for Oracle Tables. I'm assuming that Dev Ashish
created this for Oracle.

Anyone know how to change it to SQL using System DSN's?

JG

 

[Jeff Garrison]

Doug -

I also looked at the DSN-less connections, but am having some issues...

1. I placed the code in a Class Module.
2. I copied the "FixConnections..." into the immediate window, replaced
with my info and hit return. It came back saying Sub or Function Not
Defined....am I doing something wrong?
3. If i have different SQL Databases, is there something that needs to be
altered?

Thanks.

Jeff

 

[Douglas J. Steele]

The code doesn't go into a class module: it goes into a "normal" module.
(That should answer points 1 and 2)

Define "different SQL Databases". Are you saying that, say, 3 tables will
come from database A and 4 tables from database B, or are you simply saying
that sometimes you want to be linked to database A, and other times you want
the same tables to be linked to database B?

For case 1, you'd need to store the information somehow as to which table
corresponds to which database.

For case 2, you'd run the code each time you wanted to relink.

 

[Douglas J. Steele]

Yes, Dev used Oracle, but that's not an issue. The issue is that
(unfortunately) it doesn't have very good instructions!

Look in function fReconnectODBC(). It's looking for hard-coded DSN names:

'Check to make sure ODBC DSNs are present
'You can follow the same steps to check for multiple DSNs
strTmp = fReturnRegKeyValue(HKEY_CURRENT_USER, _
cREG_PATH & "\qc03", "Server")
If strTmp = vbNullString Then Err.Raise cERR_NODSN

'Another ODBC DSN
strTmp = fReturnRegKeyValue(HKEY_CURRENT_USER, _
cREG_PATH & "\PMIP", "Server")
If strTmp = vbNullString Then Err.Raise cERR_NODSN


Those lines of code are checking for the existence of two user DSNs named
qc03 and PMIP. I'd say that the odds of you having DSNs with the same name
are pretty low. <g> You could change the hard coded DSN names, or you could
pass the name of a DSN to the function:

Function fReconnectODBC(DSN_Name As String) As Boolean

and replace that code with

'Check to make sure ODBC DSN is present
strTmp = fReturnRegKeyValue(HKEY_CURRENT_USER, _
cREG_PATH & "\" & DSN_Name, "Server")
If strTmp = vbNullString Then Err.Raise cERR_NODSN

(If you want to refer to System DSNs, as opposed to User DSNs, you also have
to change HKEY_CURRENT_USER to HKEY_LOCAL_MACHINE.)

The reference to Oracle is due to the error message being hard coded as

If Err.Number = cERR_NODSN Then
MsgBox "The User DSN for Oracle Tables were not found. Please " _
& "check ODBC32 under Control Panel.", vbExclamation + vbOKOnly,
_
"Couldn't locate User Data Sources"

You'll probably want to change that.

 

[Jeff Garrison]

Your first case is the one I would use...I have some tables linked from
Database A and some from Databse B.

 

[Jeff Garrison]

I'm assuming that I have to hard code the System DSN's in the following
line?

strTmp = fReturnRegKeyValue(HKEY_LOCAL_MACHINE, _
cREG_PATH & "\" & DSN_Name, "Server")

Do I put in the System DSN for DSN_Name and the SQL Server Name in for
"Server"?

 

[Douglas J. Steele]

You're going to have to resolve that whether you use my DSN-less approach,
or the DSN approach from "The Access Web".

My recommendation would be to have a local table in your application that
lists each of the linked tables and which "class" they belong to (i.e.:
identify which tables come from the same database). You'd then have to use
that information to determine which connection string to use when linking.

 

[Douglas J. Steele]

No.

For a SystemDSN named xyz, you'd use

strTmp = fReturnRegKeyValue(HKEY_LOCAL_MACHINE, _
cREG_PATH & "\xyz", "Server")


What that code is doing is looking to see whether there's an entry in the
registry for the specific DSN, and returning the value of the Server key
from it.

 

[Jeff Garrison]

Stupid mistake....I was putting the SQL Server's name in the
"Server"....works fine now!

Thanks.

Jeff

 

[david]

Your first case is the one I would use...I have some tables linked from

Database A and some from Databse B.

I'm not sure if that will work. You might want to post a question in
access.odbcclientserver, or be prepared to do some testing.

Access caches ODBC connections, and I thought I remembered
that the help said something about not being able to do multiple
different ODBC connections.

If neccessary, the workaround is to create links inside a SQL Server
database (either A, B or Cab), so that Access can link to just one
server and database.

(david)