SQL statements in VBA - Access 2000

[DoDo1975]

Hi,

I am using Microsoft Access 2000, and would like to run a select query (not select..into) from vba instead of making a constant query.

Is there something similar to the DoCmd.runQuery method that works on select queries.

It is important because I am generating the Select and From clauses of the SQL statement using VBA, and would like to run that exact statement over multiple tables.

Any help would be appreciated.

[Ecniv]

Create your basic Sql statement in a string.
Example
"Select Blah.* from blah"

Then use VBA to add on the where clauses etc.
Then open a recordset using the Sql statement you've compiled.

I can't see why you'd use a DoCmd.RunQuery if you're returning records...


Vince

[DoDo1975]

I have already used VBA to create my SQL statement, and it is valid; I cut and pasted it into a Access query and it ran great.

I just want to use this SQL statement to return a recordset into a temporary table.

For instance lets say I have an sql statement in a variable called SQLString

SQLString = "SELECT Site.* FROM Site WHERE (((Site.LL_number)='807'))"

This resides on the codesheet for my form in access. I would like to press a button on my form and have it query the database and return a recordset. I have tried opening a recordset using querydef but that doesnt appear to work.

[Ecniv]

I have tried opening a recordset using querydef but that doesnt appear to work.

This would only work if the query was stored in the database.

Can you paste your code you have so far please.

I ask because although you are persuing the correct way it hasn't got there yet and you should be opening a recordset to view the data... or store it or whatever.

Also can you state what you want todo with the data. The initial post had this :

... like to run a select query (not select..into) from vba ...

So what do you want to do with it? Display it in a list box? or put the results of the query in a temporary/output table?


Vince

[DoDo1975]

I would like to display the results in a temporary table!


Private Sub buildQuery()
Dim SQLString
Dim selectClause
Dim fromClause
Dim whereClause
Dim temp
Dim temp2
Dim myDB As Object
Dim dbs As Object
Dim qdf As Object
Dim rstTemp As Object

' This function builds an SQL query string from the values on the global search page of the search form
' An SQL string consists of select, from, where and order by clauses

selectClause = "SELECT "
If search2site = True Then
selectClause = selectClause & "Site.*, "
End If
If search2sample = True Then
selectClause = selectClause & "Sample.*, "
End If
If search2fielddata = True Then
selectClause = selectClause & "FieldData.*, "
End If
If search2labdata = True Then
selectClause = selectClause & "LabData.*, "
End If
selectClause = Left(selectClause, Len(selectClause) - 2) & " " 'This subtracts the last comma from the string

fromClause = "FROM ((Site INNER JOIN Sample ON Site.ra_Site_Number = Sample.ra_Site_Number) INNER JOIN FieldData ON Sample.Sample_ID = FieldData.Sample_ID) INNER JOIN LabData ON (FieldData.Sample_ID = LabData.Sample_ID) AND (Sample.Sample_ID = LabData.Sample_ID)" & " "

Set myDB = CurrentDb()
temp = FieldType(myDB.TableDefs(Forms![Search]![search2_1]).Fields(Forms![Search]![search2_2]).Type)

If temp = "Memo" Or temp = "Text" Then
temp2 = "'%" & search2where2 & "%'"
Else: temp2 = search2where2
End If

whereClause = "WHERE ((([" & Forms![Search]![search2_1] & "].[" & Forms![Search]![search2_2] & "])"
whereClause = whereClause & " " & search2_op & " "
If search2_op = "BETWEEN" Then
whereClause = whereClause & temp2 & " AND " & search2where3
Else: whereClause = whereClause & temp2
End If

SQLString = selectClause & fromClause & whereClause & "));"
MsgBox SQLString
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("myRecordset", SQLString)
Set rstTemp = qdf.OpenRecordset()
'rstTemp.MoveLast
'want to display results in here
dbs.QueryDefs.Delete qdf.Name
dbs.QueryDefs.Delete "myRecordset"
dbs.Close

End Sub