Create and open a dataset in code

[Guest]

Sample code to create a dataset and step through it

 

[Ted Allen]

Hi Rich,

Another method from the one Tim posted to step through a
recordset would be similar to the following (in DAO -
watch wrapping):

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("YourTableName", dbOpenTable)
With rst
Do While Not .EOF
.Edit
'Put your code here to gather or edit data such as:
![SomeFieldName] = SomeVariable
.Update
.MoveNext
Loop
End With

Note that this code opened the recordset based on a table
name, but you can also enter a query name or sql text.
You can also define a recordset to be a form's recordset.

There are many commands to navigate within a recordset
(such as Seek or FindFirst), or you can just step through
the records as above, depending on what you need to do
with the recordset.

You can also batch changes into transactions, and commit
or roll back the entire transaction at the end.

Hope that helps.

-Ted Allen

 

[Joel]

Ted:

Tried this code in Access XP and it doesn't work. What's
up?? Could you help with code to do samething in XP??

You're Great! TIA,

Joel

-----Original Message-----
Hi Rich,

Another method from the one Tim posted to step through a
recordset would be similar to the following (in DAO -
watch wrapping):

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("YourTableName", dbOpenTable)
With rst
Do While Not .EOF
.Edit
'Put your code here to gather or edit data such as:
![SomeFieldName] = SomeVariable
.Update
.MoveNext
Loop
End With

Note that this code opened the recordset based on a table
name, but you can also enter a query name or sql text.
You can also define a recordset to be a form's recordset.

There are many commands to navigate within a recordset
(such as Seek or FindFirst), or you can just step through
the records as above, depending on what you need to do
with the recordset.

You can also batch changes into transactions, and commit
or roll back the entire transaction at the end.

Hope that helps.

-Ted Allen

-----Original Message-----
Sample code to create a dataset and step through it
.

.

 

[Ted Allen]

Hi Joel,

This code should work in Access XP (that's also what I'm
using) provided you have referenced DAO in your VB
Library. To check, choose Tools|References from the menu
in the VB Code Window and check to see if DAO 3.6 is
checked. If not, checking it may resolve your problem.
If it is already checked, post back with your code and
I'll see if I can spot anything.

-Ted Allen

-----Original Message-----
Ted:

Tried this code in Access XP and it doesn't work. What's
up?? Could you help with code to do samething in XP??

You're Great! TIA,

Joel

-----Original Message-----
Hi Rich,

Another method from the one Tim posted to step through a
recordset would be similar to the following (in DAO -
watch wrapping):

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("YourTableName", dbOpenTable)
With rst
Do While Not .EOF
.Edit
'Put your code here to gather or edit data such as:
![SomeFieldName] = SomeVariable
.Update
.MoveNext
Loop
End With

Note that this code opened the recordset based on a table
name, but you can also enter a query name or sql text.
You can also define a recordset to be a form's recordset.

There are many commands to navigate within a recordset
(such as Seek or FindFirst), or you can just step through
the records as above, depending on what you need to do
with the recordset.

You can also batch changes into transactions, and commit
or roll back the entire transaction at the end.

Hope that helps.

-Ted Allen

-----Original Message-----
Sample code to create a dataset and step through it
.

.

.