asked on

Access, QODBC and Transfering Data

I have a VBA script set up to transfer data from a QuickBooks file to an access database. it's set to run every morning at 5am, so that reports can be ran during the day off of the access database.

Some of the time it works perfect. Lately, I've gotten this error:

The Microsoft Access database engine could not find the object 'acInvoiceLine'. Make sure the object exists and that you spell its name correctly. If 'acInvoiceLine' is not a local object, check your network connection or contact the server administrator

The error doesn't always happen on 'acInvoiceLine' - I'm importing 10 tables, any of them can have it happen.

So my VBCode is:

On Error GoTo Macro1_Err

    
'DoCmd.SetWarnings = False
If tableExists("acInvoice") Then
    DoCmd.DeleteObject acTable, "acInvoice"
End If

If tableExists("acInvoiceLine") Then
    DoCmd.DeleteObject acTable, "acInvoiceLine"
End If

If tableExists("acCustomer") Then
    DoCmd.DeleteObject acTable, "acCustomer"
End If

If tableExists("acVendor") Then
    DoCmd.DeleteObject acTable, "acVendor"
End If

If tableExists("acItemInventory") Then
    DoCmd.DeleteObject acTable, "acItemInventory"
End If

If tableExists("acItemInventory") Then
    DoCmd.DeleteObject acTable, "acItemInventory"
End If

If tableExists("acCreditMemo") Then
    DoCmd.DeleteObject acTable, "acCreditMemo"
End If

If tableExists("acCreditMemoLine") Then
    DoCmd.DeleteObject acTable, "acCreditMemoLine"
End If

If tableExists("acCreditMemoLinkedTxn") Then
    DoCmd.DeleteObject acTable, "acCreditMemoLinkedTxn"
End If

If tableExists("acSalesOrderLine") Then
    DoCmd.DeleteObject acTable, "acSalesOrderLine"
End If

dsn = "QuickBooks Data"

ODBCConnectStr = "ODBC;DSN=" & dsn & ";"

DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Customer", "acCustomer", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Invoice", "acInvoice", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "ItemInventory", "acItemInventory", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Vendor", "acVendor", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "InvoiceLine", "acInvoiceLine", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemo", "acCreditMemo", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemoLine", "acCreditMemoLine", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemoLinkedTxn", "acCreditMemoLinkedTxn", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "SalesOrderLine", "acSalesOrderLine", False


Application.Quit

Macro1_Exit:
    
    Exit Function

Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit

Open in new window

I thought maybe the issue was that I shouldn't delete the table, and instead, just clear out the data. So I ran:

DoCmd.RunSQL "Delete * from acCustomer" (and all the other tables...)

Open in new window

But when I run the code, I get duplicate tables - (acCustomer1, acVendor1, etc).

Is there a way to just import the data - since the structure of the table is already in place?
I'm not sure if this is possible or if it will eliminate the issue, but it's the only thing I can think of...

Thanks for your help!

Jeffrey Coachman

The code is probably failing because acInvoiceLine is already deleted, so this may be a timing issue.
(Also make sure that the table is actually being imported)

I would try seeing if the table exists by interrogating the msysObjects table... (This may be a bit less resource intensive than using your "tableExists" function)

If Dcount("Name","msysObjects","Name='acInvoiceLine'")=1 then
DoCmd.DeleteObject acTable, "acInvoiceLine"
End If

JeffCoachman

Jeffrey Coachman

well clearing, ...then reloading, the table may be a better option, ...but then you need SQL to "update" the empty table with the new data.

Also try using Currentdb.execute instaead of docmd.runSQL...:

dim DBS as dao.datatbase
set DBS=CurrentDB

DBS .Execute "Delete * from acCustomer",dbfailonerror
...
...

DBS .execute "INSERT INTO YourTargetTable SELECT YourSourceTable.* FROM YourSourceTable",dbfailonerror

JeffCoachman

Jeffrey Coachman

In a nutshell, start from scratch with all tables deleted (or all tables loaded)

...And run your code, there is no reason that I can see why that one table is failing on the delete line...

JeffCoachman

slightlyoff

ASKER

Thanks Jeff, I'll give those things a try.
Is it possible to do the SQL Insert statement when the source table is part of a different database?

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightlyoff

ASKER

Thank you for your help. I was able to rule out the code as the cause of my issues - and as a result, I started looking elsewhere.

I compacted/repaired the database - which, though empty was 2gb. It's now 40mb when empty and it appears like everything is going to work correctly.

It appears I'll just have to compact and repair the database every so often.

Thanks again for your help!!!