Update Linked Tables

[ITperson]

Hello

I deal with a db that has many linked tables. Because the db is live and
constantly being used, I make no direct changes to the live db environment.

There are 3 dbs (temp, data, hist).
All dbs are protected by a workgroup login (via access not by OS).
For my test environment, I change the paths for the linked tables to reflect
my local copy of the dbs.

After making changes to my dbs, I copy the main file to the server where the
end users then download a copy locally where the paths of the linked tables
should be reflected properly.

However, because there are hundreds of linked tables (lots to each of the 3
dbs and with different paths), it is very time consuming to go in and
manually update the links. I need to go through the list of hundreds of
tables and check all the tables for 1 db name, then update the links, then do
the same for the other 2 dbs.

Is there a way using vba that I can just run some code that will look for
the name of the db (ex: *data, *hist, *temp) and change the path for each of
them based on the db file name string? (hist, data, temp)

Thank you so much
Terry

 

[Alex Dybenko]

Hi,
here a sample proc:
http://www.mvps.org/access/tables/tbl0009.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[ITperson]

Hi,
here a sample proc:
http://www.mvps.org/access/tables/tbl0009.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com



Hi Alex

Thank you for the response.

I am trying to find out where the error in the code is coming from. The
code goes through the iteration of the linked tables (over 100 of them going
to 3 different dbs) then passes the value to prompt for the new path to the
function fGetMDBName and crashes:

Here is the code for the function:

Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")
strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")
'Filter:=strFilter,
fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:=strIn, _
Flags:=ahtOFN_HIDEREADONLY)
End Function

The error is "Named argument not found" and it happens at the line
"fGetMDBName = ahtCommonFileOpenSave(Filter:=

It highlights Filter:=

Any suggestions?

Thank you
Terry

 

[Alex Dybenko]

Hi,
have you included OpenFileDialog?
"If the database specified for the linked table is not present, the code
brings up the GetOpenFileName dialog so that the user can select an
alternative source. Note: You must include that code in your application for
this example to work"

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[ITperson]

Hi,
have you included OpenFileDialog?
"If the database specified for the linked table is not present, the code
brings up the GetOpenFileName dialog so that the user can select an
alternative source. Note: You must include that code in your application for
this example to work"

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




Hi Alex

Again, thank you for your reply.

Yes, Getopenfilename is in there, but it never actually gets called. Im not
sure where I should be putting the call to it.

I see the Functions make reference to it but only in comments, not in an
actual call.

Any suggestions?

Thank you
Terry

 

[Alex Dybenko]

Well, try again. I just copied 2 cope snippets from mentioned pages - and it
works fine

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com