open an existing table for read and write

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to open an existing table for both read and write capabilities. What commands will do that and do I have to define the database table that is being opened, that is the schema? I can not seem to get a table open so that I can read the fields(rows) by column or field. When I try to dim (define) a variable as being equal to one of the values, I either get some off the wall entry or 'error 2023' which I have not been able to get an explaination of what that error means. I am doing this from a module within the database in which the table exists. Thanks for any help.
 
Hi,
You have to open a recordset based on the table.

Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("yourTable",dbOpenDynaset)

You can then scroll through the records using rs.MoveNext, rs.MovePrevious etc
You access the field values like:
yourVar = rs!yourField

For more info look up the (DAO) Recordset object in Help.


--
HTH
Dan Artuso, Access MVP


vtj said:
I need to open an existing table for both read and write capabilities. What commands will do that and do I have to define the
Click to expand...
database table that is being opened, that is the schema? I can not seem to get a table open so that I can read the fields(rows) by
column or field. When I try to dim (define) a variable as being equal to one of the values, I either get some off the wall entry or
'error 2023' which I have not been able to get an explaination of what that error means. I am doing this from a module within the
database in which the table exists. Thanks for any help.
 
Thanks for the help. The table will open and read but will not allow a write. The statement I am using is in essence field1 = variable. The error is 'Update or CancelUpdate without AddNew or Edit' but it will not accept either of those commands. The variables contain the proper information.
 
Hi,
Why don't you post the code you're using. It's much easier to offer
suggestions
that way.

Dan Artuso MVP

vtj said:
Thanks for the help. The table will open and read but will not allow a
Click to expand...
write. The statement I am using is in essence field1 = variable. The error
is 'Update or CancelUpdate without AddNew or Edit' but it will not accept
either of those commands. The variables contain the proper information.
 
Thanks for the help. The table will open and read but will not allow a write. The statement I am using is in essence field1 = variable. The error is 'Update or CancelUpdate without AddNew or Edit' but it will not accept either of those commands. The variables contain the proper information.
Click to expand...


you need somehting like

rst.edit
make your changes
rst.update
rst.move next

hth
 
write. The statement I am using is in essence field1 = variable. The error
is 'Update or CancelUpdate without AddNew or Edit' but it will not accept
either of those commands. The variables contain the proper information.
you need somehting like

rst.edit
make your changes
rst.update
rst.move next
Click to expand...


Careful! The Update method does not necessarily (ever?) leave the updated
record as the current record. You have to reposition to it explicitly,
before the movenext:

....
rst.update
RST.BOOKMARK = RST.LASTUPDATED
rst.movenext

Check whether the property is lastupdated or lastmodified. I think the
former, but I don't have Access here to check (& I always get them mixed
up).

HTH,
TC
 
TC said:
write. The statement I am using is in essence field1 = variable.
The error is 'Update or CancelUpdate without AddNew or Edit' but it
will not accept either of those commands. The variables contain the
proper information.


Careful! The Update method does not necessarily (ever?) leave the
updated record as the current record. You have to reposition to it
explicitly, before the movenext:

...
rst.update
RST.BOOKMARK = RST.LASTUPDATED
rst.movenext

Check whether the property is lastupdated or lastmodified. I think the
former, but I don't have Access here to check (& I always get them
mixed up).
Click to expand...

I can't find a reference handy, TC, but I think you're mistaken about
this. IIRC, after the Update method finishes, the record that was
current before you called Edit or AddNew remains the current record. In
the case of AddNew, you have to use the Bookmark and LastModified
properties to move to the newly added record; however, if you called
Edit to modify the current record, that record should still be current
after you call Update.

I could be wrong, of course, but I don't think so.
 
Dirk Goldgar said:
I can't find a reference handy, TC, but I think you're mistaken about
this. IIRC, after the Update method finishes, the record that was
current before you called Edit or AddNew remains the current record. In
the case of AddNew, you have to use the Bookmark and LastModified
properties to move to the newly added record; however, if you called
Edit to modify the current record, that record should still be current
after you call Update.

I could be wrong, of course, but I don't think so.
Click to expand...


Hi Dirk!

I just returned from an unexpeced new year break.

You are doubtless right about this. I knew that AddNew did not leave
it positioned; I must have assumed that Edit did the same. >Naughty<
TC!

Cheers,
TC
 
Back
Top