Link hundreds of tables by ODBC using code

[GRose]

Hello fellow users,

I am the admistrator of a finance system. The tables all sit on Oracle and I can happily link them into my Access 97 database using the external data option. 2 problems though.
1 - Each datasource has around 70 tables and there are 11 different datasources I need to link to, which means a lot of tables to link manually.
2 - Although the above would take some time I could leave it to do its work in the background. But, ever few tables I get asked to select a unique record indentifier. This means it would take days to click OK/Cancel every time this message came up.

What I would like to know is - can I link all the tables I need using VBA and without having to get asked this every few tables?

Thanks in advance

 

[Summerwind]

Hi

IMHO a different approach may be needed. Linking to all of those tables is going to take massive network resources as well as make the whole operation quite slow, even if the system is blitzed with hardware capabilities.

It may not be possible, but it may be necessary to only pull and push data when you actually need it. Do this by connecting to the datasource and table through connections at the time you are building recordsets to feed forms, reports etc.

That may need a significant re-write of your app if you have been lured down the road of binding forms to complete tables.

 

[GRose]

I appreciate what you say but the database it not going to have any forms or any other objects, just the linked tables. This is needed for my job as I often have to go into the tables from the datasources to check for problems or help with writing Cystal Reports etc. The network traffic is not really an issue. We have a under utalisted network capable of handling a lot of data so no problems there.

 

[GRose]

I have started doing a database for each ODBC source. It has taken 3 hours for me to link the 741 tables from one source. That means, I have another 10 sources to go in 10 different databases.

See my post of amending MSysObjects. That would help me loads.

Thanks so far

 

[Bodisathva]

this may help you along:

  Set tblDef = db.TableDefs(tblName)
  tblDef.Connect = ";DATABASE=" & dbPath
  tblDef.RefreshLink