Search code examples
excelvbams-accessadodb

ADODB run-time error with execute method fail

Strange thing with an SQL query execution. I'm trying to query Access DB from Excel VBA The following query runs ok "SELECT * FROM LL_domain"

While executing of this simple query "SELECT * FROM LL_domain WHERE domain = 'de" I get a run-time error.

I copied the query into Access query frame and it works ok. Seems, it's not a syntax problem. With an other provider for the connection object I get the same error.

What can be a reason for that?

some of my code:

Function foo (ByRef dBase as Object) as Boolean
Dim myConn2 as Object
    Set myConn2 = CN.getCN_Ace16(dBase.fileDB.path)
    Me.marketID = getMarketID(myConn2)
End Function

Function getCN_Ace16(ByVal dbPath As String) As Object
    Set getCN_Ace16 = CreateObject("ADODB.Connection")
    getCN_Ace16.connectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" & dbPath
End Function

Function getMarketID(ByRef myConn As Object) As String
Dim RS As Object
    myConn.Open
    Set RS = myConn.Execute("SELECT * FROM LL_domain WHERE domain = 'de'")
    ......
End Function

enter image description here

Solution

  • DOMAIN is an Access database engine reserved word

    Your query may work if you bracket the field name or qualify the name like one of these examples ...

    SELECT * FROM LL_domain WHERE [domain] = 'de'
SELECT * FROM LL_domain WHERE LL_domain.domain = 'de'
SELECT * FROM LL_domain AS ll WHERE ll.domain = 'de'

    However, if it were me, I would prefer to change the field name to something which is not a reserved word. Allen Browne provided a comprehensive list of Problem names and reserved words in Access