form to run sql queries

[p0welly]

We have a product that is shipped as an mde front end and an mdb database, the application runs in runtime and usually the systems it runs on do not have access installed on them.

Can anyone point me in the right direction with regards to creating a form with a textbox on it, and some code on a button to run an sql query that has been entered in the textbox?

 

[modest]

This depends on what kind of query you're going to run. Update queries use different call functions then action queries (I have a feeling this will change in the future). This also depends on whether you want the query to run in the background... or if you want to be able to see the results window.

The code below is just a sample (one of the many solutions this can be done)

In your button's _click() event:

Dim strSQL As String

strSQL = Me.[Your Text Box Name].Value

If strSQL like "SELECT*" Then
DoCmd.OpenQuery strSQL
Else
DoCmd.RunSQL strSQL
EndIf

This is for update/insert/create/delete (and any other action queries)... select queries use a different call (docmd.openquery strSQL).

I'm not sure the if statement is correct.. you might have to use InStr() or whatever.. you just want to be able to know if it's a select statement.

The one thing you should add is an error event capture. On error tell the user that the query was invalid and that it will not be run.


hope this helps, or at least gets you started
-modest