MSAccess + MySQL - Autonumber problem

Posted on 2003-03-05
Medium Priority
Last Modified: 2012-06-27
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?

Question by:mj_cole
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 15

Expert Comment

ID: 8078180
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 ?

Author Comment

ID: 8079508

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 :)
LVL 15

Expert Comment

ID: 8079614
yes, but I don't have a clue for solving your (excitingly new to me :D ) problem 8-)
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


Author Comment

ID: 8079762

Author Comment

ID: 8110553
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.

Expert Comment

ID: 10211954
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.

Author Comment

ID: 10214061

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.


Author Comment

ID: 10215503

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.


Accepted Solution

Computer101 earned 0 total points
ID: 12270250
PAQed, with points refunded (100)

E-E Admin

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question