0

Working in Access 2007 in VBA:

Basically what I have currently works without errors but I no I'm probably violating something within the code (adodb and DAO possibly?), regardless I can't get the connection to end when the code finishes. If I remove the code for the "import," then the connection does start, executes whatever code, then shuts off which is what I'm looking to do but with an import.

My reasoning for doing this is the QODBC accesses a user account that goes into Quickbooks and then extracts the information. Problem is that the "user" basically stays logged in which isn't good because we need access to single user mode and what have you. Here is the code I have so far. Please help! 

Private Sub Connect_Click()
On Error GoTo ErrorHandler
'*****************************************************
'Connects the DB to QODBC, imports, and queries the info
'*****************************************************

Dim msg As String
Dim oConnection
Dim sConnectString
Dim dbs As DAO.Database
Dim lngRowsAffected As Long

'Sets connection string
sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"
Set oConnection = CreateObject("ADODB.Connection")
oConnection.Open sConnectString

        Set dbs = CurrentDb

' Import from QODBC
        DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=QuickBooks Data;DFQ=C:\Users\Public\Documents\Intuit\QuickBooks\Sample Company Files\QuickBooks 2012\sample_manufacturing business.QBW;SERVER=QODBC;OptimizerDBFolder=%AppData%\QODBC Driver for QuickBooks\Optimizer;OptimizerCurrency=Y;OptimizerAllowDirtyReads=D;OptimizerSyncAfterUpdate=Y;SyncFromOtherTables=N;ForceSDKVersion=<default SDK>;LicenseYear=2018", acTable, "SalesOrder", "SalesOrder1"
'Executes a query that appends a table called 'SalesOrder' from a table called 'SalesOrder1'
        dbs.Execute "qryAppendSalesOrder", dbFailOnError
'Bypasses warning messages through an execution of query but this grabs the total appended
        lngRowsAffected = dbs.RecordsAffected
'Function that logs how many lines were appended. Basically just an activity table
        Globals.Logging "Sales Orders Appended: " & lngRowsAffected
'Updates the 'SalesOrder' from 'SalesOrder1'
        dbs.Execute "qryUpdateSalesOrder", dbFailOnError
'Deletes the 'SalesOrder1' table that was imported
        DoCmd.DeleteObject acTable, "SalesOrder1"
        lngRowsAffected = dbs.RecordsAffected


'Closes Connection
oConnection.Close
Set oConnection = Nothing

ErrorHandler:
    If Err.Number <> 0 Then
        msg = "Error # " & Str(Err.Number) & " was generated by " _
        & Err.Source & Chr(13) & "Error Lne: " & Erl & Chr(13) & Err.Description
        MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
    End If

End Sub
Colton Mayers
  • 13
  • 4
  • So does it work or not? What's the *speciifc* problem, and is there not a way to reproduce it with a [mcve]? – Mathieu Guindon Sep 07 '17 at 16:43
  • I suppose the specific problem is that I need to start a connection, import a table from that connection and then close that connection. Right now, it will open the connection and do all the code without error but won't close the connection to the ODBC source. However, if i remove the "import" part of the code entirely. The connection will open, do whatever code inside, then close the connection successfully. I believe the import is invoking its own connection? – Colton Mayers Sep 07 '17 at 17:04
  • What else would it need a connection string for? – Mathieu Guindon Sep 07 '17 at 17:05
  • I suppose no other reason than to invoke a connection. Do you think I could somehow reference the connection that is opened at the start of the code? Or would there be a way to close the connection some time after the import? – Colton Mayers Sep 07 '17 at 17:08
  • Why do you even need `oConnection` anyway? You open it, and then you close it - it's never used. Get rid of it. Or, see if `TransferDatabase` can take an `ADODB.Connection` object instead of a connection string (doubtful). – Mathieu Guindon Sep 07 '17 at 17:10
  • If the import is absent then the oConnection' works. I'm not really sure how to close the connection after an import? Really I don't care how it invokes a connection but I have no idea how to get it to close that connection. That's the key – Colton Mayers Sep 07 '17 at 17:16

1 Answers1

0

oConnection.Open - 1st Connection is opened to QuickBooks Data.

DoCmd.TransferDatabase - 2nd Connection is opened to QuickBooks Data.

oConnection.Close - 1st Connection is Closed

When you use DoCmd, MS Access will open the connection to DSN=QuickBooks Data and will not close it until your close MS Access application.

There is an active connection to QODBC DSN & QuickBooks and thus, you are not able to close QuickBooks application.

You may have to close the MS Access application to release the connection. Manually Close ODBC DSN Connection

Or Find a way to close the connection opened via DoCmd [DoCmd.Close() - does not close the connection]

Or use oConnection to transfer data from QODBC tables to MS Access tables. Read each row and add it to your MS Access table.

For more help, please create a support ticket or do a search for this issue at our support system page:

http://support.qodbc.com

Rajendra Dewani
  • 176
  • 5