VBA Code - Records from linked tables and appending to unlinked table

[C. Pete Straman via AccessMonster.com]

I am using the code below to search for records on all linked tables (Seven
linked tables). The records are appended along with a ~tmp1&*? table
created some how created by my code. Can someone tell me how to change the
code to eliminate the extra table and streamline the code?
Thanks in advance.

C. Pete S



'**** Step 3*******
'***** Appends linked tables
Private Sub cmdAppendNewTable_Click()

Dim condatabase As ADODB.Connection

'Index of tables in database
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table

Dim strSQL As String

Set condatabase = CurrentProject.Connection
Set catDB = New ADOX.Catalog

'Open a catalog on the database in which to linked tables
catDB.ActiveConnection = CurrentProject.Connection


'Check if accountnumber was changed to new text-255 data type
Answer = MsgBox("Have you processed Steps 1 and 2?", _
vbYesNo + vbQuestion, "Tell me")
If Answer = vbNo Then Exit Sub

For Each tblLink In catDB.Tables
' Check to make sure table is a linked table.
If tblLink.Type = "LINK" Then
strSQL = "INSERT INTO 060004" _
+ " SELECT accountnumber AS accountnumber, transactiondate AS
transactiondate," _
+ " financialclass AS financialclass, facilityid AS facilityid,
visitnumber AS visitnumber," _
+ " dos AS dos, facilityname AS facilityname, insname AS insname,
revenue AS revenue," _
+ " payment AS payment, adjustment AS adjustment, dosMonth AS
dosMonth, dosYear AS dosYear," _
+ " transMonth AS transMonth, transYear AS transYear, transmoyr AS
transmoyr, dosmoyr AS dosmoyr, facilitystate AS facilitystate" _
+ " FROM Dalcon" _
+ " WHERE facilityid=60004;"
condatabase.Execute strSQL
MsgBox (tblLink.Name + " Appended to 060004 Table.")
End If
Next

'************************************************************************
'****************************Close Connection****************************
'************************************************************************

condatabase.Close
Set condatabase = Nothing
Set catDB = Nothing


End Sub

 

[Tim Ferguson]

I am using the code below to search for records on all linked tables
(Seven linked tables).

Can someone tell me how to
change the code to eliminate the extra table and streamline the code?

I think I'd go for a major design change and get all these identical tables
into one properly designed one.

Best of luck


Tim F