0

I was working on an Access Application that has already an access database (tables, queries etc ) and a front end. I was trying to link access existing database tables with mysql. So, what I have done so far is, I have successfully linked the access tables with mysql using ODBC. My understanding regarding linking access table with mysql db was that, by doing so, I would have the same 'old' access table linked with mysql, but I was wrong. Instead after linking I got a new table (fields same as old) that has links with mysql database.

https://i.stack.imgur.com/Ueiih.png [See this link showing old 'access' and new 'linked' table]

For instance, I had an 'Actor' table in access db (before linking) and after it I have 2 tables. One is the old access 'Actor' table and other is the new linked table named 'Actor1'. So, if I do changes in my old access table, the table in mysql doesn't get change but when I do changes in my new linked table i.e 'Actor1' changes happen in mysql table too.

Here the problem is my front end form/queries are using my access table the old one, so is there any way to use new 'linked' table without changing in the front end ?

Thanks in advance.

HK1
  • 11,941
  • 14
  • 64
  • 99
ihaider
  • 1,290
  • 4
  • 19
  • 38
  • The only MySQL/Access solution I ever built I used pure ADO for everything, no linked tables. If you want to use ODBC linked tables I think SQL Server Express is a much better choice. You should at least be able to get this working but you're probably going to run into quite a few gotchas and caveats. Some of them might be show stoppers. – HK1 Jul 28 '13 at 03:02
  • Ahan ! Actually we already have an access app ( front-end and access db ) that was being used for like last 9-10 months. But now we thought we should move to mysql, so I found ODBC's solution to connect access table with mysql. Anyways, you are saying that using this solution isn't a good practice ? Please confirm ! What might be the serious issues in using this? I dont have much experience in Access ! – ihaider Jul 29 '13 at 05:37
  • There are numerous questions on SO regarding strange issues when using MySQL and Access via ODBC. See this post: http://stackoverflow.com/questions/5842/issues-using-ms-access-as-a-front-end-to-a-mysql-database-back-end And then take a look through these questions: http://stackoverflow.com/questions/tagged/ms-access+mysql?sort=votes&pagesize=50 – HK1 Jul 29 '13 at 12:57

1 Answers1

1

You just need to rename your Linked MySQL table (rename the link in Access only) so that it matches perfectly the name you were using for that same table before in Access. This way you won't have to change your forms, queries, reports.

There is no magic that happens in Access between local tables and linked tables. It's still up to you to move your data, make sure the MySQL table is designed the way you want it, maintain the links, delete old Access tables, etc.

HK1
  • 11,941
  • 14
  • 64
  • 99
  • sorry I didn't mention that I tried renaming the Linked table name same as old one and deleting the old access table, but that din't worked. Getting an error "ODBC -- Insert on a link table 'test' failed" when I try inserting data in table via forms. Anyways Thanks. Atleast you made me sure that I'm heading towards the right path. (that renaming thing) – ihaider Jul 28 '13 at 01:28
  • 1
    Can you update your questions with that information? It sounds like your actual problem is that your ODBC Linked table is not updateable. Can you open the linked table directly in Access and type information in, then move to a different record? Do you get the same error when you try this? – HK1 Jul 28 '13 at 03:00
  • Thankyou @HK1 for help. Actually that issue was due to problem in my SQL Db table. Means, when I exported my access table into SQL via ODBC, everything like attributes, data etc exported fine but things like PK and Auto increment didn't. So I had to go to mysql table and did update the PK and AI manually. – ihaider Jul 28 '13 at 18:48