[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3084
  • Last Modified:

Run a SQL select statement in VBA

Hi
How would I, in DBA code, run a SQL select statement and then display the number of records it returns?

The statement is:

"SELECT Contact_Number FROM Contacts WHERE Contacts.Tag1=Yes"

I know I can make this as a Query, but there are nine to make and I would rather I did it in code.

Thanks

Corin
0
corin_pearce
Asked:
corin_pearce
  • 2
  • 2
1 Solution
 
walterecookCommented:
set a reference to MS DAO 3.6 object library

dim rs as dao.recordset
dim myCount
set rs = currentdb.openrecordset("SELECT Contact_Number FROM Contacts WHERE Contacts.Tag1=Yes")
rs.movelast
myCount = rs.recordcount
set rs = nothing

That's one way.  I didn't test this, but it looks alright.

Hope it helps
Walt

0
 
fulscherCommented:
How about

    Dim SQL As String
    Dim RS As Recordset
    SQL = "SELECT Contact_Number FROM Contacts WHERE Contacts.Tag1=Yes"
   
    Set RS = CurrentDb.OpenRecordset(SQL)
    RS.MoveLast
   
    MsgBox RS.RecordCount

Hope this helps
Jan
0
 
fulscherCommented:
Sorry Walt - didn't notice your post. Jan
0
 
walterecookCommented:
Happens all the time!  No worries, fulscher

Walt
0
 
corin_pearceAuthor Commented:
Didn't know it was that simple!
Thanks very much, was playing around withQueryDef etc for ages...
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now