Solved

Access Macro over a Network with local ODBC

Posted on 2013-05-29
7
328 Views
Last Modified: 2013-08-08
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:

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

Open in new window


Most of the time this works as expected.  The last two days, I received the following screen:
Access error information
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!
0
Comment
Question by:slightlyoff
  • 4
  • 2
7 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39206923
Whats the code in your "TableExists" function?

Could be a problem in there.

Also you could save a few lines of code if you have a new version:

tableDeleteIfExists("tablename")

And add the delete in there.
0
 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 250 total points
ID: 39206933
is it this code:

http://www.access-programmers.co.uk/forums/showthread.php?t=199209

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 strTableNameCheck
On Error GoTo ErrorCode
'try to assign tablename value
strTableNameCheck = CurrentDb.TableDefs(TableName)
'If no error and we get to this line, true
TableExists = True
ExitCode:
On Error Resume Next
Exit Function
ErrorCode:
Select Case Err.Number
Case 3265 'Item not found in this collection
TableExists = False
Resume ExitCode
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "hlfUtils.TableExists"
'Debug.Print "Error " & Err.number & ": " & Err.Description & "hlfUtils.TableExists"
Resume ExitCode
End Select
End Function

Open in new window


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 strTableNameCheck

On Error GoTo ErrorCode
'try to assign tablename value (case sensitive!)
strTableNameCheck = CurrentDb.TableDefs(TableName).Name
'If no error and we get to this line, true

ExitCode:
On Error GoTo ErrorCode
If ((IsNull(strTableNameCheck) = False) And (strTableNameCheck <> "")) Then
DoCmd.DeleteObject acTable, TableName
End If
TableDeleteIfExists = True
Exit Function
ErrorCode:
Select Case Err.Number
Case 0
Case 3265 'Item not found in this collection
TableDeleteIfExists = False
Case 7874 ' cannot find the object to delete - maybe delted between check and delte
'TableDeleteIfExists = False /  allow return true
Resume ExitCode
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "TableDeleteIfExists result"
Resume ExitCode
End Select

End Function

Open in new window

- edit - debugged and updated code with more error messages
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39206972
Replacing lines 6 to 44 from your code if you use the new function.

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  

Open in new window


Also, in your code, lines 22 to 28, you are deleting [item inventory] twice, but never deleting [invoice line].
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 250 total points
ID: 39207119
Hi,

Just to add to the work already provided by Sudonim:

You have a few statements including 'acTalbe' that should read 'acTable'.

BFN,

fp.
0
 
LVL 1

Author Comment

by:slightlyoff
ID: 39208934
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:
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "SalesOrderLine", "SalesOrderLine", False

Open in new window

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!!!
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39209024
ok, lets experiment....

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

Open in new window

with
If ((IsNull(strTableNameCheck) = False) And (strTableNameCheck <> "")) Then
DoCmd.DeleteObject acTable, TableName
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, TableName, TableName, False
End If

Open in new window


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.
0
 
LVL 1

Author Closing Comment

by:slightlyoff
ID: 39393025
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now