Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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......... --------------------------------------------------------------------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!'") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!'") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access Recordset Rows to Excel | Excel Programming | |||
How to populate Excel Range from Access RecordSet? | Excel Programming | |||
Acquiring a single recordset from Access into Excel | Excel Programming | |||
Copy recordset from an Access "make table" query | Excel Programming | |||
open range (within workbook) as ado recordset - excel vba | Excel Programming |