Adding Tables at Runtime

[ShaneBrennan]

I'm not very good at explaining technical things but I will give it a good go.

I have a MS Access 97 database that has 5 linked tables from 5 other user databases on the same network also written in Access 97. As I enter information into my system I can select which of the linked tables gets a copy of the information - this works fine.

However, I will shortly need to increasing this to 15 databases - which means I have to manually link in 10 further tables (called UserInfo) from 10 databases located on the network - I know I can manually do this - but I would love to find a way of doing it automatically, e.g. using a function such as:

LinkNewTable(NewTableName, DBPath, TableName).

Has anyone got a routing that can do this, or has some information to get me started?

Thank you in advance for any help given.


Shane Brennan

'-----------------------

' Emails: shanebrennan@postmaster.co.uk

 

[mp9]

If you want a function to do it, try this (untested) code:

Function LinkNewTable(NewTableName As String, DBPath As String, TableName As String)
On Error GoTo Err_LinkNewTable

    DoCmd.TransferDatabase acLink, "Microsoft Access", DBPath, acTable, TableName, NewTableName, False

Exit_LinkNewTable:
    Exit Function

Err_LinkNewTable:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation
    Resume Exit_LinkNewTable

End Function

As you can see, there's only one line that actually does the linking, so you might just want to have 15 lines of DoCmd.Transfer... rather than 15 calls to this function.

Hope this helps.

http://www22.brinkster.com/accessory/

 

[ShaneBrennan]

Thanks MP9 - I will give it a try

Shane Brennan

'-----------------------

' Emails: shanebrennan@postmaster.co.uk