victorlong
asked on
about the query in ADO
After creating a query, something like
Set OldDb = OpenDatabase("c;\file.mdb" )
Set NewQry = OldDb.CreateQueryDef("Qry1 ")
NewQry.SQL = "select * from table1 where field1 like '*1*'"
How to access the selected records? Say debug.print first record.
Moreover, why not use
Set OldDb = OpenDatabase("c;\file.mdb" )
Set NewSet = OldDb.OpenRecordset("selec t * from table1 where field1 like '*1*'")
which looks much easy and one may directely assess the selected records by the recordset.
Set OldDb = OpenDatabase("c;\file.mdb"
Set NewQry = OldDb.CreateQueryDef("Qry1
NewQry.SQL = "select * from table1 where field1 like '*1*'"
How to access the selected records? Say debug.print first record.
Moreover, why not use
Set OldDb = OpenDatabase("c;\file.mdb"
Set NewSet = OldDb.OpenRecordset("selec
which looks much easy and one may directely assess the selected records by the recordset.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Sekans. I want to make sure that
1. We can do:
Set NewQry = OldDb.CreateQueryDef("Qry1 ")
NewQry.SQL = "select * from table1 where field1 like '*1*'"
Set NewSet = OldDb.OpenRecordset("Qry1" )
2. The above is the same as:
Qry2 = "select * from table1 where field1 like '*1*'"
Set NewSet = OldDb.OpenRecordset(Qry2)
3. You like the second method.
1. We can do:
Set NewQry = OldDb.CreateQueryDef("Qry1
NewQry.SQL = "select * from table1 where field1 like '*1*'"
Set NewSet = OldDb.OpenRecordset("Qry1"
2. The above is the same as:
Qry2 = "select * from table1 where field1 like '*1*'"
Set NewSet = OldDb.OpenRecordset(Qry2)
3. You like the second method.
1. Yes, this will create a new query object in the specified database, then return the records based on that query.
2. The two methods are almost the same. The difference is, the first method actually creates a new query object in your database, while the second, only returns the desired records.
3. Other experts may disagree, but I definately prefer the second method. I don't see why you should junk up your database with queries, when you don't need to. As you can see, the difference in code is minimal.
Hope this helps,
Sekans
2. The two methods are almost the same. The difference is, the first method actually creates a new query object in your database, while the second, only returns the desired records.
3. Other experts may disagree, but I definately prefer the second method. I don't see why you should junk up your database with queries, when you don't need to. As you can see, the difference in code is minimal.
Hope this helps,
Sekans
ASKER
Hi Sekans
Thank you for your help.
Thank you for your help.
Your very welcome,
Sekans
Sekans
Do Until NewSet.EOF = True
'Do something
NewSet.MoveNext
Loop
Sekans