Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 12th, 2005, 03:49 PM
Registered User
 
Join Date: Aug 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Make a linked table in VBA

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?


 
Old August 13th, 2005, 01:43 AM
Friend of Wrox
 
Join Date: Jul 2005
Location: Oklahoma City, OK, USA.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 14th, 2005, 10:00 PM
Registered User
 
Join Date: Aug 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old August 14th, 2005, 10:44 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old August 14th, 2005, 11:08 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
The Following User Says Thank You to dartcoach For This Useful Post:
ErikP (January 26th, 2011)
 
Old August 15th, 2005, 03:44 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hey Admiral,

Did that help you?

Kevin

dartcoach
 
Old January 19th, 2006, 11:00 AM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I just googled in, it helped me, thanks dartcoach

 
Old January 19th, 2010, 11:12 PM
Registered User
 
Join Date: Jan 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Smile Creating link using VBA

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??
 
Old January 25th, 2011, 02:13 PM
Registered User
 
Join Date: Jan 2011
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Default To do more than one table

to do more than one table just repeat this section
Code:
 ConnectOutput dbsTemp, _
            "LinkedTable", _
            ";DATABASE=C:\db1.mdb", _
            "Dates"
using the different table names.

This part of the code calls the ConnectOutput procedure which then returns to the next table.....i.e.
Code:
ConnectOutput dbsTemp, _
            "LinkedTable1", _
            ";DATABASE=C:\db1.mdb", _
            "Dates1

ConnectOutput dbsTemp, _
            "LinkedTable2", _
            ";DATABASE=C:\db1.mdb", _
            "Dates2
etc for however many tables.......
 
Old January 26th, 2011, 01:21 PM
Registered User
 
Join Date: Jan 2011
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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?




Similar Threads
Thread Thread Starter Forum Replies Last Post
Read-Only Linked Table pjm Access 6 August 21st, 2006 07:42 AM
Can a Make Table Query produce a Linked table? kronik Access 5 May 16th, 2006 06:17 AM
Linked Table sdilucca Access 1 February 24th, 2006 07:29 AM
Calling a stored make table query from VBA jas644 Access VBA 0 March 30th, 2005 06:29 PM
linked table Tasha Access VBA 1 August 6th, 2004 03:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.