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 = FalseIf tableExists("Invoice") Then DoCmd.DeleteObject acTable, "Invoice"End IfIf tableExists("InvoiceLine") Then DoCmd.DeleteObject acTable, "InvoiceLine"End IfIf tableExists("Customer") Then DoCmd.DeleteObject acTable, "Customer"End IfIf tableExists("Vendor") Then DoCmd.DeleteObject acTable, "Vendor"End IfIf tableExists("ItemInventory") Then DoCmd.DeleteObject acTalbe, "ItemInventory"End IfIf tableExists("ItemInventory") Then DoCmd.DeleteObject acTalbe, "ItemInventory"End IfIf tableExists("CreditMemo") Then DoCmd.DeleteObject acTalbe, "CreditMemo"End IfIf tableExists("CreditMemoLine") Then DoCmd.DeleteObject acTalbe, "CreditMemoLine"End IfIf tableExists("CreditMemoLinkedTxn") Then DoCmd.DeleteObject acTalbe, "CreditMemoLinkedTxn"End IfIf tableExists("SalesOrderLine") Then DoCmd.DeleteObject acTalbe, "SalesOrderLine"End Ifdsn = "QuickBooks Data"ODBCConnectStr = "ODBC;DSN=" & dsn & ";"'error happens here sometimesDoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Customer", "Customer", FalseDoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Invoice", "Invoice", FalseDoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "ItemInventory", "ItemInventory", FalseDoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Vendor", "Vendor", FalseDoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "InvoiceLine", "InvoiceLine", FalseDoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemo", "CreditMemo", FalseDoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemoLine", "CreditMemoLine", FalseDoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemoLinkedTxn", "CreditMemoLinkedTxn", FalseDoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "SalesOrderLine", "SalesOrderLine", FalseMacro1_Exit: Exit FunctionMacro1_Err: MsgBox Error$ Resume Macro1_ExitEnd 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.
Function TableExists(TableName As String) As Boolean'************************************************* ' Purpose: Checks to see whether the named table exists in the database, and try and delete it'' Returns: True, if table found in current db, False if not found.'************************************************* ****************************Dim strTableNameCheckOn Error GoTo ErrorCode'try to assign tablename valuestrTableNameCheck = CurrentDb.TableDefs(TableName)'If no error and we get to this line, trueTableExists = TrueExitCode:On Error Resume NextExit FunctionErrorCode:Select Case Err.NumberCase 3265 'Item not found in this collectionTableExists = FalseResume ExitCodeCase ElseMsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "hlfUtils.TableExists"'Debug.Print "Error " & Err.number & ": " & Err.Description & "hlfUtils.TableExists"Resume ExitCodeEnd SelectEnd Function
Here is an updated version i have amended to delte if exists, and ignore errors if the table is gone...
Function TableDeleteIfExists(TableName As String) As Boolean'*************************************************' Purpose: Checks to see whether the named table exists in the database'' Returns: True, if table found and deleted in current db, False if not found or Delete Failed.'************************************************* ****************************Dim strTableNameCheckOn Error GoTo ErrorCode'try to assign tablename value (case sensitive!)strTableNameCheck = CurrentDb.TableDefs(TableName).Name'If no error and we get to this line, trueExitCode:On Error GoTo ErrorCodeIf ((IsNull(strTableNameCheck) = False) And (strTableNameCheck <> "")) ThenDoCmd.DeleteObject acTable, TableNameEnd IfTableDeleteIfExists = TrueExit FunctionErrorCode:Select Case Err.NumberCase 0Case 3265 'Item not found in this collectionTableDeleteIfExists = FalseCase 7874 ' cannot find the object to delete - maybe delted between check and delte'TableDeleteIfExists = False / allow return trueResume ExitCodeCase ElseMsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "TableDeleteIfExists result"Resume ExitCodeEnd SelectEnd Function
- edit - debugged and updated code with more error messages
0
Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
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.
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.
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.
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Just to add to the work already provided by Sudonim:
You have a few statements including 'acTalbe' that should read 'acTable'.
BFN,
fp.