CODEWITHSUNDEEP
sqlms-access
VBA_Code_Ex
VBA_Code_Ex

Reputation: 21

Characters found after end of SQL statement VBA code Access 2007

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

Answers (3)

Fionnuala
Fionnuala

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

Raj More
Raj More

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

hythlodayr
hythlodayr

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

Related Questions