Excel VBA Late Bind to Access and SQL Insert

Question

I am having a frustrating issue with late binding to MS Access from Excel VBA to execute a DML statement like Insert or Update. All of the data I use in vba comes from user defined Classes. I can query just fine but writing to the DB gets different errors each time I try a different way to do the same thing. Below are some links to the same/similar issues, however each is slightly out of context and therefore I could not get passed my problem.

My end goal is to simply execute a DML string statement and it has to use late binding. Mainly I get the 3251 error saying my connection is 'Read Only' or a missing ISAM when I add ReadOnly=0 to the connection string. Fyi, getProjectFile just returns a path to a file starting from the parent folder of my project. I am pretty sure I can just use the connDB.Execute so I only need SQL Insert, I don't want to query first because the queries will get fat quick. I also think something might be wrong with the enum params because the ExecuteOptions want bitmasks instead of just a Long and I don't really know how to do that. From most of my research, I kept getting referred to the LockType and/or cursor not being right. For my environment; Windows 8.1 64bit, MS Office 2010 32bit(required). Does anyone see what is wrong here?

Sub ADO_Tester()

Dim strSQL, strFile, strConnection As String
Dim connDB As Object

'late bind to the ADODB library and get a connection object
Set connDB = CreateObject("ADODB.Connection")

'Connect to the DB
strFile = Application.ActiveWorkbook.Path & "\" & "PortfolioDB.accdb"
strConnection = "Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strFile & ";"
connDB.Open strConnection

'insert statement for a test record
strSQL = "INSERT INTO underlying_symbol (symbol) VALUES ('xyz')"

'execute the 
connDB.Execute strSQL, , 2, 1 + 128

'clear the object
connDB.Close
Set connDB = Nothing

End Sub

Edit:

Early binding:

connDB.Execute strSQL, , adCmdText + adExecuteNoRecords

Late Binding: How to enter the value for adExecuteNoRecords? On msdn it is 0x80 and another post says &H0001,either way it gives a syntax error. It says enter a bitmask for this enum value.

connDB.Execute strSQL, , 1 + 0x80

Edit: Now the correct way - adExecuteNoRecords (the ADO enum value) = 0x80 (a binary value) = 128 (a decimal value)

connDB.Execute strSQL, , 1 + 128

Edit: Now the issue gets even deeper. When I execute the code in a test spreadsheet into a test database, it works. When I copy and paste into the actual project spreadsheet and point to actual project db, I get the error: operation must use an updateable query . . . again. Same db name, same dml, same table name. The only difference is the actual DB is a product of a split to separate it from the forms and code in Access. Could this have changed some setting to make it read only?

Edit: It just gets deeper and deeper. The issue causing it not to work in the project db is because I have some Excel Tables querying the db. I made these through the Excel UI, Ribbon -> External Data -> Access -> etc. . . It has now become obvious these are causing me to be unable to insert DML because they are probably set to read only. How can I change the tables connections permissions? Is there another way I could be making these tables so that I can provide the connection? How to get Tables to be friendly with DML in VBA?

I forgot to mention that I also looked into the 'IN' statement for an insert; 'INSERT INTO <myTable> IN <pathToDB> (<fields>) VALUES (<myData>). However I have not tried it quite yet. Could this be a good solution? Any words of wisdom for using it? — mr haven
It is a way to do it, but should not be necessary. I usually use those statements when I don't want to open separate connections to each access database. What error happens when you change connDB.Execute strSQL, , 2, &H1 to connDB.Execute strSQL ? — Jimmy Smith
When I just use connDB.Execute strSQL, I get an error that says it is read only and either the locktype is wrong or the provider can't do this. On msdn it says the Execute method defaults to ForwardReadOnly. My provider is the ACE.OLEDB, which is the same provider everyone else used in examples. I tried some of the other lockTypes as well but they returned the same error. — mr haven
Sorry, the exact error is, the operation must use an updateable query. This is how I found out about the other params and the default ForwardReadOnly on the execute. When I try and change the locktypes I get the other error I mentioned. — mr haven

VBlades VBlades · Accepted Answer · 2015-02-27T00:53:12

This worked for me:

Option Explicit

Private Const acCmdText As Integer = 1

Sub ADO_Tester()
On Error GoTo ErrorHandler

    Dim strSQL As String
    Dim strFile As String
    'Dim adoRecSet As Object
    Dim connDB As Object

    'late bind to the ADODB library and get a connection object
    Set connDB = CreateObject("ADODB.Connection")

    'Connect to the DB
    strFile = getProjectFile("core", "PortfolioDB.accdb")
    connDB.Open connectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strFile & ";"

    'If State = 1, db connection is okay.
    MsgBox "ADO Connection State is " & connDB.State & "."

    'SQL to get the whole [underlying_symbol] table
    'strSQL = "underlying_symbol" 'if options 2
    'strSQL = "SELECT * FROM underlying_symbol" 'if options 1
    strSQL = "INSERT INTO underlying_symbol (symbol) VALUES ('xyz')"

    'late bind to adodb and get recordset object
    'Set adoRecSet = CreateObject("ADODB.Recordset")

    '&H0001 = bitmask for aCmdText
    connDB.Execute strSQL, , acCmdText

    'With adoRecSet
     '   .Open Source:=strSQL, _
      '      ActiveConnection:=connDB, _
       '     CursorType:=1, _
        '    LockType:=3, _
         '   Options:=&H1
        '.AddNew
        '.fields("symbol") = "XYZ"
        '.Update
    'End With

    '------------------

    'close the objects
    'adoRecSet.Close
    connDB.Close

    'destroy the variables
    'Set adoRecSet = Nothing
    Set connDB = Nothing

ExitMe:
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description
    GoTo ExitMe

End Sub

Added some error handling, a constant that defines acCmdText (Why just not add a reference to ADO library? Up to you, though.), and a message box to check the connection state to the database, as I can't test your getProjectFile function. Late binding doesn't seem to be the issue here, I think the key line is:

connDB.Execute strSQL, , 2, &H1

Can really say what's going on here as I've never done it like this (code doesn't even compile), but changing it to

connDB.Execute strSQL, , acCmdText

worked for me.

Excel VBA Late Bind to Access and SQL Insert

1 Answers