Select Statements with DAO 
Author Message
 Select Statements with DAO

Hi:

I am trying to run select statements with DAO and VBA to
retrieve values from my table in Access 97. However, I'm
not getting the result of my query outputted to the
screen, nor am I getting any errors. I've tried it 2
different ways: The following is a code snippet for each
way that I've tried:

First way:

Dim quer As String, queryname As String, myquery As
QueryDef

queryname = "New Query Object"
quer = "select * from temptable;"

Set myquery = DBEngine(0)(0).CreateQueryDef()
myquery.Name = queryname
myquery.SQL = quer

DBEngine(0)(0).QueryDefs.Append myquery
myquery.Close

Second Way:

Dim mydb As Database, myrs As Recordset, quer As String

Set mydb = DBEngine(0)(0)
quer = "select * from temptable;"

Set myrs = mydb.OpenRecordset(quer)

Debug.Print myrs.Fields("tempname")
Debug.Print myrs.Fields("tempdist")

In either of these 2 scenarios, I'm not getting the result
of my query outputted to the screen. Can someone please
tell me what I'm doing wrong?

Thanks,

Dar



Sat, 11 Jun 2005 01:38:00 GMT  
 Select Statements with DAO

First way:

Dim db as Database,strSQL As String, qdf As QueryDef

strSQL = "select * from temptable;"
Set db=CurrentDB
Set qdf = db.CreateQueryDef("New Query Object",strSQL)
db.close
Set qdf=Nothing
Set db=Nothing

Second Way:

Dim db as Database,strSQL As String, rst as Recordset

strSQL = "select * from temptable;"
Set db=CurrentDB
Set rst = db.OpenRecordset(strSQL)

Make sure the table exists.
Good luck.



Sat, 11 Jun 2005 03:26:53 GMT  
 Select Statements with DAO
Hi Dar,

The first question is how do you really want to use this query? Do you want
a recordset that you can access with VBA, do you really just want to open
the query in datasheet view (not recommended since you have no control over
the data at this point unless the query is read only), or are you creating a
query that some other process will use?

If all you want to do is build and open a query in datasheet view, you can
use the following code where you would replace the assignment to strSQL with
your actual SQL, and replace 'qryMyQuery' with the name you want for your
query. However you should also note that building queries this way can bloat
the database so you will want to be sure that it is compacted routinely.

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "Select * from Customers order by Customerid;"
db.CreateQueryDef "qryMyQuery", strSQL
DoCmd.OpenQuery "qryMyQuery"

When you open a recordset you do not have to build a querydef first. You
just supply the SQL to the OpenRecordset method. However, when you open a
recordset you must use DAO (or ADO) to access the contents of the
recordset - it will not be displayed on the screen as though you had opened
it from the Database window.  Your second set of code will open a recordset
but the debug.print statements are only going to show values of  'tempname'
and
'tempdist' from the first row returned and this display is going to go to
the immediate window of the VB Editor (In the VBE View->Immediate Window).

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Quote:

> Hi:

> I am trying to run select statements with DAO and VBA to
> retrieve values from my table in Access 97. However, I'm
> not getting the result of my query outputted to the
> screen, nor am I getting any errors. I've tried it 2
> different ways: The following is a code snippet for each
> way that I've tried:

> First way:

> Dim quer As String, queryname As String, myquery As
> QueryDef

> queryname = "New Query Object"
> quer = "select * from temptable;"

> Set myquery = DBEngine(0)(0).CreateQueryDef()
> myquery.Name = queryname
> myquery.SQL = quer

> DBEngine(0)(0).QueryDefs.Append myquery
> myquery.Close

> Second Way:

> Dim mydb As Database, myrs As Recordset, quer As String

> Set mydb = DBEngine(0)(0)
> quer = "select * from temptable;"

> Set myrs = mydb.OpenRecordset(quer)

> Debug.Print myrs.Fields("tempname")
> Debug.Print myrs.Fields("tempdist")

> In either of these 2 scenarios, I'm not getting the result
> of my query outputted to the screen. Can someone please
> tell me what I'm doing wrong?

> Thanks,

> Dar



Sat, 11 Jun 2005 03:27:59 GMT  
 Select Statements with DAO
Try this method:

Public Sub DoIt()
Dim db As Database, qd As QueryDef, str As String

Set db = CurrentDb

On Error Resume Next
db.QueryDefs.Delete "thequery"
On Error GoTo 0

str = "Select * From temptable"

Set qd = db.CreateQueryDef("thequery", str)

DoCmd.OpenQuery "thequery", acViewNormal

Set qd = Nothing
Set db = Nothing
End Sub

This will create a query, and then the OpenQuery method will display it to
the screen.

-Louis


Quote:
> Hi:

> I am trying to run select statements with DAO and VBA to
> retrieve values from my table in Access 97. However, I'm
> not getting the result of my query outputted to the
> screen, nor am I getting any errors. I've tried it 2
> different ways: The following is a code snippet for each
> way that I've tried:

> First way:

> Dim quer As String, queryname As String, myquery As
> QueryDef

> queryname = "New Query Object"
> quer = "select * from temptable;"

> Set myquery = DBEngine(0)(0).CreateQueryDef()
> myquery.Name = queryname
> myquery.SQL = quer

> DBEngine(0)(0).QueryDefs.Append myquery
> myquery.Close

> Second Way:

> Dim mydb As Database, myrs As Recordset, quer As String

> Set mydb = DBEngine(0)(0)
> quer = "select * from temptable;"

> Set myrs = mydb.OpenRecordset(quer)

> Debug.Print myrs.Fields("tempname")
> Debug.Print myrs.Fields("tempdist")

> In either of these 2 scenarios, I'm not getting the result
> of my query outputted to the screen. Can someone please
> tell me what I'm doing wrong?

> Thanks,

> Dar



Sat, 11 Jun 2005 03:33:57 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. select statements with DAO

2. How to select the current record only with SELECT statement

3. Type mismatch on openrecordset statement in DAO

4. referencing textbox within dao statement

5. SQL Statement edit access db with DAO

6. Executing UPDATE SQL statements in DAO 3.5 and VB5

7. Prob: VB5 closes after DAO statements finish

8. DAO Sql statement doesnt work in an ADO environment

9. Problem with SELECT statement

10. Access: Err 2342 in SQL SELECT Statement in VBA

11. Help Please with Select Statement using AND

12. Counting records returned by SQL Select statement

 

 
Powered by phpBB® Forum Software