New fields in ODBC linked table source

[Brian]

I have an app with ODBC links to a (DB2) DB. One of the source tables (and
probably several others) have had new fields added. How can I update the
definition of the linked table to show the new fields?

I know I can just delete and re-link, but for DB's with lots of tables, it
would be much faster to automate this in VBA.

 

[Barry Gilbert]

If this is a one-time or periodic deal, you can use the Linked Table Manager.
Select all tables and click ok. (don't click Always Prompt...).

If you want to automate this so it happens programmatically, you could
iterate through all tables and use the RefreshLink method on each table. Dev
Ashish has a nice routine that
(http://www.mvps.org/access/tables/tbl0010.htm) that handles a lot of this
for you.

Barry

 

[Brian]

I already refresh links programmatically using the Windows API to browse when
needed. The problem here is that refreshing an ODBC link, even using the
Always Prompt for new location option, does not add/reveal fields that have
been added to the source (DB2) table since I established the original link. I
seem to be stuck with the fields that existed when I did the initial ODBC
link.

Removing the link and requerying the database for its list of table in order
to re-link is very time-consuming because it takes so long to enumerate the
table list in DB2.

 

[Barry Gilbert]

I already refresh links programmatically using the Windows API to browse when
needed. The problem here is that refreshing an ODBC link, even using the
Always Prompt for new location option, does not add/reveal fields that have
been added to the source (DB2) table since I established the original link. I
seem to be stuck with the fields that existed when I did the initial ODBC
link.

Not sure why this happens. We use ODBC links to SQL Server and this brings
table changes in every time.

Removing the link and requerying the database for its list of table in order
to re-link is very time-consuming because it takes so long to enumerate the
table list in DB2.

We use a table that contains a list of table names and the corresponding
table names in the database back-end. We have a routine that drops all table
defs and relinks them based on the entries in the table. We have the option
of only running it when a flag is set in an INI file.

Barry

 

[Brian]

Hmmm...must be something in the DB2 ODBC driver. I suppose I will just have
to record the table name & connection string, delete the link, and then
recreate it. I was just hoping there was a little simpler solution like the
refresh links routine. Still, that will be faster than doing it manually.

Thanks.

 

[Brian]

I finally found the problem: this particular DB2 table has over 255 fields!
So even when I dropped the link & recreated it, I got only the first 255,
which did not include the new fields.

Solution? Create a view in DB2 including just the fields I need and link to
the view instead of the table.

 

[Tim Ferguson]

Solution? Create a view in DB2 including just the fields I need and
link to the view instead of the table.

Solution 2 -- get someone to redesign the DB2 database...


Tim F

 

[Brian]

Fat chance. This is a $50,000+ trucking dispatch package provided by an
international software company with 500 customer companies using the
product...

 

[Tim Ferguson]

Fat chance. This is a $50,000+ trucking dispatch package provided by an
international software company with 500 customer companies using the
product...

No wonder it's cocked up then... dollar gets you twenty it started off life
in dBase IV or FileMaker Pro.


All the best


Tim F

 

[Brian]

Interbase and whatever predecessors that may have had.