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
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