Solved

MS Access Front End with a MySQL BackEnd

Posted on 2006-11-22
7
966 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 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

734 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