use results returned from existing queries from VB

[yann]

I am new with access programming, therefore I am wondering if it's possible to use the results of queries I have already created in MS Access when I am programming my modules in MS VB? If I can, how can that be done

Thanks in advance!
yann

 

[Allen Browne]

OpenRecordset() will let you get at the records in a query.

Execute will allow you to run an action query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am new with access programming, therefore I am wondering if it's

possible to use the results of queries I have already created in MS Access
when I am programming my modules in MS VB? If I can, how can that be done?

 

[Billy Yao [MSFT]]

Hi Yann,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.

I believe Allen has pointed out that you can use OpenRecordset() to get the recordsets in a
query. Here I use DAO to draft a VBA script for your reference. Please note that in your VB
project, you should use OpenDatabase to open that Access databse rather than using
CurrentDb() as below.

'''''''''''''''''''''''''''''''''''''''
Private Sub Command0_Click()

Dim DB As DAO.Database
Dim RS As DAO.Recordset, FLD As DAO.Field

Set DB = CurrentDb()
Set RS = db.OpenRecordset("dbo_Category Sales for 1997 Query")

RS.MoveFirst
Debug.Print "Results" & vbCrLf & "---"
Do Until RS.EOF
For Each FLD In RS.Fields
Debug.Print FLD.Value,
Next
Debug.Print
RS.MoveNext
Loop

Set RS = Nothing
Set DB = Nothing

End Sub
''''''''''''''''''''''''''''''''''''

Yann, does this answer your question? Please feel free to let me know if this help solves your
problem. If there is anything more I can do to assist you, please feel free to post it in the group

Best regards,

Billy Yao
Microsoft Online Support

 

[yann]

Dear Allen & Billy,

Thanks for your help. I tried putting this codes into VB of Access

Private Sub Command0_Click()

Dim DB As DAO.Database
Dim RS As DAO.Recordset, FLD As DAO.Field
Set DB = OpenDatabase("D:\yann\report.mdb")
Set RS = DB.OpenRecordset("PrdtsToUpdate") 'query name in report.mdb
...
...
...

but I am getting an error :"Run-time error "3734": The database has been placed in a state by user 'Admin' on machine '<machine_name>' that prevents it from being opened or locked."

Please kindly advise what have i done wrong in my codes to cause this error?

Thanks & regards
yann

 

[Allen Browne]

If you are working in d:\yann\report.mdb, there is no need to OpenDatabase.
Instead, use:
Set DB = CurrentDb()

If that is not the case, make sure that d:\yann\report.mdb is not already
open (exclusively).

If the database crashed, there may be a spurious file named report.ldb in
d:\yann. Delete it.