MSAccess + MySQL - Autonumber problem

humm
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 ?

OK,

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-)
Sorry.

:p

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.

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.

Marts

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.

Marts