VBA code for searching and appending data from linked tables to unlinked tables

  • Thread starter Thread starter Pete Straman Straman via AccessMonster.com
  • Start date Start date
P

Pete Straman Straman via AccessMonster.com

I am currently coding in the linked table names and cycling through them to
find and append records from them to a table that is not linked in the
database.
I need to code to loop through all the tables in my database and assign a
number to them (or identify with a existing VBA property). Then I wan to
loop through searching from my the records I need from each so that I
append them to my unlinked table.
If I can get the linked tables assigned an ID and know what and how to
access them I can write the loop to pull records. But ofcourese I will take
both if i can get them.

C.Pete S
 
Hi Pete,

You can identify linked tables by iterating through the database's
TableDefs collection. Any TableDef that has a non-blank Connnect
property is a linked table. I don't know why you want to give them IDs:
every table in a database has a unique name. Try something like this:

Private Sub MyButton_Click()
Dim dbD As DAO.Database
Dim tdfT As TableDef
...
Set dbD = CurrentDB()
For Each tdfT in dbd.TableDefs
If Len(tdfT.Connect) > 0 Then
'It's a linked table
Call DoMyStuff(tdfT.Name)

End If
Next
...
End Sub

Sub DoMyStuff(TableName As String)
'pull records from linked table TableName
'and append them to other table

End Sub
 
Back
Top