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

linking tables in runtime

100+
P: 121
Hi,

I am trying to use the modRefreshTableLinks module in the samples database which links the tables to the north wind database. How do i get this to work with my own database ie which lines of code do i have to change for it to look up my tables?

Thanks Phill
Mar 11 '08 #1
Share this Question
Share on Google+
1 Reply


mshmyob
Expert 100+
P: 903
To link tables via code take a look at the following:

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.  
This sample is hard coded for the directory of where your back end tables are. Before using this code you would first have to manually link the tables.

cheers,

Hi,

I am trying to use the modRefreshTableLinks module in the samples database which links the tables to the north wind database. How do i get this to work with my own database ie which lines of code do i have to change for it to look up my tables?

Thanks Phill
Mar 11 '08 #2

Post your reply

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