MS Access Front End with a MySQL BackEnd

I have a db in MySQL and I created a quick front end. Here is a description of each MySQL table:

mysql> describe press_schedule;
+--------------+----------------------+------+-----+---------+----------------+
| Field        | Type                 | Null | Key | Default | Extra          |
+--------------+----------------------+------+-----+---------+----------------+
| press_sch_id | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| press_date   | date                 | YES  |     | NULL    |                |
| rgj_lst_pte  | datetime             | YES  |     | NULL    |                |
| rgj_prs_str  | datetime             | YES  |     | NULL    |                |
| rgj_prs_stp  | datetime             | YES  |     | NULL    |                |
| maint_str    | time                 | YES  |     | NULL    |                |
| maint_stp    | time                 | YES  |     | NULL    |                |
+--------------+----------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> describe press_schedule_sections;
+--------------+----------------------+------+-----+---------+-------+
| Field        | Type                 | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| press_sch_id | smallint(5) unsigned | NO   | PRI | NULL    |       |
| section_id   | smallint(5) unsigned | NO   | PRI | NULL    |       |
| lst_pte      | time                 | YES  |     | NULL    |       |
| prs_str      | time                 | YES  |     | NULL    |       |
| prs_stp      | time                 | YES  |     | NULL    |       |
| prd_dte      | date                 | YES  |     | NULL    |       |
+--------------+----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> describe sections;
+--------------+----------------------+------+-----+---------+----------------+
| Field        | Type                 | Null | Key | Default | Extra          |
+--------------+----------------------+------+-----+---------+----------------+
| section_id   | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| section_name | varchar(100)         | NO   |     | NULL    |                |
+--------------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

As you can see from the table design, many records can be inserted in the press_schedule_sections table which relate to a single record in the press_schedule table. I obviously created a subform using press_schedule_sections and inserted it into press_schedule linking press_sch_id. All good until I tried inserting a record in the parent form. Here are the problems:

1. As soon as I moved my focus to the child form, all of the fields in the  main form changed to #Deleted. I then inserted data into the child form, and when I tried moving on to the next row, I got the following error:
ODBC --insert on a linked table 'press_schedule_sections' failed.
2. The strangest thing is that after closing the form, I ran a select statement on the MySQL db and the inserted record was there! After opening the form again in Access, the data for the recent inserted record appeares and I'm able to insert data into the child form.
3. Although the data appears in the inserted records, Access does not convert the data correctly. Instead of showing a time field, it shows a date field.
4. When I try to update a record, Access won't allow this. I get the following error when trying to do so:
"This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made." I also get two options, to Drop the Changes or to Copy to Clipboard. The button Save Record is grayed out.

I am using MySQL ODBC Driver 3.51 and I also installed the patch. Any ideas as to what may be causing these discrepancies?
horaliaAsked:
Who is Participating?
 
ChrisedeboConnect With a Mentor Commented:
After you've inserted a record in the main form, click on refresh in the record menu. What happens? Does the record come back displayed correctly? or is it still deleted?

I suspect we may be fighting against a bug in access.....I've had experience of access talking to MySQL and it's difficult at best.....

The Date Time field thing is also an issue, Access doesn't have a time data type so it will always be displayed as a date in the format 01/01/1900 hh:nn, where hh and nn are the hours and minutes of the time field in MySQL respectively. Also, check out the options you have set on the ODBC connection. There are three tabs of switches, I can't remember them all, but they me be of some assistance......

Chris
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Are you using bound forms?

Do all tables have a primary key set on them?

If you're using bound forms, then are you also changing the data through other means (like through VBA code or SQl statements)?
0
 
horaliaAuthor Commented:
Yep, both forms are bound directly to the table.
Yes, all three tables have a primary key.
No, I haven't done any coding yet, all data is inserted automatically into MySQL.

That's the weird part, the data is saved in MySQL in the correct form, but Access won't display it correctly.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ChrisedeboCommented:
I've also had lots of issues with the "Drop Changes, Copy to Clipboard" issue. Most of the time they were fixed by re-linking the table.
0
 
horaliaAuthor Commented:
I ended up deciding not to use MSAccess with MySQL. I'll divide the points between both participants in case I change my mind or if I need to use this combination in the future. Thanks for your suggestions!
0
 
ChrisedeboCommented:
Glad my experience is of some use to someone else :o)

When I mentioned using access and mysql earlier I had a cached copy of the data stored in access and syncronised the data to mysql whenever the record was saved. But this was because we were updating a clients live database over the internet and it needed to be current. Other than that kind access, I'd steer clear of it unless you are using unbound forms and connecting through ado, but then you'd still have to consider the field types very carefully.

Cheers

Chris
0
 
horaliaAuthor Commented:
Thanks for the advice Chris!
0
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.

All Courses

From novice to tech pro — start learning today.