Update Link Table - New Field in Source Database

[paulcook]

To all,

I have a macro to automatically export thirteen linked tables in Access 2003. The database to which these tables are linked was recently updated and new fields were added to two of the tables.

In order to have these new tables appear in my linked tables I deleted them and created new linked tables to the database. Is there a way that I can do this in the macro? If so how much time would that add to the macro run time? Is it possible to check if the table has been updated and only run this process if new fields have been added?

My Access macro skill are at a beginner level so if this process is to complicated I will just write a procedure to delete the link table and add it again in case of database changes.

TIA,

Paul

 

[KenReay]

Hi

Are the tables in an Access (.mdb) or an SQL Server database. Linked (Access .mdb) tables do not require that you refresh or relink for new columns to be recognised, they will be "seen" next time you open the database(s) containing the links

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[paulcook]

Ken,

The tables to which I am linking are in an Informix database. This database recently received a patch in which two of the tables received one new field each.

I went to do my montly export to CSV files and the vendor to whom I send these files reported that these new fields were not in the CSV files for those two tables.

Sure enough when I reopened the Access database which contains the link tables the fields where not there. I then just deleted those two tables and recreated the linked tables and sent the data. It was also my belief that these tables would update automatically but I tested the theory on an older version of the same Access database with the linked tables and upon opening the fields where not there.

I also ran the Tools > Database Utilities > Linked Table Manager and updated the tables which did not solve my problem either.

Thanks,
Paul

 

[KenReay]

Hi

Yes, as I said I expected that linked tables where the "source" table was an access table would automatically show new fields, but others I was less sure of

Have you tried the .RefreshLink method of the tabledef object (note you will need a reference to the DAO library)

something like:

Dim Db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String

set db = currentDb
for each tdf in db.tabledefs
strConnect = "whatever it needss to be for an informix database"& ";Table=" & tdf.Name
tdf.Connect = strConnect
tdf.RefreshLink

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK