Simple SQL not working in Access

[Sircal87]

Hello,

I've been trying to figure out simply how to use SQL in the MS Access's Visual Basic editor, and so far I've literally run into 20 unique errors over the last couple of days. Now, I've finally got a super simplified script that doesn't return any errors, but it doesn't display anything either, and I can't figure out why. Any ideas?

 Private Sub Command7_Click() 
 
Dim db As Database
Dim rs As DAO.Recordset
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM Temporary;")
 
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
 
End Sub
 

(It's linked to a form, so that when someone clicks the submit button this code is called).

Thank you!

[DrBunchman]

The reason that this doesn't do anything is because you've created your recordset but not done anything with it like bound it to a control.

I've moved this to the MS Access Forum where I think it's more likely you'll be able to get help than the ASP Forum.

Dr B

[Stewart Ross]

As Dr B said, you have opened a recordset but not done anything with it. The OpenRecordset method is intended for further code processing - loop processing of individual records, transfer of the recordset to Excel, searching for matching records, and so on.

If what you want to do is to set your form's recordsource to an SQL statement in response the on-click event of your command button you don't need the DAO recordset at all. The following single line of code will do this on its own (assuming the command button is on the same form):

Me.Recordsource = "SELECT * FROM Temporary;"

If the form is not the same as the one on which the button is placed the same principle applies but you need to supply the full reference to the form's name. The form has to be open for this to work (you can't change the recordsource for an unopened form this way).

forms("yourformname").Recordsource = "SELECT * FROM Temporary;"

etc.

You can use a similar approach in a form's On Load event to set its recordsource dynamically.

-Stewart