?
Solved

MS Access Front End with a MySQL BackEnd

Posted on 2006-11-22
7
Medium Priority
?
975 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 1000 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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

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.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

850 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