I am new to the Access and VB world so my question is pretty basic. I need to call an Access stored query (which happens to take 1 parameter) in Visual Basic. Can someone tell me the best method and how to accomplish this? Would ADO be the best mechanism (as opposed to DAO)?
I'm not quite sure what your request is, but if you're just trying to run the query using VBA (e.g. click on a button to run the query) then all you'd need to do is:
My problem is this: I need to call from VB a stored query (in Access 2003) - called 'viewPatient', which takes in 1 parameter (patientId). I say I need to make it a VB program because the stored query uses custom VB code to process the query - code which is unseen by an outside program (C++ or VB). So my VB will modify the stored query a bit to just retrieve the data and then process or massage the data by the rest of the code - the custom VB code that will be pulled in - custom code that is compiled with my program (a VB 6.0 Visual Basic Enterprise Edition VB executable). That is in a nutshell the code assignment I have to develop. So, being a newbie at VB I can obtain the Database but from then on I need help in calling the query.
No, the stored query was created within Access 2003. So I just want or need to invoke it from a Visual Basic program. The stored query (viewPatient) needs 1 parameter (patientId).
Thanks for your reply. I am really confused. I need to call a stored query that requires 1 parameter. The stored query is 'viewPatient'. Again, it takes 1 parameter, ie. patientId. How can I do this?
*&%^$# - I just spent quite some time typing up a decent response, only to have everything lost because the session timed out and I was no longer logged in.
Okay, let me try again
Method 1
Create a table (assume tbl_patient) & form (assume frm_patient).
Enter some data into the form. Assume also that the Primary key for tbl_Patient is PatientID.
Edit the pre-created query in Design mode. Under the Criteria use the Build button on your toolbar to create a reference to the loaded frm_Patient form's PatientID.
Run the query & confirm that you have the data you are looking for.
Advantage of this approach is that there is no need for any coding.
Method 2
Copy the sql code from thepre-designed query into VBA and assign it as a ext value to a string variable as follows:
Ok, I am going to go down the DAO route for this response and assume you are using VB as opposed to Access and VBA.
Access:
Open up your Access mdb and design the query viewPatient. Take out any parameters or criteria relating to the patient ID, then save the query.
VB:
Step 1. You open your VB project
Step 2. Create a new module called ModPublics
Within this create a public variable named DAODb
Code:
Public DAODb As DAO.Database
Save the Module
Step 3:
Design the form that you want to retreive the data from
In the declarations section enter the following:
Set DAODb = OpenDatabase("X:\WhereAmI\Database.mdb")
Dim DAORs As DAO.Recordset
Dim LngPID As Long
X:\WhereAmI\Database.mdb is the full path and name of the above access database.
You have now delcared that this is the nominated database where all the information is.
Somewhere on your form you will have a control that the user will enter a patient id or some other means of detemining what the patient id is.
If we assume that there is a textbox named TxtPID; double click on the control to take you into the code section, from the right hand combo box select Validation:
Code:
Private Sub TxtPID_Validate(Cancel As Boolean)
LngPID = CLng(TxtPID)
'Retreive the patient record form the query viewpatient
Set DAORs = DAODb.OpenRecordset("Select * From viewPatient Where PatientID=" & LngPID)
'Check to see if the number entered by the user is actually a valid number
If Not DAORs.EOF And Not DAORs.BOF Then
'PID Found
place you code here...
Else
'PID Not Found
MsgBox "Cound not find " & CStr(LngPID) & " In the patient table. Please re-enter and retry.",vbExclamation+vbOkOnly,"Find Failed"
End If
'Now you need to close the database and recordset and remove from memory
DAORs.Close
DAODb.Clsoe
Set DAORs = Nothing
Set DAODb = Nothing
End Sub
In summary this code accepts a value from the user and attempts to find a record in the patient table whose ID matches that which was entered by the user.
It uses the DAO method for noiminating a database and recordset to filter an existing query (view) of the patients table.
If the filter returns a record then the search was successful and the user can continue, otherwise if no records were returned a message tells the user that the number they entered was incorrect.
At the end of the routine both the database and the recordset are disconnect and the variables removed from memory.
Coding is an exact science and the above is for brevity only.
is the line where I am setting the parameter of the Query in VBA. Just wrap the parameter in [ parameter here ] and then set it using =
Code:
Dim rstShawBulk As DAO.Recordset
Dim qdShawBulk As QueryDef
Set qdShawBulk = CurrentDb.QueryDefs("ShawBulk_qry")
qdShawBulk![[Forms]![ShawBulkUpload]![Driver]] = [Forms]![ShawBulkUpload]![Driver]
Set rstShawBulk = qdShawBulk.OpenRecordset
