I am trying to create a linked table from VBA code. The wizard to link the table is not an option.

The have the Table Name, Source Table Name, and the Directory Path information loaded as string variables just waiting to create a link. How do I do it in VBA?

There is not a wizrad to create a link to a table. There is a wizard to "relink" the tables to another path.

To link a table go to the file menu, select external data, then link.



Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com

The link to to another table in another access database. The wizards I am refering to are on create table and Link Manager. I can't use either. It is very important to be able to create a linked table with VBA. I have made a local table with VBA, how do I create it as a linked table?

Admiral Hook,

Check out the "Refresh Link" in VBA help. It has an example for connected and adding a link to the tabledef collection for the database. I've never tried it, but it looks like it's what you need.

Kevin

dartcoach

Ok Admiral - here you go:

This is modified from the help I told you about earlier.

   Dim dbsTemp As Database
   Dim strMenu As String
   Dim strInput As String

   ' Open a Microsoft Jet database to which you will link
   ' a table.
   Set dbsTemp = CurrentDb

   ' Call the ConnectOutput procedure. The third argument
   ' will be used as the Connect string, and the fourth
   ' argument will be used as the SourceTableName.
         ConnectOutput dbsTemp, _
            "LinkedTable", _
            ";DATABASE=C:\db1.mdb", _
            "Dates"

End Sub

Sub ConnectOutput(dbsTemp As Database, _
   strTable As String, strConnect As String, _
   strSourceTable As String)

   Dim tdfLinked As TableDef

   ' Create a new TableDef, set its Connect and
   ' SourceTableName properties based on the passed
   ' arguments, and append it to the TableDefs collection.
   Set tdfLinked = dbsTemp.CreateTableDef(strTable)

   tdfLinked.Connect = strConnect
   tdfLinked.SourceTableName = strSourceTable
   dbsTemp.TableDefs.Append tdfLinked

End Sub

Be sure to replace the directory where the database you want to link to and the table name you want to link to, and the name you want your linked table to have.

HTH,

Kevin


dartcoach

Hey Admiral,

Did that help you?

Kevin

dartcoach

I just googled in, it helped me, thanks dartcoach

dartcoach - I stumbled on this thread looking to automatically create linked tables and this code works a treat! Its brilliant.

I have about 10 tables i will need to link to the database, is this code capable of creating multiple linked tables?

I hope this is not too much to ask??

to do more than one table just repeat this section
using the different table names.

This part of the code calls the ConnectOutput procedure which then returns to the next table.....i.e.
etc for however many tables.......

Just want to say thanks for posting this. It looks like this is really going to solve a big issue for me.

I wonder if there is anyway to check if someone else is linked to a table and/or lock other users out of a linked table before acting on the table?