By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

Access and MYSQL continous form problem

P: n/a
I am using an Access2K front end to a MYSQL database. If I enter a new
record in a continuous form the record appears as #deleted as soon as I move
to a different record in the form until I requery the form. After the
requery the records are in different order to the order that they may be
entered in.
The record does not seem to be assigned an ID (autonumber ID field) until
after it is requeried.
My problem is that after requerying the form I want to move the cursor back
to the new record and since it has not got an ID I cannot do this. I have
tried using docmd.GoToRecord,,acLast but this moves the cursor to the last
record on the form and after the requery this is generally not the one I
want.
Has anyone experienced the same problem and found a work around.
Alex
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
This is a common problem when you are using a server's equivalent to
AutoNumber for your primary key... it is, in fact, not assigned until the
record is written by the server, and is not returned. I believe there is a
way to retrieve it from MS SQL Server, but I am not aware of corresponding
methods for other servers.

In several Access client - server database configurations with various
servers, we avoided the problem by creating a Stored Procedure to return
(and increment) the next id for the tables on the server... so, we knew the
ID of the record we were creating. If you have the recent version of MySQL
which supports Stored Procedures, that approach would work for you, too.

Larry Linson
Microsoft Access MVP
"Paradigm" <al********@hotmail.com> wrote in message
news:9n****************@newsfe4-gui.ntli.net...
I am using an Access2K front end to a MYSQL database. If I enter a new
record in a continuous form the record appears as #deleted as soon as I move to a different record in the form until I requery the form. After the
requery the records are in different order to the order that they may be
entered in.
The record does not seem to be assigned an ID (autonumber ID field) until
after it is requeried.
My problem is that after requerying the form I want to move the cursor back to the new record and since it has not got an ID I cannot do this. I have
tried using docmd.GoToRecord,,acLast but this moves the cursor to the last
record on the form and after the requery this is generally not the one I
want.
Has anyone experienced the same problem and found a work around.
Alex

Nov 13 '05 #2

P: n/a
First, make sure that a timestamp field is included, and exposed to
ms-access. (this good advice applies to all server based systems..and you
thus should expose the timestamp field to the client software).

The above should fixed the deleted problem.

As for the 2nd problem of a re-query..you don't mention where/how the
re-query is being done, but the simple solution is to force a disk
write..and then grab the id....

you can use:

me.refresh ' force current record to disk
lngCurrentID = me!ID
me.requery ' re-load all records to form
" (all book marks set previous to this line of code
are now invalid )

' now move pointer back to the recode we were on before the requery

me.recordsetclone.findFirst "id = " & lngCurrentID
me.bookmark = me.recordsetclone.bookmark
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #3

P: n/a
I don't believe MySQL has the equivalent of the MS SQL Server TIMESTAMP field.
In any case, it should not be necessary to prevent the #DELETED problem.

With Oracle, and PostgreSQL, autonumber primary keys do work, though I don't
know for sure with MySQL. The ODBC driver must have the ability to recognize
an Autonumber key, and know how to get the key back from the server after
adding a new row via an ODBC cursor.

If the problem is that MySQL doesn't do that right, the easiest answer is
probably to use a surrogate key as the "primary" key (assuming you have one),
and make the autonumber key a secondary key. Otherwise, you can use a custom
key generation technique rather than an autonumbering key, and write the key
you want to the record when you create it.

You can probably find a more official answer to this question in an FAQ for
the MySQL ODBC driver.

On Wed, 29 Dec 2004 03:34:32 GMT, "Albert D. Kallal"
<Pl*******************@msn.com> wrote:
First, make sure that a timestamp field is included, and exposed to
ms-access. (this good advice applies to all server based systems..and you
thus should expose the timestamp field to the client software).

The above should fixed the deleted problem.

As for the 2nd problem of a re-query..you don't mention where/how the
re-query is being done, but the simple solution is to force a disk
write..and then grab the id....

you can use:

me.refresh ' force current record to disk
lngCurrentID = me!ID
me.requery ' re-load all records to form
" (all book marks set previous to this line of code
are now invalid )

' now move pointer back to the recode we were on before the requery

me.recordsetclone.findFirst "id = " & lngCurrentID
me.bookmark = me.recordsetclone.bookmark


Nov 13 '05 #4

P: n/a
The Timestamp field was the answer. No idea why this works but it appears
that as soon as a record is created the timestamp filed forces the record to
get its ID and also the form does not need to be requeried to display the
new data. This means that I can leave the data in the order that it is
entered until the user decides to requery and resort it.
Thanks a lot.

Alex

"Albert D. Kallal" <Pl*******************@msn.com> wrote in message
news:cBpAd.584020$Pl.256535@pd7tw1no...
First, make sure that a timestamp field is included, and exposed to
ms-access. (this good advice applies to all server based systems..and you
thus should expose the timestamp field to the client software).

The above should fixed the deleted problem.

As for the 2nd problem of a re-query..you don't mention where/how the
re-query is being done, but the simple solution is to force a disk
write..and then grab the id....

you can use:

me.refresh ' force current record to disk
lngCurrentID = me!ID
me.requery ' re-load all records to form
" (all book marks set previous to this line of code are now invalid )

' now move pointer back to the recode we were on before the requery

me.recordsetclone.findFirst "id = " & lngCurrentID
me.bookmark = me.recordsetclone.bookmark
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

Nov 13 '05 #5

P: n/a
It would be nice if this solution _was_ available in all server based systems.
UDB for the IBM ISeries has a DataType named "Timestamp", but it is nothing more
than a DateTime and nothing like the Timestamp type in SQL Server and it
definitely does NOT solve the #DELETED problems. In fact it often contributes
to the problem because it has a much finer resolution than an Access DateTime
(out to microseconds I believe) and I think this difference is not properly
dealt with by Access and/or the ODBC driver.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Paradigm wrote:
The Timestamp field was the answer. No idea why this works but it
appears that as soon as a record is created the timestamp filed
forces the record to get its ID and also the form does not need to be
requeried to display the new data. This means that I can leave the
data in the order that it is entered until the user decides to
requery and resort it.
Thanks a lot.

Alex

"Albert D. Kallal" <Pl*******************@msn.com> wrote in message
news:cBpAd.584020$Pl.256535@pd7tw1no...
First, make sure that a timestamp field is included, and exposed to
ms-access. (this good advice applies to all server based
systems..and you thus should expose the timestamp field to the
client software).

The above should fixed the deleted problem.

As for the 2nd problem of a re-query..you don't mention where/how the
re-query is being done, but the simple solution is to force a disk
write..and then grab the id....

you can use:

me.refresh ' force current record to disk
lngCurrentID = me!ID
me.requery ' re-load all records to form
" (all book marks set previous to this line
of code are now invalid )

' now move pointer back to the recode we were on before the requery

me.recordsetclone.findFirst "id = " & lngCurrentID
me.bookmark = me.recordsetclone.bookmark
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn


Nov 13 '05 #6

P: n/a
On Wed, 29 Dec 2004 07:17:43 -0600, "Rick Brandt" <ri*********@hotmail.com>
wrote:
It would be nice if this solution _was_ available in all server based systems.
UDB for the IBM ISeries has a DataType named "Timestamp", but it is nothing more
than a DateTime and nothing like the Timestamp type in SQL Server and it
definitely does NOT solve the #DELETED problems. In fact it often contributes
to the problem because it has a much finer resolution than an Access DateTime
(out to microseconds I believe) and I think this difference is not properly
dealt with by Access and/or the ODBC driver.


Well, I'm surprised a TIMESTAMP solves this problem anyway. That usually
solves the optimistic locking problem in which the record seems to have been
changed by another user when it has not.

Anyway, according to the MS SQL Server documentation, the MS SQL Server
TIMESTAMP field is not the same as the ANSI SQL definition for timetamp, but
it is the same as the new ANSI definition for a ROWVERSION column, and SQL
Server 2000 provides ROWVERSION as a synonym for TIMESTAMP. It further
recommends the use of the ROWVERSION name from now on rather than TIMESTAMP.
So - if UDB has a ROWVERSION type, that's worth a try.
Nov 13 '05 #7

P: n/a
It is actually MYSQL that I am using. I have now put a time stamp field in
all the tables where I use a continous form entry and it works. Previously I
was using all sorts of work arounds having to test for new data, then
requery, then try to get the cursor back to near where it had been.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:nt********************************@4ax.com...
On Wed, 29 Dec 2004 07:17:43 -0600, "Rick Brandt" <ri*********@hotmail.com> wrote:
It would be nice if this solution _was_ available in all server based systems.UDB for the IBM ISeries has a DataType named "Timestamp", but it is nothing morethan a DateTime and nothing like the Timestamp type in SQL Server and it
definitely does NOT solve the #DELETED problems. In fact it often contributesto the problem because it has a much finer resolution than an Access DateTime(out to microseconds I believe) and I think this difference is not properlydealt with by Access and/or the ODBC driver.
Well, I'm surprised a TIMESTAMP solves this problem anyway. That usually
solves the optimistic locking problem in which the record seems to have

been changed by another user when it has not.

Anyway, according to the MS SQL Server documentation, the MS SQL Server
TIMESTAMP field is not the same as the ANSI SQL definition for timetamp, but it is the same as the new ANSI definition for a ROWVERSION column, and SQL
Server 2000 provides ROWVERSION as a synonym for TIMESTAMP. It further
recommends the use of the ROWVERSION name from now on rather than TIMESTAMP. So - if UDB has a ROWVERSION type, that's worth a try.

Nov 13 '05 #8

P: n/a
rkc
Steve Jorgensen wrote:
I don't believe MySQL has the equivalent of the MS SQL Server TIMESTAMP field.
In any case, it should not be necessary to prevent the #DELETED problem.


http://dev.mysql.com/doc/mysql/en/DELETED_problem.html
Nov 13 '05 #9

P: n/a
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:nt********************************@4ax.com...

Well, I'm surprised a TIMESTAMP solves this problem anyway. That usually
solves the optimistic locking problem in which the record seems to have
been
changed by another user when it has not.


ms-access uses the timestamp field to "help" it figure out when the record
was changed. If no timestamp is present, then all fields have to be checked.
Including a timestamp field (be it sql server, Oracle, Sysbase, MySql etc.)
helps ms-access function correctly. You can on occasions leave out the
timestamp field..but ms-access runs far better when you include it, and
expose it to ms-access.

ms-access great form/sub-form feature works well with server based systems
(and, yes..even MySql) *if* you include a timestamp. if you don't, then you
will often see the "deleted" problem as ms-access has difficult figuring out
what was changed..and when....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.