DAO Add/Edit/Update Methods

[Ronald Dodge]

Access 2002, SP3
MDAC 2.8

Is there something about the Add, Edit and/or Update methods using DAO
recordsets that I should know about. From my understanding and what I have
seen, it looks like at times, the updating of records takes place, but other
times, it doesn't for the same people with the same forms.

In the past, I have known the EditMode property to not be showing in the
proper mode in the following situation

Put recordset in either Add or Edit mode
User update the unbound controls
Click on command button to update the DB (at this point, it would show the
EditMode property is not in the proper mode, but rather EditNone, thus
couldn't even transfer the data from the controls to the fields in the
recordset for the record that is suppose to be in EditInProcess mode).

Do I need to switch over to ADO coding to avoid these kinds of issues? I
can not use bound forms, so that's not an option. I'm already having to
similate disconnected databases like how ADO.NET is done, so the dynamic
cursor keyset isn't needed, thus bypasses that issue with regards to ADO not
allowing for a dynamic cursor keyset with a JET engine DB.

In the past, it was needed to use dynamic cursor keyset, thus given the DAO
and ADO issues above listed, that's what drove me away from Access along
with me not really being able to effectively use bound forms as at times,
the user may not know which mode to be in until after filling in the form,
thus why DBs like JDE allows for Action codes or command buttons to
determine which prior to pressing enter. For JDE it's the following:

A or 1 for Add
C or 2 for Change (Aka Edit for Access)
D for Delete (doesn't allow for the numeric value of 3 due to the danger of
delete operations)
I or 4 for Inquire

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Klatuu]

Ronald,

If your are using mde and mdb files, then DAO is actually the better choice.
I don't know why you can't use bound forms. It certainly has to be some
managerial edict because I have never seen a situation where it is
technically impossible. That being said, here are some pointers that may
help.

First, make sure you are opening your recordset that will updates as Dynaset:

Set RstOne = CurrentDb.OpenRecordset("RoundTable", dbOpenDynaset)

Use VBA to update or add new records. You will have to know whether you are
creating a new record or updating an existing record. To Add

With RstOne
If blnNewRecord Then
.AddNew
Else
.Edit
End If
![SomeField] = Me.SomeControl
![AnotherField] = Me.DifferentControl
.Update
End with

 

[Ronald Dodge]

This below is exactly why I can't use bound forms. You MUST know prior to
even starting on the record which method you are going to use. Once you
start working on a record, you can't do anything but reverse the action by
cancelling it out, thus why I included an update current record command
button and an add new record command button as standards on my data forms.

You will have to know whether you are
creating a new record or updating an existing record.

Also, if this is to be portable to ADO.NET for future purposes, dynamic
connection won't be available either. This is another reason why I didn't
try to do a work around but rather using Add New Record and Edit Current
Record command buttons.

Don't get me wrong, there are some differences between DAO and ADO, and then
to go from Access ADO (MDAC 2.8 in this case) to ADO.NET, you also have a
whole set of differences yet, but the more we can keep the same, the better.

It's inevitable that migration will take place, so why not address some of
those issues early on, so as to have less work to do when that migration
process does take place, even if you aren't using those items currently?


Just so you know, when I have used DAO with the dynamic cursor keyset, I
still ran into the issue of the EditMode property not showing the edit mode
properly and the recordset prematurely going out of the edit/add mode and
into EditNone mode without any such command telling it to either Update or
CancelUpdate, which in this case, it would cancel the update. By the looks
of things, DAO will not be worked on any time soon by MS, if ever. Makes
you wonder why DAO is being abandoned from being worked on by MS.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

Ronald,

If your are using mde and mdb files, then DAO is actually the better
choice.
I don't know why you can't use bound forms. It certainly has to be some
managerial edict because I have never seen a situation where it is
technically impossible. That being said, here are some pointers that may
help.

First, make sure you are opening your recordset that will updates as
Dynaset:

Set RstOne = CurrentDb.OpenRecordset("RoundTable", dbOpenDynaset)

Use VBA to update or add new records. You will have to know whether you
are
creating a new record or updating an existing record. To Add

With RstOne
If blnNewRecord Then
.AddNew
Else
.Edit
End If
![SomeField] = Me.SomeControl
![AnotherField] = Me.DifferentControl
.Update
End with

--
Dave Hargis, Microsoft Access MVP

Access 2002, SP3
MDAC 2.8

Is there something about the Add, Edit and/or Update methods using DAO
recordsets that I should know about. From my understanding and what I
have
seen, it looks like at times, the updating of records takes place, but
other
times, it doesn't for the same people with the same forms.

In the past, I have known the EditMode property to not be showing in the
proper mode in the following situation

Put recordset in either Add or Edit mode
User update the unbound controls
Click on command button to update the DB (at this point, it would show
the
EditMode property is not in the proper mode, but rather EditNone, thus
couldn't even transfer the data from the controls to the fields in the
recordset for the record that is suppose to be in EditInProcess mode).

Do I need to switch over to ADO coding to avoid these kinds of issues? I
can not use bound forms, so that's not an option. I'm already having to
similate disconnected databases like how ADO.NET is done, so the dynamic
cursor keyset isn't needed, thus bypasses that issue with regards to ADO
not
allowing for a dynamic cursor keyset with a JET engine DB.

In the past, it was needed to use dynamic cursor keyset, thus given the
DAO
and ADO issues above listed, that's what drove me away from Access along
with me not really being able to effectively use bound forms as at times,
the user may not know which mode to be in until after filling in the
form,
thus why DBs like JDE allows for Action codes or command buttons to
determine which prior to pressing enter. For JDE it's the following:

A or 1 for Add
C or 2 for Change (Aka Edit for Access)
D for Delete (doesn't allow for the numeric value of 3 due to the danger
of
delete operations)
I or 4 for Inquire

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Klatuu]

Ronald,

I believe you have some serious misconceptions. First, there is no reason
you cannot use ADO and bound forms. Based on what you are saying about
future upgrades, then perhaps migrating your Jet database to SQL Sever and
using adp instead of mdb would be the way to go.
--
Dave Hargis, Microsoft Access MVP

This below is exactly why I can't use bound forms. You MUST know prior to
even starting on the record which method you are going to use. Once you
start working on a record, you can't do anything but reverse the action by
cancelling it out, thus why I included an update current record command
button and an add new record command button as standards on my data forms.

You will have to know whether you are
creating a new record or updating an existing record.

Also, if this is to be portable to ADO.NET for future purposes, dynamic
connection won't be available either. This is another reason why I didn't
try to do a work around but rather using Add New Record and Edit Current
Record command buttons.

Don't get me wrong, there are some differences between DAO and ADO, and then
to go from Access ADO (MDAC 2.8 in this case) to ADO.NET, you also have a
whole set of differences yet, but the more we can keep the same, the better.

It's inevitable that migration will take place, so why not address some of
those issues early on, so as to have less work to do when that migration
process does take place, even if you aren't using those items currently?


Just so you know, when I have used DAO with the dynamic cursor keyset, I
still ran into the issue of the EditMode property not showing the edit mode
properly and the recordset prematurely going out of the edit/add mode and
into EditNone mode without any such command telling it to either Update or
CancelUpdate, which in this case, it would cancel the update. By the looks
of things, DAO will not be worked on any time soon by MS, if ever. Makes
you wonder why DAO is being abandoned from being worked on by MS.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

Ronald,

If your are using mde and mdb files, then DAO is actually the better
choice.
I don't know why you can't use bound forms. It certainly has to be some
managerial edict because I have never seen a situation where it is
technically impossible. That being said, here are some pointers that may
help.

First, make sure you are opening your recordset that will updates as
Dynaset:

Set RstOne = CurrentDb.OpenRecordset("RoundTable", dbOpenDynaset)

Use VBA to update or add new records. You will have to know whether you
are
creating a new record or updating an existing record. To Add

With RstOne
If blnNewRecord Then
.AddNew
Else
.Edit
End If
![SomeField] = Me.SomeControl
![AnotherField] = Me.DifferentControl
.Update
End with

--
Dave Hargis, Microsoft Access MVP

Access 2002, SP3
MDAC 2.8

Is there something about the Add, Edit and/or Update methods using DAO
recordsets that I should know about. From my understanding and what I
have
seen, it looks like at times, the updating of records takes place, but
other
times, it doesn't for the same people with the same forms.

In the past, I have known the EditMode property to not be showing in the
proper mode in the following situation

Put recordset in either Add or Edit mode
User update the unbound controls
Click on command button to update the DB (at this point, it would show
the
EditMode property is not in the proper mode, but rather EditNone, thus
couldn't even transfer the data from the controls to the fields in the
recordset for the record that is suppose to be in EditInProcess mode).

Do I need to switch over to ADO coding to avoid these kinds of issues? I
can not use bound forms, so that's not an option. I'm already having to
similate disconnected databases like how ADO.NET is done, so the dynamic
cursor keyset isn't needed, thus bypasses that issue with regards to ADO
not
allowing for a dynamic cursor keyset with a JET engine DB.

In the past, it was needed to use dynamic cursor keyset, thus given the
DAO
and ADO issues above listed, that's what drove me away from Access along
with me not really being able to effectively use bound forms as at times,
the user may not know which mode to be in until after filling in the
form,
thus why DBs like JDE allows for Action codes or command buttons to
determine which prior to pressing enter. For JDE it's the following:

A or 1 for Add
C or 2 for Change (Aka Edit for Access)
D for Delete (doesn't allow for the numeric value of 3 due to the danger
of
delete operations)
I or 4 for Inquire

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Ronald Dodge]

Only if I had the funds and/or the call to be able to go to SQL Server, but
I don't get to make such calls.

BTW, I didn't say you can't use ADO and bound forms, but bound forms do
require the application to know rather to go into add, edit, or review mode
first before doing anything with the bound controls on the form.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

Ronald,

I believe you have some serious misconceptions. First, there is no reason
you cannot use ADO and bound forms. Based on what you are saying about
future upgrades, then perhaps migrating your Jet database to SQL Sever and
using adp instead of mdb would be the way to go.
--
Dave Hargis, Microsoft Access MVP

This below is exactly why I can't use bound forms. You MUST know prior
to
even starting on the record which method you are going to use. Once you
start working on a record, you can't do anything but reverse the action
by
cancelling it out, thus why I included an update current record command
button and an add new record command button as standards on my data
forms.

You will have to know whether you are
creating a new record or updating an existing record.

Also, if this is to be portable to ADO.NET for future purposes, dynamic
connection won't be available either. This is another reason why I
didn't
try to do a work around but rather using Add New Record and Edit Current
Record command buttons.

Don't get me wrong, there are some differences between DAO and ADO, and
then
to go from Access ADO (MDAC 2.8 in this case) to ADO.NET, you also have a
whole set of differences yet, but the more we can keep the same, the
better.

It's inevitable that migration will take place, so why not address some
of
those issues early on, so as to have less work to do when that migration
process does take place, even if you aren't using those items currently?


Just so you know, when I have used DAO with the dynamic cursor keyset, I
still ran into the issue of the EditMode property not showing the edit
mode
properly and the recordset prematurely going out of the edit/add mode and
into EditNone mode without any such command telling it to either Update
or
CancelUpdate, which in this case, it would cancel the update. By the
looks
of things, DAO will not be worked on any time soon by MS, if ever. Makes
you wonder why DAO is being abandoned from being worked on by MS.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

Ronald,

If your are using mde and mdb files, then DAO is actually the better
choice.
I don't know why you can't use bound forms. It certainly has to be
some
managerial edict because I have never seen a situation where it is
technically impossible. That being said, here are some pointers that
may
help.

First, make sure you are opening your recordset that will updates as
Dynaset:

Set RstOne = CurrentDb.OpenRecordset("RoundTable", dbOpenDynaset)

Use VBA to update or add new records. You will have to know whether
you
are
creating a new record or updating an existing record. To Add

With RstOne
If blnNewRecord Then
.AddNew
Else
.Edit
End If
![SomeField] = Me.SomeControl
![AnotherField] = Me.DifferentControl
.Update
End with

--
Dave Hargis, Microsoft Access MVP


:

Access 2002, SP3
MDAC 2.8

Is there something about the Add, Edit and/or Update methods using DAO
recordsets that I should know about. From my understanding and what I
have
seen, it looks like at times, the updating of records takes place, but
other
times, it doesn't for the same people with the same forms.

In the past, I have known the EditMode property to not be showing in
the
proper mode in the following situation

Put recordset in either Add or Edit mode
User update the unbound controls
Click on command button to update the DB (at this point, it would show
the
EditMode property is not in the proper mode, but rather EditNone, thus
couldn't even transfer the data from the controls to the fields in the
recordset for the record that is suppose to be in EditInProcess mode).

Do I need to switch over to ADO coding to avoid these kinds of issues?
I
can not use bound forms, so that's not an option. I'm already having
to
similate disconnected databases like how ADO.NET is done, so the
dynamic
cursor keyset isn't needed, thus bypasses that issue with regards to
ADO
not
allowing for a dynamic cursor keyset with a JET engine DB.

In the past, it was needed to use dynamic cursor keyset, thus given
the
DAO
and ADO issues above listed, that's what drove me away from Access
along
with me not really being able to effectively use bound forms as at
times,
the user may not know which mode to be in until after filling in the
form,
thus why DBs like JDE allows for Action codes or command buttons to
determine which prior to pressing enter. For JDE it's the following:

A or 1 for Add
C or 2 for Change (Aka Edit for Access)
D for Delete (doesn't allow for the numeric value of 3 due to the
danger
of
delete operations)
I or 4 for Inquire

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Klatuu]

I really don't understand what you are saying about bound controls. When you
open a bound form, the data of the first record in the recordset is
displayed. If you change any values in the bound controls, the bound field
is updated when you navigate away from the record. If you want to create a
new record, you just naviage to a new record. What leads you to believe
otherwise?
--
Dave Hargis, Microsoft Access MVP

Only if I had the funds and/or the call to be able to go to SQL Server, but
I don't get to make such calls.

BTW, I didn't say you can't use ADO and bound forms, but bound forms do
require the application to know rather to go into add, edit, or review mode
first before doing anything with the bound controls on the form.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

Ronald,

I believe you have some serious misconceptions. First, there is no reason
you cannot use ADO and bound forms. Based on what you are saying about
future upgrades, then perhaps migrating your Jet database to SQL Sever and
using adp instead of mdb would be the way to go.
--
Dave Hargis, Microsoft Access MVP

This below is exactly why I can't use bound forms. You MUST know prior
to
even starting on the record which method you are going to use. Once you
start working on a record, you can't do anything but reverse the action
by
cancelling it out, thus why I included an update current record command
button and an add new record command button as standards on my data
forms.

You will have to know whether you are
creating a new record or updating an existing record.

Also, if this is to be portable to ADO.NET for future purposes, dynamic
connection won't be available either. This is another reason why I
didn't
try to do a work around but rather using Add New Record and Edit Current
Record command buttons.

Don't get me wrong, there are some differences between DAO and ADO, and
then
to go from Access ADO (MDAC 2.8 in this case) to ADO.NET, you also have a
whole set of differences yet, but the more we can keep the same, the
better.

It's inevitable that migration will take place, so why not address some
of
those issues early on, so as to have less work to do when that migration
process does take place, even if you aren't using those items currently?


Just so you know, when I have used DAO with the dynamic cursor keyset, I
still ran into the issue of the EditMode property not showing the edit
mode
properly and the recordset prematurely going out of the edit/add mode and
into EditNone mode without any such command telling it to either Update
or
CancelUpdate, which in this case, it would cancel the update. By the
looks
of things, DAO will not be worked on any time soon by MS, if ever. Makes
you wonder why DAO is being abandoned from being worked on by MS.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

Ronald,

If your are using mde and mdb files, then DAO is actually the better
choice.
I don't know why you can't use bound forms. It certainly has to be
some
managerial edict because I have never seen a situation where it is
technically impossible. That being said, here are some pointers that
may
help.

First, make sure you are opening your recordset that will updates as
Dynaset:

Set RstOne = CurrentDb.OpenRecordset("RoundTable", dbOpenDynaset)

Use VBA to update or add new records. You will have to know whether
you
are
creating a new record or updating an existing record. To Add

With RstOne
If blnNewRecord Then
.AddNew
Else
.Edit
End If
![SomeField] = Me.SomeControl
![AnotherField] = Me.DifferentControl
.Update
End with

--
Dave Hargis, Microsoft Access MVP


:

Access 2002, SP3
MDAC 2.8

Is there something about the Add, Edit and/or Update methods using DAO
recordsets that I should know about. From my understanding and what I
have
seen, it looks like at times, the updating of records takes place, but
other
times, it doesn't for the same people with the same forms.

In the past, I have known the EditMode property to not be showing in
the
proper mode in the following situation

Put recordset in either Add or Edit mode
User update the unbound controls
Click on command button to update the DB (at this point, it would show
the
EditMode property is not in the proper mode, but rather EditNone, thus
couldn't even transfer the data from the controls to the fields in the
recordset for the record that is suppose to be in EditInProcess mode).

Do I need to switch over to ADO coding to avoid these kinds of issues?
I
can not use bound forms, so that's not an option. I'm already having
to
similate disconnected databases like how ADO.NET is done, so the
dynamic
cursor keyset isn't needed, thus bypasses that issue with regards to
ADO
not
allowing for a dynamic cursor keyset with a JET engine DB.

In the past, it was needed to use dynamic cursor keyset, thus given
the
DAO
and ADO issues above listed, that's what drove me away from Access
along
with me not really being able to effectively use bound forms as at
times,
the user may not know which mode to be in until after filling in the
form,
thus why DBs like JDE allows for Action codes or command buttons to
determine which prior to pressing enter. For JDE it's the following:

A or 1 for Add
C or 2 for Change (Aka Edit for Access)
D for Delete (doesn't allow for the numeric value of 3 due to the
danger
of
delete operations)
I or 4 for Inquire

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Ronald Dodge]

I understand what you saying there, but I'm also having to think like how
others think, which JDE is our main DB system. Therefore, to keep
individuals from getting the 2 methods confused, I have to keep the methods
in Access to be similar to JDE as reasonably close as possible. Not only
that, but you also nailed it on the head with the fact that it pulls up the
first record right away unless the form is put into a different mode such as
data entry mode, which then puts you into add new record mode.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

I really don't understand what you are saying about bound controls. When
you
open a bound form, the data of the first record in the recordset is
displayed. If you change any values in the bound controls, the bound
field
is updated when you navigate away from the record. If you want to create
a
new record, you just naviage to a new record. What leads you to believe
otherwise?
--
Dave Hargis, Microsoft Access MVP

Only if I had the funds and/or the call to be able to go to SQL Server,
but
I don't get to make such calls.

BTW, I didn't say you can't use ADO and bound forms, but bound forms do
require the application to know rather to go into add, edit, or review
mode
first before doing anything with the bound controls on the form.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

Ronald,

I believe you have some serious misconceptions. First, there is no
reason
you cannot use ADO and bound forms. Based on what you are saying about
future upgrades, then perhaps migrating your Jet database to SQL Sever
and
using adp instead of mdb would be the way to go.
--
Dave Hargis, Microsoft Access MVP


:

This below is exactly why I can't use bound forms. You MUST know
prior
to
even starting on the record which method you are going to use. Once
you
start working on a record, you can't do anything but reverse the
action
by
cancelling it out, thus why I included an update current record
command
button and an add new record command button as standards on my data
forms.

You will have to know whether you are
creating a new record or updating an existing record.

Also, if this is to be portable to ADO.NET for future purposes,
dynamic
connection won't be available either. This is another reason why I
didn't
try to do a work around but rather using Add New Record and Edit
Current
Record command buttons.

Don't get me wrong, there are some differences between DAO and ADO,
and
then
to go from Access ADO (MDAC 2.8 in this case) to ADO.NET, you also
have a
whole set of differences yet, but the more we can keep the same, the
better.

It's inevitable that migration will take place, so why not address
some
of
those issues early on, so as to have less work to do when that
migration
process does take place, even if you aren't using those items
currently?


Just so you know, when I have used DAO with the dynamic cursor keyset,
I
still ran into the issue of the EditMode property not showing the edit
mode
properly and the recordset prematurely going out of the edit/add mode
and
into EditNone mode without any such command telling it to either
Update
or
CancelUpdate, which in this case, it would cancel the update. By the
looks
of things, DAO will not be worked on any time soon by MS, if ever.
Makes
you wonder why DAO is being abandoned from being worked on by MS.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

Ronald,

If your are using mde and mdb files, then DAO is actually the better
choice.
I don't know why you can't use bound forms. It certainly has to be
some
managerial edict because I have never seen a situation where it is
technically impossible. That being said, here are some pointers
that
may
help.

First, make sure you are opening your recordset that will updates as
Dynaset:

Set RstOne = CurrentDb.OpenRecordset("RoundTable", dbOpenDynaset)

Use VBA to update or add new records. You will have to know whether
you
are
creating a new record or updating an existing record. To Add

With RstOne
If blnNewRecord Then
.AddNew
Else
.Edit
End If
![SomeField] = Me.SomeControl
![AnotherField] = Me.DifferentControl
.Update
End with

--
Dave Hargis, Microsoft Access MVP


:

Access 2002, SP3
MDAC 2.8

Is there something about the Add, Edit and/or Update methods using
DAO
recordsets that I should know about. From my understanding and
what I
have
seen, it looks like at times, the updating of records takes place,
but
other
times, it doesn't for the same people with the same forms.

In the past, I have known the EditMode property to not be showing
in
the
proper mode in the following situation

Put recordset in either Add or Edit mode
User update the unbound controls
Click on command button to update the DB (at this point, it would
show
the
EditMode property is not in the proper mode, but rather EditNone,
thus
couldn't even transfer the data from the controls to the fields in
the
recordset for the record that is suppose to be in EditInProcess
mode).

Do I need to switch over to ADO coding to avoid these kinds of
issues?
I
can not use bound forms, so that's not an option. I'm already
having
to
similate disconnected databases like how ADO.NET is done, so the
dynamic
cursor keyset isn't needed, thus bypasses that issue with regards
to
ADO
not
allowing for a dynamic cursor keyset with a JET engine DB.

In the past, it was needed to use dynamic cursor keyset, thus given
the
DAO
and ADO issues above listed, that's what drove me away from Access
along
with me not really being able to effectively use bound forms as at
times,
the user may not know which mode to be in until after filling in
the
form,
thus why DBs like JDE allows for Action codes or command buttons to
determine which prior to pressing enter. For JDE it's the
following:

A or 1 for Add
C or 2 for Change (Aka Edit for Access)
D for Delete (doesn't allow for the numeric value of 3 due to the
danger
of
delete operations)
I or 4 for Inquire

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000