Running a SQL string

[michaelc80]

All,
Some time ago I posted a question and received assistance (many thanks). However I need to tweak the code some. Here is what I have.

Private Sub btnGetErr_Click()

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

strSQL = "SELECT SQL FROM DATA_SCRUB_SQL " &_
"WHERE Error_ID = Forms!frmErrors!txtErrorNumber;"

Set db = CurrentDb
Set qdf = db.QueryDefs("qryResults")
qdf.SQL = strSQL
DoCmd.OpenQuery "qryResults", acViewNormal, acReadOnly
Set qdf = Nothing
Set db = Nothing


End Sub

What the above code does is retrieve a SQL string listed in the field SQL of the table DATA_SCRUB_SQL and return the SQL string in a query called "qryResults". It's selects the one that is associated with the field Error_ID, which is input by the user in a text box.

However, what I'd like it to do is actually run the SQL string that is in the SQL field, and return the results. For example, if the SQL code is "Select * from tablename" I'd like it to run that and return the results. It currently just shows the SQL code in the query.

Any help is appreciated!

 

[raskew]

Hi -

Try this, replacing my strSQL with yours.

Public Sub ShowSQL()
Dim qd     As QueryDef
Dim strSQL As String

    'create query SQL
    strSQL = "SELECT Orders3.OrderID, Orders3.CustomerID, Orders3.OrderDate" _
          & " FROM Orders3" _
          & " ORDER BY OrderDate, OrderID;"
    Debug.Print strSQL
    
    'Create QueryDef
     Set qd = CurrentDb.CreateQueryDef("qryTemp", strSQL)
    
    'Open / view new query
    docmd.OpenQuery qd.NAME
    
    'Delete QueryDef since this is just an example
    CurrentDb.QueryDefs.Delete qd.NAME

End Sub

HTH - Bob

 

[michaelc80]

Hi Bob,
That still gets me at the same point I'm at-the SQL field comes up in the query. I'd actually like the SQL to run and return a recordset rather than just showing me the SQL string. Thanks for the help though!

 

[KeithG]

You need to use your SQL in a Recordset to return records.

 

[michaelc80]

You need to use your SQL in a Recordset to return records.

You'll have to forgive me-I'm not a VBA programmer and this project was passed on to me some time ago.

So you're saying that I can't programmatically pass the SQL string into VBA and return the recordset? I have the SQL string listed in a table called DATA_SCRUB_SQL. There are 200+ SQL strings listed and I'd rather not have a form with 200+ buttons on it, although I can do that if I have no other choice.

Let's make sure I'm making myself clear. The table I'm working with has 2 fields-Error_ID and SQL. The Error_ID field is a unique number (1 thru 250). The SQL field is a unique SQL string that error checks data in other tables in the database (data that I've imported). There are 20 or so tables that get data imported into them.

What I want to do is to be able to put the Error_ID number into a field and have it run the associated SQL string and return the recordset. So for example, if Error_ID 22 is "SELECT * FROM ES_EMPLR WHERE HIRE_DATE IS NULL" I'd like to enter 22 into the field and press a command button to have it run "SELECT * FROM ES_EMPLR WHERE HIRE_DATE IS NULL" and return the results.

The above code returns "SELECT * FROM ES_EMPLR WHERE HIRE_DATE IS NULL" in a query rather than the recordset that the Select statement returns.

Just want to make sure I'm clear on what I'm trying to accomplish.

Thanks!

 

[raskew]

Hi -

I'm not sure where this is going wrong for you. The code I provided creates a temp query def, prints the SQL to the debug window for reference, then opens the query. There's no way that I can think of that opening a query using docmd.OpenQuery qd.NAME is going to return the SQL, rather than a record set.

Try the code again, copying/pasting to a public module (replacing my strSQL with yours). Save the module then, from the debug (immediate) window type: Call ShowSQL <return>

Bob

 

[michaelc80]

Hi -

I'm not sure where this is going wrong for you. The code I provided creates a temp query def, prints the SQL to the debug window for reference, then opens the query. There's no way that I can think of that opening a query using docmd.OpenQuery qd.NAME is going to return the SQL, rather than a record set.

Try the code again, copying/pasting to a public module (replacing my strSQL with yours). Save the module then, from the debug (immediate) window type: Call ShowSQL <return>

Bob

I apologize, but all I seem to return is the SQL string, not the recordset. Clearly I'm missing something, but I don't know what.

 

[knelson]

If you want to take a SQL statement and actually run it then use the DoCmd.RunSQL method and pass your SQL string as an argument.

 

[michaelc80]

If you want to take a SQL statement and actually run it then use the DoCmd.RunSQL method and pass your SQL string as an argument.

Again you'll have to forgive me-I don't know how to write the code to do that.

In all honesty, it's probably going to be easier to just create a form with 200+ buttons-one for each error. I had thought it would be fairly simple to run the SQL string, but apparently it's not.

Thanks for the help though.

 

[raskew]

Hi -
If the SQL you posted is what you're trying to run, think the problem may be in your syntax. Are you using the query grid to create your desired query? Here's an example I created from the query grid (and it works). Note the handling of the WHERE statement.

SELECT Table1.*
FROM Table1
WHERE (((Table1.ItemSay) Is Null));

Oops. Just tried it with the parenthesis removed and it also works. Could you post a sample database containing your table.

Bob

 

[knelson]

I'm not sure if I understand what you want exactly, but I think you could do something like the following. It will do your first select statement and pull out the SQL strings from your table then insert it into a temporary table. It then puts assigns the first value in the temp table as the SQL statement for your query, so your query should now run the SQL code that was extracted from the DATA_SCRUB_SQL table. Finally it opens the query and deletes the temp table.

Private Sub btnGetErr_Click()
dim qdf as QueryDef
Dim strSQL As String

strSQL = "INSERT INTO tblTemp SELECT SQL FROM DATA_SCRUB_SQL " &_ 
"WHERE Error_ID = Forms!frmErrors!txtErrorNumber;"

DoCmd.RunSQL strSQL

Dim rs As Recordset
Set rs = Application.CurrentDb.OpenRecordset("tblTemp",dbOpenTable)
rs.MoveFirst

Set qdf = Application.CurrentDb.QueryDefs("qryResults")
qdf.SQL = rs.Fields(0).Value

DoCmd.OpenQuery "qryResults", acViewNormal, acReadOnly

rs.Close
Set rs = Nothing
Set qdf = Nothing
DoCmd.RunSQL "DROP TABLE tblTemp"

End Sub

Is this more like what you are looking for?