MySQL Forums
Forum List  »  Microsoft Access

DAO/Access2k/MySQL5 rs.addnew #DELETED#
Posted by: Jake Yazici
Date: July 11, 2005 09:06PM

Hello Everyone!

I've been pulling my hair out in tufts trying to trace an elegant solution this problem. This is a real show stopper.

I have a legacy application that makes use of DAO in an Access 2000/Windows 2000 environment. I am attempting to move the backend from MSSQL Server to MySQL 5.0.7. However, on a particular copy operation, I cannot retrieve the last inserted record id.

I have a feeling this is part of the infamous #DELETED# record problem on inserts.

I have spent a week reading through just about every post on this matter in all of the various forums, and googled for solutions as well. I've made some headway using some of the suggested methods.

These include:

1) Converting any "BIGINT" fields to INTs.
2) Making sure AUTO_INC is primary key field & type INT
3) Add TIMESTAMP field
4) Patch the MSJet engine using "windows2000-kb829558-x86-enu.exe"

I have also tried linking tables to the MDB file via MyODBC 3.51.11-2 & 3.51.12 (which I've thouroughly installed and uninstalled).

Now, I can manually create a new record into the linked table and have the record properly inserted and visible in the linked table. The Auto_Inc field gets updated just after insert, as well as the TIMESTAMP.

However, when attempting to insert a row programatically, I get goose eggs.

Using the VBA, the code looks like this:

strSQLTarget = "Select * from " & linkedTableName & " Where 1 = 2"
Set qdfTarget = Application.CurrentDb.CreateQueryDef("", strSQLTarget)
Set rstTarget = qdfTarget.OpenRecordset(dbOpenDynaset, dbSeeChanges)
rstTarget.AddNew

..... update some fields here .....

rstTarget.Update
rstTarget.MoveFirst
retRecId = rstTarget(PKName)

This code worked flawlessly against MSSQL7 & MSSQL2K using MDAC2.6

When I trace the code via breakpoints, I inspect the the primary key value for the recordset before and after the update command. Before, it is "NULL", as it should be. After the update, the value is set to <No current record>. After the MoveFirst call, the value for the PK is <Record is deleted.>. The record is created, but the pointer to the record is lost.

It seems that the Jet engine is not playing nicely with the Auto_Inc fields using the MyODBC drivers.

Will someone please explain what is going on and how I can fix this? Rewriting all the code for ADO is not an option for a variety of reasons (too involved to list here). I'm simply trying to extend the usefulness of this product until the next major revision (which is based on C#/.NET).

What is the cause of this problem? Is this a problem with the ODBC driver or Access or ???

Inserts are a fundamental operation. Is this not well supported yet?

-Jake

"I conclude that there are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies and the other way is to make it so complicated that there are no obvious deficiencies." - C. A. R. Hoare

Options: ReplyQuote


Subject
Views
Written By
Posted
DAO/Access2k/MySQL5 rs.addnew #DELETED#
5215
July 11, 2005 09:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.