automatically relinking tables from external database

[polparrot via AccessMonster.com]

I have a database with 4 tables. 2 are from an external database called
controllers.mdb
This database is run in 2 places. The primary location is on a server and
all computers on the server point to "r:\r&m\site_info_sheets\maindatabase.
mdb". The secondary location is a laptop that goes with a tech in the field
so it is no longer connected to the server. The databse then resides on the
laptops c: drive with the same path. I am trying to set the code such that
the access database can be copied from the server to the laptop and when the
master form is loaded or opened then the tables will be relinked to the
controller.mdb on the c: drive as opposed to the r: drive.

I have placed the following code in my Open event of my master form:

Private Sub Form_Open(Cancel As Integer)
Dim db As dao.Database
Dim tdf As dao.TableDef
Dim strConnect As String
Dim strdir As String

strdir = CurrentProject.Path
strConnect = ";DATABASE=" & strdir & "\controllers.mdb"

Set db = CurrentDb
For Each tdf In db.tabledefs
tdf.connect = strConnect
tdf.refreshlink
Next tdf

Set tdf = Nothing
Set dbs = Nothing

End Sub

This code I got from another thread.

Any help on why this is not working would be greatly appreciated.

Polly

 

[Ralph]

Access has system tables that don't use that connection string. You need to
test for the connection string then chanage it.

Dim db As dao.Database
Dim tdf As dao.TableDef
Dim strNewConnect As String
Dim strOldConnect As String
Dim strOldDir As String
Dim strNewDir As String

strOldDir = "r:\r&m\site_info_sheets"
strNewDir = CurrentProject.Path
strOldConnect = ";DATABASE=" & strOldDir & "\controllers.mdb"
strNewConnect = ";DATABASE=" & strNewDir & "\controllers.mdb"

Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Connect = strOldConnect Then
tdf.Connect = strNewConnect
tdf.RefreshLink
End If

Next tdf

Set tdf = Nothing
Set db = Nothing

 

[polparrot via AccessMonster.com]

Thank you Ralph.

I tried the new code and am still experiencing the following error:

r:\r&m\site_info_sheets\controllers.mdb is not a valid path. Make sure the
path name is spelled correctly and that you are connected to the server on
which the file resides.

If I have this code in the Form Open event, shouldn't it relink the tables
before trying to use them?
My master form is one that also pulls info from the tables in the controllers.
mdb database.

 

[Ralph]

I think I spelled your database name wrong, it should be controller.mdb, also
after looking at your post again I am not sure that is the database that you
link to on the R drive. Try the following:

Dim db As dao.Database
Dim tdf As dao.TableDef
Dim strConnect As String
Dim strDir As String

strDir = CurrentProject.Path
strConnect = ";DATABASE=" & strDir & "\controller.mdb"

Set db = CurrentDb
For Each tdf In db.TableDefs
If InStr(tdf.Connect, "DATABASE") > 0 Then
tdf.Connect = strConnect
tdf.RefreshLink
End If

Next tdf

Set tdf = Nothing
Set db = Nothing

 

[polparrot via AccessMonster.com]

Thanks again Ralph.

My biggest problems seems to be that the On Open event is not running. That
is where I have the VB code. However, I continually get the error re: the
path. It is my understanding that the code in the On Open event is run PRIOR
to anything else. I even put a STOP at the begin of the On Open and the code
did not stop.

Got any ideas about what I might try next?

Thanks, Polly

 

[Andy]

Sounds like the Special Access Keys are not turned on.

Check the setting in Tools->Startup options->Advanced.

 

[polparrot via AccessMonster.com]

Thanks Andy.

I have looked at the special access keys and they are on.

Any other ideas anyone?

Whatever help I can get is greatly appreciated.

Polly

 

[Ralph]

Are you testing the code while you are attached to the network? If the table
is not linked and the form displays fields from the table you will get an
error whether the code runs or not. Is the database named controllers.mdb or
controller.mdb? Is the database on the R drive named controller.mdb?

If you are not attached to the network, try manually re-linking the tables
to the current directory on the c drive and then run the code. You might
consider a menu form to change the link to the tables.

 

[polparrot via AccessMonster.com]

Ralph,
At present I am not on the network. The primary usage of this db is in the
main office and runs on the network. However, the seconday usage is for the
technician to have the db running on a laptop which he takes with him. I
have tried creating a splash form and using the OnOpen event of that form to
relink the tables. My code is as follows:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Stop

strConnect = CurrentProject.Path
strConnect = ";DATABASE=" & strConnect & "\controllers.mdb"

Set db = CurrentDb
For Each tdf In db.TableDefs
tdf.Connect = strConnect
tdf.RefreshLink
Next tdf

Set tdf = Nothing
Set db = Nothing

I am now getting a Run-Time Error 3219 "Invalid Operation" error on the

tdf.connect = strConnect

line of code.

Also, the database with the tables I want to link to is called controllers.
mdb and resides on the R: drive for those using the network and the C: drive
on the laptops.

I have tried to relink the tables manually and do not have a problem with
that.

Any help is greatly appreciated.

Polly

 

[Ralph]

If you continue to use the original code you posted, I am not sure I can
help. In addition to any tables that are in your database that are not linked
there are system tables that Access creates. Therfore you cannot set the
connection string for those tables to the strConnect variable. The code you
posted below loops through ALL of the tables in the database and assumes they
are linked tables and tries to link them to controller.mdb. If you add the
following line to your code and test it, it may shed some light on what is
going wrong. The msgbox line below will give you a message box with the name
of the table it is trying to re-link to.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
strConnect = CurrentProject.Path
strConnect = ";DATABASE=" & strConnect & "\controllers.mdb"

Set db = CurrentDb
For Each tdf In db.TableDefs
msgbox tdf.name
tdf.Connect = strConnect
tdf.RefreshLink
Next tdf

Set tdf = Nothing
Set db = Nothing

 

[polparrot via AccessMonster.com]

Thank you Ralph,

I'll give that a try.

Polly

 

[polparrot via AccessMonster.com]

Ralph,

I put the msgbox in and it definitly helped me find my problem. Thank you so
much for you patience in helping me. I did not realize that the
currentProject.Path command value was case sensitive. I feel like a fool.

All of you guys are great and help tremendously!

Polly