Make an Query

[firework]

how to make an query using Access database that opened using OPENDTABASE command in VB

[Helger]

It's somewhat more complicated than just using opendatabase. I hope this is what you need. Just copy your SQL-text from ACCESS to strQuery.

Option Explicit
 
Function fGetRstDao(strQuery As String, Optional strPar1 As String, Optional strPar2 As String, Optional strPar3 As String) As DAO.Recordset
 
End Function
 
Dim wrkJet As DAO.Workspace
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rstDAO As DAO.Recordset
 
'open workspace
Set wrkJet = CreateWorkspace("", "Admin", "", dbUseJet)
'open DB
Set db = wrkJet.OpenDatabase("c:\myDB.mdb")
'create an empty Query
Set qdf = db.CreateQueryDef("")
 
'define Query
qdf.SQL = strQuery
'check how many parameters have been put to the query and assign the values:
If Not IsMissing(strPar1) And qdf.Parameters.Count >= 1 Then
    qdf.Parameters(0) = strPar1
    
    If Not IsMissing(strPar2) And qdf.Parameters.Count >= 2 Then
        qdf.Parameters(1) = strPar2
 
        If Not IsMissing(strPar3) And qdf.Parameters.Count >= 3 Then
            qdf.Parameters(2) = strPar3
        End If
    End If
End If
'next line will open a recordset based on the above query and
'assign it to the function as its return value to get one that
'you can actually work with call the function as seen in
'the sub "Sub Test" below...
Set fGetRstDao = qdf.OpenRecordset
End Function
 
'this function can be called like this:
'example with one parameter. no parameters or 2 or 3 are possible too
'for more change the above function accordingly
 
Sub Test()
Dim strQuery As String
Dim strParameter As String
Dim rst As DAO.Recordset
 
strSQL = "SELECT * FROM tblMyTbl WHERE fld1 = ?;"
strPar1 = "xyz" 'this will transport the value for the parameter set to '?' above. Can't do it directly!
 
Set rst = fGetRstDao(strQuery, strParameter)
 
End Sub

regards,

Helger