Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Union saved access query won't open as recordset in Excel

Status
Not open for further replies.

bluecjh

Programmer
Mar 12, 2003
385
I am using Office 2007.

I have a saved query in Access which runs fine as a stand alone query. However, when I attempt to open the recordset in my Excel VBA procedure I receive an "Invalid SQL ... " error.

I have managed to create very simple union queries and get the code to run as a test, but cannot see why my original query errors?

The code fails on the second line here:

cnn.Open "provider=Microsoft.ACE.OLEDB.12.0;data source= " & strFilePath & ";"
rs.Open "qry1", cnn, adOpenStatic, adLockReadOnly

My query consists of two SELECTS and a UNION ALL.

Is Access just being flaky, or are my 'open recordset' parameters wrong? Many thanks for any help.

BlueCJH
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top