How do I create a query using VBA?

[InkyRich]

Hi all,
For reasons which are to long to explain I find that I have the need to create the equivalent to a query to filter and return records between two dates but using only VBA. Can anyone tell me the coding I need to do this.

Inky

Hampshire UK.

A fool and his money are soon parted - so you might as well send it to me!

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

One way to start would be to create the query using the query wizard, switch to SQL view, copy/paste the code.


Randy

 

[Knicks]

I can't remember off hand where I found it, but I have a utility you can use, its basically a tabbed form and using Randy700's technique of switching to SQL view and copying, you paste it into the tabbed form and press convert and the other tab shows you the VBA equivalent. Then just copy that into your module or wherever you are using the code. It is awesome for pesky text delimiters.

Search the net for VBA SQL CONVERTER. If you don't have any luck I can work on getting you the code.

 

[JBinQLD]

G'day Inky,

As mentioned above you can use the query builder to generate the SQL code and to execute it:

docmd.runSQL ("SELECT table.* FROM table;")

does the trick.

If you meant something entirely different, ie, literally making a query from VB I use something like:

Dim strSQL As String
Dim db As Database
Dim qd As QueryDef
Dim strLineBreak As String
Dim intLBPos As Integer

strSQL = "SELECT table.* FROM table;"

Set db = CurrentDb
Set qd = New QueryDef
qd.Name = "qryMyNewQuery"
qd.SQL = strSQL
db.QueryDefs.Append qd

Set qd = Nothing
set db=nothing

Hope this helps

JB

 

[pkj14]

A good code which should serve your purpose is given in "Access Data Analysis cook book" by Ken Bluttman & Wayne S Freeze(O'Reilly) on page 106. One more idea is to build a SQL string to be used as WhereCondition of a form's controlsource [see more in John L Viescas & Jeff Conrad's "Access 2007 Inside Out"(Microsoft Press). I give a part of code (partly modified) from their book -

Private Sub cmdCreateQuery_Click()

Dim varWhere As Variant,
Dim rst As DAO.Recordset
Dim FilterOprtr As String
' Initialize to Null
varWhere = Null
'A combo box picks a user specified "AND" or "OR" operator - assign it
FilterOprtr = Me.cmbFilterOperator
.
.
.
' If specified a Employee Code value in textbox txtEmpCode
If Not IsNothing(Me.txtEmpCode) Then
' .. build the predicate
varWhere = "([EmpCode] = ' " & Me.txtEmpCode & "')"
End If

' Do Full Name similarly
If Not IsNothing(Me.txtFullName) Then
' .. build the predicate
' Note: taking advantage of Null propogation
' so we don't have to test for any previous predicate
varWhere = (varWhere + (" " + FilterOprtr + " ")) & "([Name] LIKE '" & Me.txtFullName & "*')"
End If
.
.
.
.
DoCmd.OpenForm "frmEmployeeList", WhereCondition:=varWhere
.
.
End Sub

-PKJ14