Query into VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mat - 03 Feb 2006 17:52 GMT

Hello

I need to be able to access a query in VBA so I can cycle through it
and write parts of it out to a text file.

I have managed this with a database so far, but have now got a bit
stuck.

My database bit goes like this

****************************************************
Dim RST As Recordset
Dim QryRST As Recordset

Set DBS = CurrentDb()
Set RST = DBS.OpenRecordset("SchoolDB", dbOpenTable)

With RST
While not .EOF
'Do export routine in here.
.MoveNext
Wend
End With
******************************************************

I basically want the same sort of thing except from a table.

Thanks for any help

Mat

Reply to this Message

salad - 03 Feb 2006 21:52 GMT

> Hello
>
[quoted text clipped - 26 lines]
>
> Mat

What is SchoolDB? A database, in Access, is file with an extension of
MDB for the most part. The database usually contains tables, queries,
forms, reports, macros, and modules.

Let's say you have an MDB called SchoolDB.MDB. In that database, you
have a table called Students. You might do something like this
    Dim rst As DAO.Recordset
    set rst = Currentdb.Openrecordset("Students",dbOpenDynaset)
    With rst
        ....
    End With

Reply to this Message

Mat - 04 Feb 2006 00:10 GMT

Its a table. But I need to open a Query and sort through the data with
code.

Mat

Reply to this Message

salad - 04 Feb 2006 16:02 GMT

> Its a table. But I need to open a Query and sort through the data with
> code.
>
> Mat

What's the diff between a query and a table? If it craps out, use
"dbopendynaset" instead of "dbOpenTable"

I do this all the time
    Dim r As Recordset
    Dim strSQL As String
    strSQL = "Select * From Employees"
    set r = currentdb.openrecordset(strSQL,dbopensnapshot)

If snapshot, the result is not updateable. If dunaset, it's updateable.
Of course, queries like a Totals query (using groupby) aren't updateable .

Reply to this Message

Mat - 06 Feb 2006 21:56 GMT

Fantastic, thought it was going to be complicated.

Found some stuff on passing paramiters in

qdf.Parameters(1) = [Forms]![invoice generation]![pass value]

And together at last this makes sence

Thanks for your help

Mat

Reply to this Message