Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

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?

1 Solution
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-)
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now