Reputation: 21
I'm writing a VBA code in Excel to access an Access database.
I'm using this provider
"Provider=Microsoft.ACE.OLEDB.12.0; Data Source= " & DBFile & ";"
and here is my SQL string
SQlSrc = "SELECT YEAR FROM Data ORDER BY YEAR ASC"
SQlSrc = SQlSrc & ";SELECT STN FROM STN_List WHERE include = TRUE"
When I open each recordset individualy it works (just first line or second) but when I make as a single statement as above I get an error
"Characters found after end of SQL statement"
Does anybody have idea if it's a problem with Access 2007?
Upvotes: 2
Views: 4062
Reputation: 91376
You can reuse the connection.
Dim cn As Object
Dim rs As Object
cn="Provider=Microsoft.ACE.OLEDB.12.0; Data Source= " & DBFile & ";"
SQlSrc = "SELECT YEAR FROM Data ORDER BY YEAR ASC"
rs.Open SQlSrc, cn
''Do stuff
SQlSrc = "SELECT STN FROM STN_List WHERE include = TRUE"
rs.Open SQlSrc, cn
cn.Execute "UPDATE Table SET Column=2"
Upvotes: 2
Reputation: 48068
IMO MS Access does not support multiple SELECT statements in a single Command. You may have to split this into individual commands.
Upvotes: 1
Reputation: 2387
It looks like Access and/or the provider doesn't accept multiple SQL statements for opening a single recordset.
That said, I'm not sure if this is standard for all OLEDB providers or just the one you're using.
Upvotes: 1