Access VBA Create a Temporary Query in SQL that another SQL query uses

Question

I have a question about running SQL within Access VBA. I want to basically create a query in vba but not have it be a query in my database. It just sits there, invisible, until an operation using it is complete, and then it erases.

My code is below (simplified to focus on this issue). I don't think I'm doing this right because I think on the 2nd query I need to do the " & ECStart2SQL & " thing, but I don't know how it will work when I need to reference a value.

Private Sub TimeReportingButton_Click()
'THIS IS THE 1ST QUERY
Dim ECStart2SQL As String
ECStart2SQL = "SELECT D1.[#], (SELECT Count(*) FROM tbl_Data D2 WHERE D2.[#] = D1.[#] AND D2.[tsUpdated] <= D1.[tsUpdated]) AS Sequence, D1.tsUpdated FROM tbl_Data AS D1 WHERE ((((SELECT Count(*) FROM tbl_Data D2 WHERE D2.[#] = D1.[#] AND D2.[tsUpdated] <= D1.[tsUpdated]))=1)) ORDER BY D1.[#]"


'THIS IS THE 2ND QUERY, THE UPDATE QUERY, THAT REFERS TO THE FIRST ONE
Dim UpdECStartRank2SQL As String
UpdECStartRank2SQL = "UPDATE tbl_TimeReporting INNER JOIN ECStart2SQL ON tbl_TimeReporting.[#] = ECStart2SQL.[#] SET tbl_TimeReporting.ECStart = [ECStart2SQL]![tsUpdated] WHERE (((tbl_TimeReporting.ECStart) Is Null) AND ((tbl_TimeReporting.Sequence)=2))"

'OPERATION
DoCmd.SetWarnings False
DoCmd.RunSQL UpdECStartRank2SQL
MsgBox "Done!", , "Done!"
DoCmd.SetWarnings True

End Sub

Please, if you can offer any help, I'd appreciate it. Thanks!!

Marek Stejskal · Accepted Answer · 2015-01-31 23:09:49Z

5

To create a query you use a CreateQueryDef command

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.CreateQueryDef("MyQuery", "SELECT * FROM Table1")

Now you can use the query in SQL commands like you normally would, for example

"UPDATE MyTable INNER JOIN MyQuery ON ..."

And to delete it you use QueryDefs.Delete

CurrentDb.QueryDefs.Delete "MyQuery"

edited Jan 31, 2015 at 23:09

answered Jan 31, 2015 at 22:26

Marek Stejskal

2,7181 gold badge22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Parfait Over a year ago

To add Marek,because he is running an action query, DAO.Recordset will not be needed. The original Update SQL in the OP's post will work instead.

Marek Stejskal Over a year ago

Sure, I only meant that as an example. But I guess I should change it so it won't cause any confusion

msim Over a year ago

this is excellent, my code works now and I am looking forward to learning more about this. Thanks so much!!

Collectives™ on Stack Overflow

Access VBA Create a Temporary Query in SQL that another SQL query uses

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related