How to open Access recordset via Query in Excel VBA???
 

Hello,

Trying to write a small app and hit a wall. I have an Access DB with
a table named PPX. I have a query named 'SortPPX' stored in Access
which does nothing more that do a simple sort on 3 fields and a
selection of records.

My code, posted below fails on the 'Set rstIn.....' line with invalid
argument.

Is this the way you access a table via querydef? What am I doing
wrong?

Thanks,

Hexman

--------------------------------------------------------------------------------------------------------------------------
Dim db As Database
Dim rstIn As Recordset
Dim rstOut As Recordset

Dim SVTrn As String
Dim SVSur As String
Dim SVClass As String


SVTrn = ""
SVSur = ""
SVClass = ""

Set db = OpenDatabase("I:\Workit.mdb")
Set rstIn = db.OpenRecordset("SortPPX", dbOpenSnapshot, _
dbSQLPassThrough, dbReadOnly)
Set rstOut = db.OpenRecordset("CLParLnk")

rstIn.MoveFirst
'
' Rest of code follows.........
---------------------------------------------------------------------------------------------------------------------------

How to open Access recordset via Query in Excel VBA???
 

Try it without all the extras (also recordsets don't work with action
queries):

Set rstIn = db.OpenRecordset("SortPPX")

or put the SQL directly in the openrecordset statement:

Set rstIn = db.OpenRecordset("Select SuzieQs from Hostess Where Ding
Dongs = 'Yummy!'")

How to open Access recordset via Query in Excel VBA???
 

Thanks. That worked fine. Do you know why my statement didn't work?
I thought I read the documentation correctly.

Hexman

On 27 Mar 2006 21:29:03 -0800, "Kletcho" wrote:

Try it without all the extras (also recordsets don't work with action
queries):

Set rstIn = db.OpenRecordset("SortPPX")

or put the SQL directly in the openrecordset statement:

Set rstIn = db.OpenRecordset("Select SuzieQs from Hostess Where Ding
Dongs = 'Yummy!'")

How to open Access recordset via Query in Excel VBA???
 

You needed values for each of those properties instead of the name of
the property.

Set rstIn = db.OpenRecordset("SortPPX", dbOpenSnapshot,
dbSQLPassThrough, dbReadOnly) should have read
Set rstIn = db.OpenRecordset("SortPPX", True, False, False) or
whatever it is you wanted for those properties. They are optional
though and most times you don't need them.

How to open Access recordset via Query in Excel VBA???
 

I thought those were ENUM's.

Hexman

On 29 Mar 2006 05:44:01 -0800, "Kletcho" wrote:

You needed values for each of those properties instead of the name of
the property.

Set rstIn = db.OpenRecordset("SortPPX", dbOpenSnapshot,
dbSQLPassThrough, dbReadOnly) should have read
Set rstIn = db.OpenRecordset("SortPPX", True, False, False) or
whatever it is you wanted for those properties. They are optional
though and most times you don't need them.