MSAccess + MySQL - Autonumber problem

I am somewhat new to database design, so this may be a silly question!

I have my 'mysql' database online, and am able to access it via ms access 2002.

My 'mysql' table has a primary key with autonumber. When I edit the data with access, i bypass the key coloum, and enter a value in the only other coloum. Upon moving to the next record, it replaces the record with:


however when I close the table view, and re-open it, the entered data is there?!

I assume that there is some parameter that I can set to re-initalise the table.

BTW a form does exactly the same thing?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

not silly question, but silly-looking.
Are you trying to convince me that using Access 2K ( a microsoft proprietary product for DBMS) you access a MySql database ?

Hummmm ;-)

Isn't this rather a Access2000 database with a table (strangely) called "mysql" ?!?? ;-)

Anyway, you shouldn't "skip" the PRI key as it is the key/index of your rows. It has to be set for you to modify a row via UPDATE or DELETE.

Other track to follow : isn't your client enabling transactions ? Don't you have to COMMIT changes to the underlying DB ?
mj_coleAuthor Commented:

Yes I am using an MS product to access a mysql database server.

I don't actually intend to 'skip' the pri-key, but want to keep it from the user of the database. The quick tester I did purely in access, automatically puts the autonumbered field into the pri-key box. Also using the mysql control center with the mysql realises that the pri-key is auto numbered.

However, when the access front to the linked mysql table starts a new record, it dosn't get the autonumber from the mysql server.

I guess what i would like to happen is to create a new sql record, then load the details into access for the user to edit, not showing him/her the key... but like i said, i'm new to database programming.

On your last comment, I think it does enable transactions, as the data is kept by mysql, but if the key was 'typed in wrong' ie not one above the previous key, the data 'appears' deleted until the form/table is close and reopened.

Thanks for the quick response :)
yes, but I don't have a clue for solving your (excitingly new to me :D ) problem 8-)
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

mj_coleAuthor Commented:
mj_coleAuthor Commented:
OK I figured it out myself, for anyone else who has this problem.

To use Access as a frontend to mysql, there must be both a primary key coloum AND a timestamp coloum.
Hey MJ, could you be a little more descriptive in your solution? I'm having the same problem right now, and was curious to know what you mean by using a timestamp column to get access to pull the autonumber information. Thanks.
mj_coleAuthor Commented:

Let me try to remember,

I think I needed to use the mySql control center to design my table, and I needed to place both a primary key colom, and create a timestamp colom, both using the control center, then make your various data coloms.

If I get time, I will try to recreate the situation when I get home, and post a more detailed post.

mj_coleAuthor Commented:

OK, it appears that I had reinstalled everything on my notebook since I was playing around with this system, but I downloaded the latest versions of mysql server for windows, mysqlCC for windows and odbc for windows.

I installed all three, and started mysql server by running the admin tool in the bin directory of mysql.

On  my decond try, I realised what works.

Create a table, using the mysqlCC, make the first colom a "timestamp" type (from the pulldown list). Make the second colom an autonumber integer, and specify as primary key. then make whichever other coloms you want after these.

Use msaccess to connect to the mysql database, using the mysql odbc driver.

You should be able to link to your table, and enter data. I always skip the timestamp and autonumbered coloms, and they are filled in automatically by the mysql server.

Good luck, I hope this was helpful.

PAQed, with points refunded (100)

E-E Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.