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?

Sounds like it's stopping SQL injection attacks by only allowing one statement to be executed. — OMG Ponies
– OMG Ponies, Commented Dec 11, 2009 at 19:18
Jet/ACE has never processed multiple SQL statements and never will, not to avoid SQL Injection (there are still plenty of SQL Injection types to which Jet/ACE is vulnerable), but because there is no server-side process to serialize SQL statements and interleave requests from multiple users. — David-W-Fenton
– David-W-Fenton, Commented Dec 12, 2009 at 22:23
@David W. Fenton: "because there is no server-side process to serialize SQL statements and interleave requests from multiple users" -- why do you think it needs one. You can do this client side e.g. using a ADODB Recordset with a batch optimistic locking, make changes then UpdateBatch. This has been supported from at least Jet 4.0. Why do you think this model shouldn't work? — onedaywhen
– onedaywhen, Commented Dec 14, 2009 at 8:58

Fionnuala · Accepted Answer · 2009-12-11 20:43:36Z

2

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"

edited Dec 11, 2009 at 20:43

answered Dec 11, 2009 at 19:37

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dewayne Christensen Over a year ago

Umm, not quite. The second invocation in your example is appending the second SQL query to the first one, bringing us back to where we started. It should just be a straight assignment, and lose the semicolon.

hythlodayr · Accepted Answer · 2009-12-11 19:18:11Z

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.

answered Dec 11, 2009 at 19:18

hythlodayr

2,38715 silver badges23 bronze badges

1 Comment

Fionnuala Over a year ago

Access has never supported more than one statment

Raj More · Accepted Answer · 2009-12-11 19:18:35Z

1

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

answered Dec 11, 2009 at 19:18

Raj More

48.2k34 gold badges139 silver badges199 bronze badges

Collectives™ on Stack Overflow

Characters found after end of SQL statement VBA code Access 2007

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related