Solved

MS Access Front End with a MySQL BackEnd

Posted on 2006-11-22
7
961 Views
Last Modified: 2008-10-08
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?
0
Comment
Question by:horalia
  • 3
  • 3
7 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 18000013
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
 

Author Comment

by:horalia
ID: 18000032
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
 
LVL 7

Accepted Solution

by:
Chrisedebo earned 250 total points
ID: 18007230
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:Chrisedebo
ID: 18007236
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
 

Author Comment

by:horalia
ID: 18008935
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
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 18009035
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
 

Author Comment

by:horalia
ID: 18009044
Thanks for the advice Chris!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now