John Nurick [Microsoft Access MVP]-Looping through linked tables 2nd request

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

C. Pete Straman via AccessMonster.com

John,

Thank you for your help. However, your code or mine still produce the same
result. The very first table the message box tells me that is appended
"~TMPCLP352641 Appended to 060004 Table."
I used your code below to go through the linked tables.

C. Pete Straman

PS-what is an MVP




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

Dim condatabase As ADODB.Connection
Dim dbD As DAO.Database
Dim tbldef As TableDef
Dim strSQL As String
Set condatabase = CurrentProject.Connection
Set dbD = CurrentDb()

'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


'Loop through the linked tables to find the facility number
For Each tbldef In dbD.TableDefs
If Len(tbldef.Connect) > 0 Then
'Pull data from linked tables if facilityid is found
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 (tbldef.Name + " Appended to 060004 Table.")
End If
Next

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

condatabase.Close
Set condatabase = Nothing
Set catDB = Nothing


End Sub
 
~TMPCLP352641 sounds as though it's a "left over" table that's been deleted,
but not removed from the database. Try compacting your database before you
run the code.

To learn more about what the Microsoft Most Valuable Professional (MVP)
program is all about, check
 
It sounds as if you don't want to process all your linked tables, but
only some of them. How about something like

If (Not (tbldef.Name Like "~TMP*")) _
And (Len(tbldef.Connect) > 0) Then
...
 
Back
Top