How can I create and see the results of a SELECT query in VBA?

sistemalan

Registered User.
Local time
Today, 22:54
Joined
Jun 19, 2009
Messages
77
Hi there,

Thanks for taking the time to look.

I was beginning to think I had got the hang of creating queries on the fly in VBA. After several hours I have just learned that docmd.runSQL does not work for simple SELECT statements. I'm not going to worry about why that would be. I'm sure there's a jolly good reason.

So... I am able to dynamically create the SQL string for the SELECT statement that I need.

How do I run it? It just needs to return results as a datasheet so I can see them. I'm running Access 2013. I've been searching on this subject for quite a while and found many references to DAO, ADO, ADODAOD, YODELAEYYOUDELAYIO! and other things I don't yet understand :-)

Hopefully this is simple.

Thanks

Alan
 
You can not use CurrentDB.Execute with a SELECT query. It is used with action queries only INSERT/UPDATE/DELETE.

Here's a samplevba with a recordset from an existing procedure
Code:
Sub testSelectByDate()
 Dim db As DAO.Database
      Dim rs As DAO.Recordset
10    Set db = CurrentDb
20    Set rs = db.OpenRecordset("select  * from Vmaintenance where Month(servicedate) = 3")
30    Do While Not rs.EOF
40      Debug.Print rs!servicedate
50      rs.MoveNext
60    Loop
70    Debug.Print rs.RecordCount & "  Records in Month 3"
End Sub
 
You can not use CurrentDB.Execute with a SELECT query. It is used with action queries only INSERT/UPDATE/DELETE.

Oops, :o my apologies for getting it wrong.
 
@Gasman: from your own link:

A string expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement. Include an IN clause if you want to access another database.

I urge you - and OP - in the future to first find and carefully read information in the documentation. That will save you both a lot of time and pain, and the docs are VERY comprehensive, but sometimes not very clear. But learning how to find docs and examples is IMHO at leas as important, and time saving, as being able to write code.
 
Hi all,

Thanks for the advice.

I have tried using the code suggested as follows. At present it does nothing. Further advice would be much appreciated...

Code:
Private Sub lbl_OtherKids_DblClick(Cancel As Integer)

Dim SQLString As String

SQLString = "SELECT T_Children.ChildFirstName, T_Children.ChildSurname" _
              & " FROM T_Relationships INNER JOIN T_Children ON T_Relationships.ChildID = T_Children.ChildID" _
              & " WHERE (((T_Relationships.ChildID) <> " & [Forms]![Main]![ChildID] & ") And (( T_Relationships.AdultID) = " & [Forms]![Main]![SF_Adults].[Form]![AdultID] _
              & ")) ORDER BY T_Children.ChildFirstName;"

Dim db As DAO.Database
      Dim rs As DAO.Recordset
    Set db = CurrentDb
  Set rs = db.OpenRecordset(SQLString)
  Do While Not rs.EOF
     rs.MoveNext
   Loop


End Sub

Thanks,

Alan
 
Your code isn't doing anything because there is no action.
You need to do something here
Code:
Private Sub lbl_OtherKids_DblClick(Cancel As Integer)

Dim SQLString As String

SQLString = "SELECT T_Children.ChildFirstName, T_Children.ChildSurname" _
              & " FROM T_Relationships INNER JOIN T_Children ON T_Relationships.ChildID = T_Children.ChildID" _
              & " WHERE (((T_Relationships.ChildID) <> " & [Forms]![Main]![ChildID] & ") And (( T_Relationships.AdultID) = " & [Forms]![Main]![SF_Adults].[Form]![AdultID] _
              & ")) ORDER BY T_Children.ChildFirstName;"

Dim db As DAO.Database
      Dim rs As DAO.Recordset
    Set db = CurrentDb
  Set rs = db.OpenRecordset(SQLString)
  Do While Not rs.EOF
'
'  HERE<!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'eg This will print  firstname  and Lastname to the immediate window
 Debug.print rs!T_Children.ChildFirstName & "   " & rs!T_Children.ChildSurname
     rs.MoveNext
   Loop


End Sub
 
Thanks for the suggestion, however I don't see the value to the end user of being able to view things in the immediate window. I want to see data on a datasheet.

I found a solution which works for me, which involved making a dummy query which I called "Info", and then redefining it's SQL and then opening it using the command docmd.openquery. Here's the code I ended up with in case it helps anyone with the same issue.

Code:
Dim strSQL

strSQL = "SELECT T_Children.ChildFirstName, T_Children.ChildSurname" _
              & " FROM T_Relationships INNER JOIN T_Children ON T_Relationships.ChildID = T_Children.ChildID" _
              & " WHERE (((T_Relationships.ChildID) <> " & [Forms]![Main]![ChildID] & ") And (( T_Relationships.AdultID) = " & [Forms]![Main]![SF_Adults].[Form]![AdultID] _
              & ")) ORDER BY T_Children.ChildFirstName;"

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("Info")
qdf.SQL = strSQL
DoCmd.OpenQuery ("Info")
Set qdf = Nothing
 

Users who are viewing this thread

Back
Top Bottom