?
Solved

MS Access Front End with a MySQL BackEnd

Posted on 2006-11-22
7
Medium Priority
?
969 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
[X]
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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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 Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

777 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