C
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
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