Saving SQL created on the fly as a query

[fergua]

Hi...

I am relatively new to access.

I have spent a few days coding a database search page consisting of various comboboxes and textboxes. the user can select or enter data in these and when he/she clicks the search button an SQL select statement is generated on the fly and updates an existing query and this is used to query the database. I want to be able to give the user the option to save the query he/she generates and reuse it. How can I prompt him for a name and turn the SQL string strSQL that he has generated into a query and save it for later use?

Any help greatly appreciated..thanks

 

[Oldsoftboss]

Not sure this is the way I would go, you run the risk of your users saving hundreds of queries with abstract or meaningless names.

Anyway, I construct an SQL string for a graph query, then delete the old query and save the new one with the same name. That way, the report is always bound to that query, but different data is displayed, and all works well. Code...

Dim db As DAO.Database
Dim qdfNew As QueryDef
Dim strSQL As String

Set db = CurrentDb()


'....... ->

With db
.QueryDefs.Delete "qryGraph"
Set qdfNew = .CreateQueryDef("qryGraph", strSQL)
.Close
End With

HTH

Dave

 

[fergua]

Hi..thanks. That bit I already have in place so I have one query qryMain which is altered depending on the SQL string (strSQL) generated by the search form. I have only a few users for the database and they should ideally be able to save the queries. What I need is:

1) when the user searches he generates the SQL string from the search form. The search form has a command button for "save query as...".
2) when this button is clicked the user is prompted to give a name for the query
3) when this is input it is checked to see if it already exists or not..if so reprompt, if not continue
4) the new query is produced and saved, using the SQL string strSQL from the form.

I have the code to produce a new query each time the form is used by changing the SQL string for qryMain

Private Sub cmdFind_Click()

Dim strSQL As String

If Not EntriesValid Then Exit Sub

If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If

CurrentDb.QueryDefs("qryMain").SQL = strSQL
txtSQL.Value = strSQL
Dim count As Integer
count = DCount("*", "qryMain")

If DCount("*", "qryMain") = 0 Then
MsgBox ("Sorry, the query did not return any records"), vbExclamation, "Ooops"
End If

If DCount("*", "qryMain") = 1 Then
MsgBox ("The query returned 1 record"), vbExclamation, "Success!"
DoCmd.OpenForm "frmMainResults", acNormal
End If

If DCount("*", "qryMain") > 1 Then
MsgBox ("The query returned " & count & " records"), vbExclamation, "Success!"
DoCmd.OpenForm "frmMainResults", acNormal
End If

End Sub

Any ideas?

 

[SamDeMan]

Hi

I am also new to access, and i am looking for something similar. i would like to save my sql text as a query, i would like to use ADO. can anybody help?

i would like to add that i am having a problem. i need to extract totals from a crosstab query and display it to the footer of my report. the main report uses a sql script that is generated at open. the script is based on a crosstab. i then need another sql script for the footer. the problem is that access gives me an error that a dynamic report (based on a crosstab, without naming in advance my text boxes) cannot be used as a sub report. i was thinking by saving my queries i can get this done. but i am not sure how to save a query using ADO.

thanks,

sam

 

[SamDeMan]

hi

i decided to take another route, but i wouldn't mind an answer if someone has one. i searched msdn and i found an explanation that may be is still valid. its from 1999.
topic:
Creating and Modifying Stored Queries in Microsoft Access Databases with ADOX
link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adocreateq.asp

i would like to quote:
"If you need to use code to create stored queries that are available from the Access user interface, you must use the DAO CreateQueryDef method to do so."