D
Dion
I have a series of .txt files that I import and append to existing tables in
Access. I created code the imports, sets indexes, deletes records, and
appends the new table to an existing table. The code works fine, but when I
put them all together (about 6), they bomb on the second table. I suspect I
need to rename/reset something, but don't know what.
the code snippet:
Sub zzz()
Dim cnn As ADODB.Connection
Dim rstImpErr As ADODB.Recordset
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Set cnn = CurrentProject.Connection
Set rstImpErr = New ADODB.Recordset
Set cat.ActiveConnection = cnn
'charge
DoCmd.TransferText acImportFixed, "Charge", "rjo_charge", _
"c:\temp\rjo_charge.txt"
rstImpErr.Open "SELECT * FROM rjo_charge_ImportErrors", _
cnn, adOpenStatic
rstImpErr.Close
cnn.Execute "DELETE * FROM rjo_charge WHERE BillItemId Is Null"
Set tbl = cat.Tables("rjo_charge")
tbl.Keys.Append "PrimaryKey", adKeyPrimary, "ChargeItemId"
cnn.Execute "DELETE Charge.* FROM Rjo_charge INNER JOIN Charge ON
Rjo_charge.ChargeItemId = Charge.ChargeItemId WHERE
Rjo_charge.ChargeItemId<>0"
cnn.Execute "INSERT INTO Charge SELECT * FROM rjo_charge"
'charge_mod
DoCmd.TransferText acImportFixed, "ChargeMod", "rjo_charge_mod", _
"c:\temp\rjo_charge_mod.txt"
rstImpErr.Open "SELECT * FROM rjo_charge_mod_ImportErrors", _
cnn, adOpenStatic
rstImpErr.Close
cnn.Execute "DELETE * FROM rjo_charge_mod WHERE UpdtDtTm Is Null"
Set tbl = cat.Tables("rjo_charge_mod")
tbl.Keys.Append "PrimaryKey", adKeyPrimary, "ChargeModId"
cnn.Execute "DELETE ChargeMod.* FROM Rjo_charge_mod INNER JOIN ChargeMod
" & _
"ON Rjo_charge_mod.ChargeModId = ChargeMod.ChargeModId " & _
"WHERE Rjo_charge_mod.ChargeModId<>0"
cnn.Execute "INSERT INTO ChargeMod SELECT * FROM rjo_charge_mod"
End Sub
Everything works for the "charge" section, but when it does the commands in
the "charge_mod" section, I get run-time error '3265' on the line "Set tbl =
cat.Tables("rjo_charge_mod")"
The two pieces work individually - do I need to reset something?
Thanks
Dion
Access. I created code the imports, sets indexes, deletes records, and
appends the new table to an existing table. The code works fine, but when I
put them all together (about 6), they bomb on the second table. I suspect I
need to rename/reset something, but don't know what.
the code snippet:
Sub zzz()
Dim cnn As ADODB.Connection
Dim rstImpErr As ADODB.Recordset
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Set cnn = CurrentProject.Connection
Set rstImpErr = New ADODB.Recordset
Set cat.ActiveConnection = cnn
'charge
DoCmd.TransferText acImportFixed, "Charge", "rjo_charge", _
"c:\temp\rjo_charge.txt"
rstImpErr.Open "SELECT * FROM rjo_charge_ImportErrors", _
cnn, adOpenStatic
rstImpErr.Close
cnn.Execute "DELETE * FROM rjo_charge WHERE BillItemId Is Null"
Set tbl = cat.Tables("rjo_charge")
tbl.Keys.Append "PrimaryKey", adKeyPrimary, "ChargeItemId"
cnn.Execute "DELETE Charge.* FROM Rjo_charge INNER JOIN Charge ON
Rjo_charge.ChargeItemId = Charge.ChargeItemId WHERE
Rjo_charge.ChargeItemId<>0"
cnn.Execute "INSERT INTO Charge SELECT * FROM rjo_charge"
'charge_mod
DoCmd.TransferText acImportFixed, "ChargeMod", "rjo_charge_mod", _
"c:\temp\rjo_charge_mod.txt"
rstImpErr.Open "SELECT * FROM rjo_charge_mod_ImportErrors", _
cnn, adOpenStatic
rstImpErr.Close
cnn.Execute "DELETE * FROM rjo_charge_mod WHERE UpdtDtTm Is Null"
Set tbl = cat.Tables("rjo_charge_mod")
tbl.Keys.Append "PrimaryKey", adKeyPrimary, "ChargeModId"
cnn.Execute "DELETE ChargeMod.* FROM Rjo_charge_mod INNER JOIN ChargeMod
" & _
"ON Rjo_charge_mod.ChargeModId = ChargeMod.ChargeModId " & _
"WHERE Rjo_charge_mod.ChargeModId<>0"
cnn.Execute "INSERT INTO ChargeMod SELECT * FROM rjo_charge_mod"
End Sub
Everything works for the "charge" section, but when it does the commands in
the "charge_mod" section, I get run-time error '3265' on the line "Set tbl =
cat.Tables("rjo_charge_mod")"
The two pieces work individually - do I need to reset something?
Thanks
Dion