VBA to run query record

[meter_man]

I have a db that holds many SQL statements in a memo feild. These SQL
statements can have STARTDATE and ENDDATE parameters, however they are
currently stored as plain SQL with the dates written in.

How should I save the code with the STARTDATE and ENDDATE parameters so I
can then open a form, select a query, fill in the dates (if required) and
run the SQL? i.e. How should the SQL read?
I'm Guessing
1) save Code as : "Select * from table where PostDate >= |STARTDATE| and
PostDate < |ENDDATE|"
2) Set mySQL = SQLfield where record is selected on form.
3) Replace |STARTDATE| with form's Start Date and |ENDDATE| with form's End
Date using a find/replace function? (I need help here.)
4) docmd.Execute mySQL

Since I'm new. I wanted to run this theory past you kind folks.
Thanks
meterman

 

[Stefan Hoffmann]

hi,

can then open a form, select a query, fill in the dates (if required) and
run the SQL? i.e. How should the SQL read?
I'm Guessing
1) save Code as : "Select * from table where PostDate >= |STARTDATE| and
PostDate < |ENDDATE|"

Yes. Choose your parameter delimiter carfully, but |param| should do it.

2) Set mySQL = SQLfield where record is selected on form.
3) Replace |STARTDATE| with form's Start Date and |ENDDATE| with form's End
Date using a find/replace function? (I need help here.)

mySQL = rs![SQL]
mySQL = Replace(mySQL, "|STARTDATE|", _
Format(Startdate, "\#m\/d\/yyyy hh\:nn\:ss\#"))
mySQL = Replace(mySQL, "|ENDDATE|", _
Format(Enddate, "\#m\/d\/yyyy hh\:nn\:ss\#"))

4) docmd.Execute mySQL

Yes. Better:

Dim db as DAO.Database

Set db = CurrentDb

db.Execute mySQL, dbFailOnError

MsgBox "Records affected: " & db.RecordsAffected



mfG
--> stefan <--

 

[Douglas J. Steele]

You can't use Execute with a Select query: it's only for action queries
(UPDATE, DELETE, INSERT INTO). What exactly are you hoping to achieve with
that SQL?

 

[Guest]

My first suggestion would be to save your queries as stored queries rather
than in a memo field. Here is some sample code that may be helpful to you
that does what you are trying to do:

strSQL = CreateSQL(strWhere, strXlQuery)
Set dbf = CurrentDb
Set qdfs = dbf.QueryDefs
Set qdf = dbf.CreateQueryDef("_UPOTemp", strSQL)

'Export the Query
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qdf.Name, _
strSaveFileName, True, strRangeName
Call Shell("Excel " & Chr$(34) & strSaveFileName & Chr$(34),
vbMaximizedFocus)
qdfs.Delete qdf.Name
End If
End If
************************

Private Function CreateSQL(strStartWhere As String, strXlQuery As String) As
String
Dim strWhere As String
Dim qdfXl As QueryDef
Dim qdf As QueryDef
Dim dbf As dao.Database
Dim qdfs As QueryDefs

On Error GoTo CreateSQL_Error

'Get the Template Query
Set dbf = CurrentDb
Set qdfs = dbf.QueryDefs
Set qdfXl = CurrentDb.QueryDefs(strXlQuery)
strSQL = qdfXl.SQL

'Delete the old query in case an error left it hanging
For Each qdf In qdfs
If qdf.Name = "_UPOTemp" Then
qdfs.Delete qdf.Name
Exit For
End If
Next qdf

'They all start with this
strWhere = "WHERE Actual_res_export.BillCat = ""unbillable"" And
Abs([jan]) + " _
& "Abs([feb]) + Abs([mar]) + Abs([apr]) + Abs([may]) + Abs([jun]) +
" _
& "Abs([Jul]) + Abs([aug]) + Abs([sep]) + Abs([oct]) + Abs([nov]) +
" _
& "Abs([dec]) <> 0"

'Check for ProjectID
If InStr(strStartWhere, "ProjectID") = 0 Then
strWhere = strWhere & " AND Actual_Res_export.ProjectID <>
""billable"""
End If
'Check for Pool
If InStr(strStartWhere, "Pool2") = 0 Then
strWhere = strWhere & " AND Actual_Res_export.Pool2
IN(""SEL"",""SOL"",""SUP"",""PSOL"")"
End If
If Len(strStartWhere) > 0 Then
strWhere = strWhere & " AND " & strStartWhere & " ORDER BY"
Else
strWhere = strWhere & " ORDER BY"
End If
strSQL = Replace(strSQL, "ORDER BY", strWhere)
CreateSQL = strSQL

CreateSQL_Exit:

On Error Resume Next

Set qdf = Nothing
Set qdfXl = Nothing
Set qdfs = Nothing
Set dbf = Nothing

 

[meter_man]

I run queries from inside an excel report template. Each template has its
own SQL written on a apreadsheet. These can be very complex SQLs. The excel
fiile runs that query, creates 5 reports from the results, and publishes to
a specific directory structure. We have a Db to track the development of
these reports and the associated SQL. In Excel the StartDate and EndDates
are simply inserted as a reference to a calculated cell value.

Sometimes we run VBA code to export the finished SQL code to Word for
analysis or to then copy the text into our Db for version tracking.

What I want to do is to be able to copy the SQL string into Access with a
parameter string. This might have to be accomplished using a slight change
in the VBA to export to word. IE if the cell formula points to The ranges
"STARTDATE" or "ENDDATE" then export that piece of SQL as "|STARTDATE|" and
"|ENDDATE|" instead of the date value string. I would then have that date
formula setup as another field in the Db so when the code runs, the
parameters can be retrieved and placed into the SQL string for processing. I
am going against a DB2 database. The code suggestion offered by Mr. Hoffman
seems to a very good start and may be the missing link. I can then select a
target for the resulting recordset.
mySQL = rs![SQL]
mySQL = Replace(mySQL, "|STARTDATE|", _
Format(Startdate, "\#m\/d\/yyyy hh\:nn\:ss\#"))
mySQL = Replace(mySQL, "|ENDDATE|", _
Format(Enddate, "\#m\/d\/yyyy hh\:nn\:ss\#"))

I hope I can format as Format(StartDate,"yyyy-mm-dd").

Does his suggestion seem reasonable?

 

[meter_man]

stephan,
This seems right on target! Thanks for the suggestion.
Meter_Man

hi,

can then open a form, select a query, fill in the dates (if required) and
run the SQL? i.e. How should the SQL read?
I'm Guessing
1) save Code as : "Select * from table where PostDate >= |STARTDATE| and
PostDate < |ENDDATE|"

Yes. Choose your parameter delimiter carfully, but |param| should do it.

2) Set mySQL = SQLfield where record is selected on form.
3) Replace |STARTDATE| with form's Start Date and |ENDDATE| with form's
End Date using a find/replace function? (I need help here.)

mySQL = rs![SQL]
mySQL = Replace(mySQL, "|STARTDATE|", _
Format(Startdate, "\#m\/d\/yyyy hh\:nn\:ss\#"))
mySQL = Replace(mySQL, "|ENDDATE|", _
Format(Enddate, "\#m\/d\/yyyy hh\:nn\:ss\#"))

4) docmd.Execute mySQL

Yes. Better:

Dim db as DAO.Database

Set db = CurrentDb

db.Execute mySQL, dbFailOnError

MsgBox "Records affected: " & db.RecordsAffected



mfG
--> stefan <--

 

[meter_man]

I am not sure how to store the queries as stored queries, but it sounds
interesting. Thank you so much for the complete code. I will have to do some
testing.
I will do some research on this method.
Thanks again.
Meter_man

My first suggestion would be to save your queries as stored queries rather
than in a memo field. Here is some sample code that may be helpful to you
that does what you are trying to do:

strSQL = CreateSQL(strWhere, strXlQuery)
Set dbf = CurrentDb
Set qdfs = dbf.QueryDefs
Set qdf = dbf.CreateQueryDef("_UPOTemp", strSQL)

'Export the Query
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qdf.Name, _
strSaveFileName, True, strRangeName
Call Shell("Excel " & Chr$(34) & strSaveFileName & Chr$(34),
vbMaximizedFocus)
qdfs.Delete qdf.Name
End If
End If
************************

Private Function CreateSQL(strStartWhere As String, strXlQuery As String)
As
String
Dim strWhere As String
Dim qdfXl As QueryDef
Dim qdf As QueryDef
Dim dbf As dao.Database
Dim qdfs As QueryDefs

On Error GoTo CreateSQL_Error

'Get the Template Query
Set dbf = CurrentDb
Set qdfs = dbf.QueryDefs
Set qdfXl = CurrentDb.QueryDefs(strXlQuery)
strSQL = qdfXl.SQL

'Delete the old query in case an error left it hanging
For Each qdf In qdfs
If qdf.Name = "_UPOTemp" Then
qdfs.Delete qdf.Name
Exit For
End If
Next qdf

'They all start with this
strWhere = "WHERE Actual_res_export.BillCat = ""unbillable"" And
Abs([jan]) + " _
& "Abs([feb]) + Abs([mar]) + Abs([apr]) + Abs([may]) + Abs([jun]) +
" _
& "Abs([Jul]) + Abs([aug]) + Abs([sep]) + Abs([oct]) + Abs([nov]) +
" _
& "Abs([dec]) <> 0"

'Check for ProjectID
If InStr(strStartWhere, "ProjectID") = 0 Then
strWhere = strWhere & " AND Actual_Res_export.ProjectID <>
""billable"""
End If
'Check for Pool
If InStr(strStartWhere, "Pool2") = 0 Then
strWhere = strWhere & " AND Actual_Res_export.Pool2
IN(""SEL"",""SOL"",""SUP"",""PSOL"")"
End If
If Len(strStartWhere) > 0 Then
strWhere = strWhere & " AND " & strStartWhere & " ORDER BY"
Else
strWhere = strWhere & " ORDER BY"
End If
strSQL = Replace(strSQL, "ORDER BY", strWhere)
CreateSQL = strSQL

CreateSQL_Exit:

On Error Resume Next

Set qdf = Nothing
Set qdfXl = Nothing
Set qdfs = Nothing
Set dbf = Nothing

I have a db that holds many SQL statements in a memo feild. These SQL
statements can have STARTDATE and ENDDATE parameters, however they are
currently stored as plain SQL with the dates written in.

How should I save the code with the STARTDATE and ENDDATE parameters so I
can then open a form, select a query, fill in the dates (if required) and
run the SQL? i.e. How should the SQL read?
I'm Guessing
1) save Code as : "Select * from table where PostDate >= |STARTDATE| and
PostDate < |ENDDATE|"
2) Set mySQL = SQLfield where record is selected on form.
3) Replace |STARTDATE| with form's Start Date and |ENDDATE| with form's
End
Date using a find/replace function? (I need help here.)
4) docmd.Execute mySQL

Since I'm new. I wanted to run this theory past you kind folks.
Thanks
meterman