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!
LVL 1
slightlyoffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
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
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

slightlyoffAuthor Commented:
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?
0
Jeffrey CoachmanMIS LiasonCommented:
I hate "reaching across" to other databases, (introduces even more variables...) I would rather bring the table into Access as a "temp table", then delete it.

But this in itself adds more complexity...

As I said, if everything is working as it should, I see no reason why the code is failing for just that one table...

So again, first make sure you are starting from a firm baseline (all tables deleted or all tables imported..)
...then run the code.

The only thing I can see happening is if you are introducing a timing error by running this code in rapid succession...

So as a quick test, insert this between the delete code and the Import code:

Delete Table1 code
Delete table2 code
...
    DoEvents

Import Table1 code
Import Table2 code
...



JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightlyoffAuthor Commented:
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!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.