Characters found after end of SQL statement VBA code Access 2007

Question

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?

Answer 1

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.

Answer 2

IMO MS Access does not support multiple SELECT statements in a single Command. You may have to split this into individual commands.

Answer 3

That's unbelievable!.

So I have another question. How can I make a module that handles several sql statements without open and close the connection all the time?

My goal is to make a module that will work for any Access connection with any number of SQL statements.

Do you have any suggestion?

Answer 4

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"