ODBC linked tables -- can I refresh them all at once?

[Guest]

I seem to remember that I used to be able to refresh all linked ODBC tables
at once. Now I don't seem to be able to do that. It's possible I'm
remembering wrong; that's been known to happen; or equally possible that
there's a way to make it happen that I've forgotten.

I have an Access database with hundreds of tables and views linked to our
big Sybase database server. I would like to link to our development server.
All the tables are there, but Access seems to want me to refresh the link for
each table separately. That's a lot of password typing!

TIA for information about how to refresh all ODBC linked tables at once.

(Please don't point me at table009 -- it doesn't do ODBC.)

 

[Guest]

Basically, you are replacing the DSN string of the old ODBC with that of the
new, which is not a simple refresh in the same way that an Access front-end
to back-end link is. I use the following VBA code to edit the table DSN
references en masse.

If you use this method, you will need to find a way, by table name or some
other way, to ignore any local tables or those linked to other Access db's
(you don't want to start attempting to add DSN info to those tables). This
code goes through all tables, not just the ODBC-sourced ones. The If...Then
loop in my example was used to ignore non-ODBC tables, since all my ODBC
tables names had a common prefix. You could probably rename all the non-ODBC
tables with a common prefix and exclude them instead, then rename them back
when finished. Someone else can probably provide a programmatic way to
differentiate between the type of table (i.e. ODBC vs. non-ODBC).

The "DSN=<NewInfo>" & Right(cnt, Len(cnt) - 9)" you see is my particular
concatenation string from the example where I was simply replacing the first
couple of characters with <NewInfo>. As you can see, I was keeping part of
the string and replacing part of it. Yours may be simpler.

dim db as Object
dim tdf as Object
dim cnt as String
dim X as Integer

For X = 0 To CurrentDb.TableDefs.Count - 1
Set db = CurrentDb
Set tdf = db.TableDefs(X)
cnt = CurrentDb.TableDefs(X).Connect
If CurrentDb.TableDefs(X).Name Like "<StringCommonToODBCTables>*" Then
cnt = ";DSN=<NewInfo>" & Right(cnt, Len(cnt) - 9)
tdf.Connect = cnt
tdf.RefreshLink
End If
Next X
MsgBox "Done"

 

[Guest]

I think you mean that using the Linked Table Wizard, you
have to do each table individually.

That happens when you select tables linked to more than
one source.

That happens when you get halfway through linking and
then stop, or when you add a new link to something different
(perhaps a spreadsheet, or a Jet table).

If the table sources are not very mixed up, you still may be
able to select groups to relink, so that you only have to
select 3 or 4 groups, instead of each table individually.

(david)