Linked Tables from access to MySql

[jrsl]

I am working on a project that uses a VB program and connects to a access database to store customer records ect.

Im adapting this to a website so customers can view there repair status. how ever in order to do this i have to link tables from the access database to the MySql databse required from the site.

When i make a new record in the program (inserted to access) it errors saying
"The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."

I have followed the advise on the MySql website:
"After exporting your table to MySQL, use phpMyAdmin to make the field "auto-increment" - you do this using the "change" option on the page that shows the table's structure. You may have to make that field "unique" first. You then have to add a new field to the table. It doesn't matter where it goes (the last column is probably neatest) or what it's called, but it must be of type "timestamp". You can then make the link from Access to the table. When you add new rows to the table, leave the auto-increment field and timestamp field blank - MySQL fills them in for you."

Still no luck. Any ideas?

Cheers,
Justin

 

[Banana]

I've never used phpMyAdmin, but I've used MySQL and MyODBC to link to Access.

I'm not sure if I'm following you here...

What is the MySQL server's version, MyOBDC's version, and what are you trying to do that caused the error? (More details... are you trying to add a new record in a form or by code or ???, what is the SQL you're using?)

 

[jrsl]

Thanks for the fast reply.

Im intergrating this website into a pre-made program (have no access to un-compiled files) so i cant give you the sql the program uses.

Im using the latest version ODBC 5.1 as i downloaded them last week. Its linked to PhpMyAdmin. and it accurs when i add a new customer record from the program.

Could it be possable that its because im running the program and MySql from my local host?

Cheers,
Justin

 

[Banana]

Well, phpMyAdmin is just a MySQL manager. In case of linking between Access and MySQL, phpMyAdmin has no relevance whatever. Kind how solitaire is relevant to writing an email, I guess.

What is the connection string you are using? And exactly where are you running the sql? Inside the Access or that program you mentioned?

and to be crystal clear, are we adding records into MySQL, or adding records into Access?

 

[jrsl]

Understang Mysql manager LOL...

The SQL string is within the program which i have no access to. I have simply exported the customer table to mysql then created a link to mysql database.

I created a timestamp and auto_increment as the mysql site suggested with no differance.

The program has a feature to rebuild the database, which i have done and it gives an error saying that it is trying to add a record that already exists

We are adding records into access from the vb program.

Regards,
Justin

 

[Banana]

While timestamp and auto_increment is indeed important, I think there are other factors at play here.

The only thing i don't get is why you are using program to insert records into Access? If it's configured to use MySQL, you can just insert records into MySQL, and have Access link to MySQL directly using MyODBC without involving the program at all.

What is your ultimate goal with the program and Access?

 

[jrsl]

Okay,

Its for a large television repair agent. They are using the program to raise repair jobs ect. This program saves all detail to access database.

Im making a script on there website so customers can login and view there repair status. The website has a MySql database.

So its not practical for them to insert data into the access database directly. I guess that the sql string on the program JOINS many database relationships, could that be the problem. does it not detect the new table into the relationship? (the access file as no relationship so i assume that they do it via there sql statements).

Regards,
Justin

 

[Banana]

That was helpful. Let's see if I am following you now.

You have a custom program that handle adding new jobs for the repair agents. This program uses Access as its back-end to store all jobs.

You want to be able to show the job status on the website, so you use MySQL to show the data.

Your program was working until you tried to link Access backend to MySQL and you are now unable to add data.


Did I understand the situation now?

 

[jrsl]

Thats right,

Sorry about that. So its giving me the error message
"The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."

Any idea's?

Justin

 

[Banana]

Okay, I just want to verify something to be absolutely sure... If you can, can you open Access database itself and see if it contains only tables or does it contain forms and other objects? I just want to verify that 1) it's a back-end storing data only and not other objects and 2) there are no permission restrictions.

If all of this works, can you then link to MySQL from Access?

Since MySQL will just be reading the data from Access for displaying, this should not be a problem.

 

[jrsl]

Yes the database only has tables only (no objects ect). as with permissions i dont fully understand (havnt done much work with access). It does have a password to open the actual file tho.

The customers table id used to be auto number however now that it is linked it is a text field and cannot be changed back. Could this have somthing to do with it?

Justin

 

[Banana]

Was that changed by the program or by someone else? If it was the program, I probably would leave it alone.

As for permission, you just answered it - if password is needed, the MySQL will need to have that password so it can access the Access's tables. This would be included as a part of connection string, usually.

 

[jrsl]

I dont think the password has anything to do with it as after the error message i check the MySql database and details have been added to the fields.

However when the program trys to search the access database it returns no results (data is presant in access database).

It only does this when it is linked. When i linked the tables i had to export them to mysql then link them. When linking it creates a new table called customers1, i then deleted customers and renammed customers1 to take its place.

I think its because of the auto number as this was changed due to the link. access and mysql dont reconize each others auto_numbers. Once the link is created the fields cannot be changed.


Any sense here? LOL

Cheers,
Justin

 

[Banana]

All right.

We may want to start afresh. Do you have a backup copy of everything?

 

[jrsl]

Yes i have redone it a couple of times also with the link ect.

also i have installed the program fresh with no data and relinked ect.

when i delete the table to rename the linked one to take its place it says warning "blah blah about its group" but when i look at its group its the only one there.

---
Ive just finished work and will be heading home now so ill respond to your reply in 2 hours. Ill have all the program and databases in front of me then.

Your help is very greatfull.

Regards,
Justin

 

[Banana]

Good, I'm glad we have a backup. (I was dreading the possibility that it was the only copy and we were hopelessly borked then... )

Here's what I would have had done (note that I would only do this because this is more familiar to me, and not necessary because there's no other way to do it):

1) go to Access file, open it.

2) Build a new link to MySQL tables.

3) Write a query to append new records from Access tables to MySQL table.

4) Create either a VBA function (preferred) or a macro that will execute the query using CurrentDb.Execute <NameOfQuery>, dbFailOnError and immediately close Access.

5) In Windows, create a new schedule with Task Scheduler to open a "shortcut" that has the following command:

"C:\<ThePathToMSOffice>\MSACCESS.EXE C:\<PathToDatabaseFile>\<NameOfFile>.mdb /x <NameOfMacro>"

and schedule it to run often as warranted.

This will update MySQL with the current jobs for so frequent intervals as you choose.

Mind, this is a one-way process. If you need MySQL to send back changes to Access, we'll have to work on that, but for now, let's see if this setup will work.

 

[jrsl]

okay so what i have done is made another database file that is linked to the mysql databse.

I have made an append query from the main database to the new .mdb but im using access 2007 and i cannot figure out how to make the append query a macro so i can then store it as a shortcut for task scheduler..

AHHH i know this will work if i can make that macro. (there is not many actions in the macro settings).

Cheers,
Justin

 

[jrsl]

okay got it all done but i cannot find the location of msaccess.exe lol.

all i can find is "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft Office\Microsoft Office Access 2007.ink"

c:\program files\Microsoft Office\msaccess.exe does not work either. not even from run function.

Where is the link to the exe?

 

[Banana]

Try: C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE

 

[jrsl]

Thanks that worked,

Justin