By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 2,423 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

Create a linked table in Access 2007 using VB

P: 69
I have data in a few external Access tables that I want to link to my Access 2007 database. Depending on situation (and permissions of the person using the database) I want to dynamically link to one or more of the external tables when a specific user form opens. Then I want to delete the links when the form closes.

How does one go about creating and deleting a linked table using VB? I know the name of the database files and the tables I want to link but I'm not sure which objects & methods to use (so I don't know where to look in the the Access help file).

Could someone point me in the right direction? I don't need the complete code, just an object and/or method name so I know where to look in the help files. I've been digging for a couple of hours at this point and I'm pretty frustrated that I can't figure this one out on my own.

Thanks,
sphinney
Jun 24 '08 #1
Share this Question
Share on Google+
5 Replies


mshmyob
Expert 100+
P: 903
Here is some simple code to link your tables for Access. This is hard coded so you need to know in advance where your BE is to be located.

Expand|Select|Wrap|Line Numbers
  1. Function RefreshLinks() As Boolean
  2. Dim collTbls As Collection
  3. Dim i As Integer
  4. Dim strTbl As String
  5. Dim dbCurr As Database
  6. Dim dbLink As Database
  7. Dim tdfTables As TableDef
  8. Dim strBeFile As String
  9. Dim collTables As New Collection
  10. Dim tdf As TableDef
  11.  
  12. ' get the current linked table definitions
  13.     Set dbCurr = CurrentDb
  14.     dbCurr.TableDefs.Refresh
  15. ' end
  16.  
  17.     'First get all linked tables in a collection
  18.     For Each tdf In dbCurr.TableDefs
  19.         With tdf
  20.             If Len(.Connect) > 0 Then
  21.                     collTables.Add Item:=.Name & .Connect, Key:=.Name
  22.             End If
  23.         End With
  24.     Next
  25.     Set collTbls = collTables
  26.  
  27. ' change this string to your drive/directory/filename               
  28. strBeFile = "c:\any directory\backend file name.mdb"
  29.  
  30.             Set dbLink = DBEngine(0).OpenDatabase(strBeFile)
  31.  
  32.   ' start linking your tables - start from the last and work your way down
  33.      For i = collTbls.Count To 1 Step -1
  34.             strTbl = Left$(collTbls(i), InStr(1, collTbls(i), ";") - 1)
  35.                 Set tdfTables = dbCurr.TableDefs(strTbl)
  36.                 With tdfTables
  37.                     .Connect = ";Database=" & strBeFile
  38.                     .RefreshLink
  39.                 End With
  40.     Next
  41.    End Function
  42.  
To use this you must have at one time manually linked the tables. After that you can use this code to relink them anytime to any server/directory. Works with runtime also.

Hope this gets you started.

cheers,
Jun 24 '08 #2

P: 69
Thank you! This certainly does get me started. Much obliged.

Here is some simple code to link your tables for Access. This is hard coded so you need to know in advance where your BE is to be located.

Expand|Select|Wrap|Line Numbers
  1. Function RefreshLinks() As Boolean
  2. Dim collTbls As Collection
  3. Dim i As Integer
  4. Dim strTbl As String
  5. Dim dbCurr As Database
  6. Dim dbLink As Database
  7. Dim tdfTables As TableDef
  8. Dim strBeFile As String
  9. Dim collTables As New Collection
  10. Dim tdf As TableDef
  11.  
  12. ' get the current linked table definitions
  13.     Set dbCurr = CurrentDb
  14.     dbCurr.TableDefs.Refresh
  15. ' end
  16.  
  17.     'First get all linked tables in a collection
  18.     For Each tdf In dbCurr.TableDefs
  19.         With tdf
  20.             If Len(.Connect) > 0 Then
  21.                     collTables.Add Item:=.Name & .Connect, Key:=.Name
  22.             End If
  23.         End With
  24.     Next
  25.     Set collTbls = collTables
  26.  
  27. ' change this string to your drive/directory/filename               
  28. strBeFile = "c:\any directory\backend file name.mdb"
  29.  
  30.             Set dbLink = DBEngine(0).OpenDatabase(strBeFile)
  31.  
  32.   ' start linking your tables - start from the last and work your way down
  33.      For i = collTbls.Count To 1 Step -1
  34.             strTbl = Left$(collTbls(i), InStr(1, collTbls(i), ";") - 1)
  35.                 Set tdfTables = dbCurr.TableDefs(strTbl)
  36.                 With tdfTables
  37.                     .Connect = ";Database=" & strBeFile
  38.                     .RefreshLink
  39.                 End With
  40.     Next
  41.    End Function
  42.  
To use this you must have at one time manually linked the tables. After that you can use this code to relink them anytime to any server/directory. Works with runtime also.

Hope this gets you started.

cheers,
Jun 24 '08 #3

mshmyob
Expert 100+
P: 903
You are welcome. Good luck.

cheers,

Thank you! This certainly does get me started. Much obliged.
Jun 25 '08 #4

100+
P: 547
where do you copy this code to? how do you link it to button
Oct 10 '10 #5

mshmyob
Expert 100+
P: 903
You can put the code anywhere. The way I have shown it was putting it in a function in the form and then calling the function with the OnClick event of the button.

You could also put it in a module and call it that way or you can remove the 1st and last line (the function lines) and just put the whole code in the OnClick event of the button.

cheers,
Oct 11 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.