Need example VBA code to continue

[timfgo]

I am trying to do something that in my mind should be incredibly
simple. I want to use VBA to store data from an SQL select query. I've
come to understand I'm going to need to use DAO to complete this, which
I'm not opposed to, but I dont know it and I cant find any simplified
examples of how to do a single SQL query. In pseudo this is basically
what I want.

Function storage As String
Dim sqlvariable

sqlvariable = "select * from Table1 where ID = 1"

I am completing this from a module in Access 2000, and it will be an
onclick function through a form. Does anyone have a stripped down
example of how I can get ssql to hold the data from the select
statement?

 

[Douglas J. Steele]

It's not clear to me what you're trying to do.

Are you trying to get the SQL that's associated with a stored query?

CurrentDb().QueryDefs("NameOfStoredQuery").SQL

 

[Bill Edwards]

First sub uses DAO; second sub uses ADO to do essentially the same thing.

Public Sub DAO_Sub()
On Error GoTo Err_Label
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()
strSQL = "SELECT * FROM tblClient WHERE tblClient.ClientKey = 4"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
Do While Not rst.EOF
MsgBox rst.Fields("ClientKey")
rst.MoveNext
Loop
Exit_Label:
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
Exit Sub
Err_Label:
MsgBox Err.Description
Resume Exit_Label
End Sub

Public Sub ADO_Test()
On Error GoTo Err_Label
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Set cnn = CurrentProject.Connection
strSQL = "SELECT * FROM tblClient WHERE tblCLient.ClientKey = 4"
rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
rst.MoveFirst
Do While Not rst.EOF
MsgBox rst.Fields("ClientKey")
rst.MoveNext
Loop
Exit_Label:
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
Err_Label:
MsgBox Err.Description
Resume Exit_Label
End Sub

 

[George Nicholson]

If you are only want a single value, you can use the Dlookup function:

sqlVariable = Dlookup("SomeField","Table1","ID = 1")

This would be the equivalent of the following SQL, which is different from
what you asked:
"SELECT SomeField FROM Table1 WHERE ID = 1"


If you really do want/need a whole record, or multiple records, then you
need DAO (or ADO..).
(Barebones example):

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strValue1 as string
Dim strValue2 as string

Set db = CurrentDb

strSQL = "SELECT * FROM Table1 WHERE ID = 1"
Set rs = db.OpenRecordset(strSQL)

'Exit if recordset has no records
If rs.EOF And rs.BOF Then Exit Sub
rs.MoveFirst
Do Until rs.EOF
strValue1= rs!SomeFieldName
strValue2 = rs!AnotherFieldName
'Do something with those values...
Loop
rs.Close

Set rs = Nothing
Set db = Nothing

FYI: EOF & BOF refer to EndOf or BeginningOf File properties, respectively.
They represent markers before & after any records (they are *not* the first
& last records).
If EOF is True, you have gone past all records. If BOF & EOF are both true,
you have an empty recordset and trying to navigate records (rs.MoveFirst)
would raise an error.


HTH,

 

[George Nicholson]

very, very oops.

missing "rs.MoveNext" (just before "Loop")

otherwise you'll never move off first record & find yourself in an endless
loop.

 

[timfgo]

Double Woot,

That barebones example is exactly what I'm looking for. Everything else
was so horrendously complex I could not understand where DAO ended and
builtin methods began. Thanks!