use results returned from existing queries from VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.

yann said:
I am new with access programming, therefore I am wondering if it's
Click to expand...
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?
 
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
 
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
 
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.
 
Back
Top