slightlyoff
asked on
Access Macro over a Network with local ODBC
Long question...
I have a QuickBooks database that I'm using to fill an Access database located on our small business server.
I wrote the following macro in Access, and I trigger it on my workstation using Task Scheduler. This updates the Access database with the current QuickBooks data at 6:30am every morning:
Most of the time this works as expected. The last two days, I received the following screen:
The error happens after the macro deletes the tables. If i try to re-run the query, I get the same error. The thing is, there is no network connection error. I'm not sure why "Microsoft Access database engine" could not find the object 'Customer'...
I'm wondering if the fact that the access database is on the server (in a folder on a networked drive) could be the problem.
Any suggestions? Thanks for your help!
I have a QuickBooks database that I'm using to fill an Access database located on our small business server.
I wrote the following macro in Access, and I trigger it on my workstation using Task Scheduler. This updates the Access database with the current QuickBooks data at 6:30am every morning:
Function run_it()
On Error GoTo Macro1_Err
'check if tables exist, delete if they do
'DoCmd.SetWarnings = False
If tableExists("Invoice") Then
DoCmd.DeleteObject acTable, "Invoice"
End If
If tableExists("InvoiceLine") Then
DoCmd.DeleteObject acTable, "InvoiceLine"
End If
If tableExists("Customer") Then
DoCmd.DeleteObject acTable, "Customer"
End If
If tableExists("Vendor") Then
DoCmd.DeleteObject acTable, "Vendor"
End If
If tableExists("ItemInventory") Then
DoCmd.DeleteObject acTalbe, "ItemInventory"
End If
If tableExists("ItemInventory") Then
DoCmd.DeleteObject acTalbe, "ItemInventory"
End If
If tableExists("CreditMemo") Then
DoCmd.DeleteObject acTalbe, "CreditMemo"
End If
If tableExists("CreditMemoLine") Then
DoCmd.DeleteObject acTalbe, "CreditMemoLine"
End If
If tableExists("CreditMemoLinkedTxn") Then
DoCmd.DeleteObject acTalbe, "CreditMemoLinkedTxn"
End If
If tableExists("SalesOrderLine") Then
DoCmd.DeleteObject acTalbe, "SalesOrderLine"
End If
dsn = "QuickBooks Data"
ODBCConnectStr = "ODBC;DSN=" & dsn & ";"
'error happens here sometimes
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Customer", "Customer", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Invoice", "Invoice", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "ItemInventory", "ItemInventory", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Vendor", "Vendor", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "InvoiceLine", "InvoiceLine", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemo", "CreditMemo", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemoLine", "CreditMemoLine", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemoLinkedTxn", "CreditMemoLinkedTxn", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "SalesOrderLine", "SalesOrderLine", False
Macro1_Exit:
Exit Function
Macro1_Err:
MsgBox Error$
Resume Macro1_Exit
End Function
Most of the time this works as expected. The last two days, I received the following screen:
The error happens after the macro deletes the tables. If i try to re-run the query, I get the same error. The thing is, there is no network connection error. I'm not sure why "Microsoft Access database engine" could not find the object 'Customer'...
I'm wondering if the fact that the access database is on the server (in a folder on a networked drive) could be the problem.
Any suggestions? Thanks for your help!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Replacing lines 6 to 44 from your code if you use the new function.
Also, in your code, lines 22 to 28, you are deleting [item inventory] twice, but never deleting [invoice line].
Dim problemOccured As String
problemOccured = ""
If TableDeleteIfExists("Invoice") = False Then problemOccured = problemOccured & "Invoice, "
If TableDeleteIfExists("Customer") = False Then problemOccured = problemOccured & "Customer, "
If TableDeleteIfExists("Vendor") = False Then problemOccured = problemOccured & "Vendor, "
If TableDeleteIfExists("ItemInventory") = False Then problemOccured = problemOccured & "ItemInventory, "
If TableDeleteIfExists("InvoiceLine") = False Then problemOccured = problemOccured & "InvoiceLine, "
If TableDeleteIfExists("CreditMemo") = False Then problemOccured = problemOccured & "CreditMemo, "
If TableDeleteIfExists("CreditMemoLine") = False Then problemOccured = problemOccured & "CreditMemoLine, "
If TableDeleteIfExists("CreditMemoLinkedTxn") = False Then problemOccured = problemOccured & "CreditMemoLinkedTxn, "
If TableDeleteIfExists("SalesOrderLine") = False Then problemOccured = problemOccured & "SalesOrderLine, "
'for testing:
msgbox problemOccured
Also, in your code, lines 22 to 28, you are deleting [item inventory] twice, but never deleting [invoice line].
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help!
I tried the solution above, and it removes the tables correctly, but when it goes to put them into Access, i get the same "Can't find the table" error as above.
These lines:
Its almost as if the import process is trying to happen before access creates the new table to import into.
Thanks again for your help!!!
I tried the solution above, and it removes the tables correctly, but when it goes to put them into Access, i get the same "Can't find the table" error as above.
These lines:
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "SalesOrderLine", "SalesOrderLine", False
seem to be the issue. Except sometimes they work.Its almost as if the import process is trying to happen before access creates the new table to import into.
Thanks again for your help!!!
ok, lets experiment....
change the delete function to include the relink inline
:
replace lines
16-18 in the function:
And then comment out the old relinking code.
IF you still get the problem, its something to do with locks or other systems trying to access the table...we'll have to investigate further.
change the delete function to include the relink inline
:
replace lines
16-18 in the function:
If ((IsNull(strTableNameCheck) = False) And (strTableNameCheck <> "")) Then
DoCmd.DeleteObject acTable, TableName
End If
withIf ((IsNull(strTableNameCheck) = False) And (strTableNameCheck <> "")) Then
DoCmd.DeleteObject acTable, TableName
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, TableName, TableName, False
End If
And then comment out the old relinking code.
IF you still get the problem, its something to do with locks or other systems trying to access the table...we'll have to investigate further.
ASKER
Sorry for the delay!!!
I've tried your suggestions - and everything works, just not over the network.
In the end, we set up a virtual machine on the server and just run it locally - and everyone reads the database over the network.
From what I understand, Access has issues updating across a network. It's not a big enough project to go to a different database though, so we'll just work around it.
Thanks again for taking the time to answer my question - and I again apologize for taking so long to respond.
I've tried your suggestions - and everything works, just not over the network.
In the end, we set up a virtual machine on the server and just run it locally - and everyone reads the database over the network.
From what I understand, Access has issues updating across a network. It's not a big enough project to go to a different database though, so we'll just work around it.
Thanks again for taking the time to answer my question - and I again apologize for taking so long to respond.
Could be a problem in there.
Also you could save a few lines of code if you have a new version:
tableDeleteIfExists("table
And add the delete in there.