0

I am using MySQL DB for my android application. I have installed phpmyadmin on my Ubuntu LAMP server and using it to access my DB. Is there any possibilities to access the DB using MS access 2010, as I need to do perform modifications on a regular basis to my DB

Pradeep Kumar
  • 4,065
  • 2
  • 33
  • 40
RKM
  • 89
  • 1
  • 3
  • 15
  • are you asking to connect php with ms access? if so you can try http://stackoverflow.com/questions/19807081/how-to-connect-php-with-microsoft-access-database and delete current question – Pradeep Kumar Mar 21 '17 at 06:25
  • Actually I want to update my db on a daily basis. Currently im using phpmyadmin.. For updating either I have to export it to an excel make the changes and import it again or write a update query and execute it daily. I find using ms access 2010 easy for this task as I can do it directly in MS Access and save it. But I dont know if I can open mysql db in MS Access or not. I hve tried opening a oracle db but I am not sure about mysql. Hope I made my point clear here – RKM Mar 22 '17 at 06:37
  • using phpmyadmin too u can directly update. why don't you design a simple screen in php to update and then update it yourself daily instead of handling directly to DB. – Pradeep Kumar Mar 22 '17 at 06:57
  • This person wants to do exactly what I want to do - connect a MySQL database on a server to an Access front end running on my local computer. There are tons of answers but NONE that answer his question. It seems all the answers try to tell him to do something different than he asked. The same thing happened when I asked the same question (2.5 years later) - no one gave a straight answer. Please, can someone tell us how to connect MS Access to a MySQL database on a server? Thanks. – David in Mississippi Aug 07 '19 at 23:40

3 Answers3

2

If the aim is to change the data and not the structure, using MS access is a good solution, as you can edit anything in linked tables very efficiently with litlle setup efforts.

To achieve this, first you need to download and install the MySQL ODBC driver.

In order to avoid issues, I advise you to install the 32 bit driver, even if you're on a 64 bit system. The 32bit will work flawesly, which is not always the case of the 64 bit.

Once installed, open the ODBC control panel and add an entry pointing to your MySQL database

Then in Access you can add linked tables using this ODBC entry

Once the table are linked, they will behave as if they are MS access tables and you can open them and edit data, and much more.

Thomas G
  • 9,886
  • 7
  • 28
  • 41
  • Thanks for the steps will try it and let u know the outcome! – RKM Mar 23 '17 at 07:27
  • 1
    Hello, I have tried your solution, Its giving me error while setting up ODBC . Can't open named pipe for host, It is also not working in TCP/IP connection mode. Could you please suggest – RKM Apr 09 '17 at 16:26
  • 1
    You should add a **User DSN** and not a **System DSN**. Is that what you tried? What happens if you ping your MySQL server's address? – Thomas G Apr 10 '17 at 09:03
  • Yes I am adding it as User DSN. And if i ping my server's address I'm getting "Request timed out". But my server is active, i am able to access it using phpmyadmin and in my app – RKM Apr 10 '17 at 15:01
  • [connector-odbc-usagenotes-apptips-microsoft-access](https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-access.html) is needed too! – ComputerVersteher Aug 08 '19 at 23:21
0

You can use Microsoft Access as a front-end to your MySQL database.
Refer: MySQL Documentation

But for your case, directly accessing database is wrong. I suggest better you have a view page with php to update DB with proper required validations.

Sagar V
  • 12,158
  • 7
  • 41
  • 68
Pradeep Kumar
  • 4,065
  • 2
  • 33
  • 40
  • 1
    not only this is not an answer but rather a comment, it is also completely misleading. – Thomas G Mar 22 '17 at 12:29
  • I dnt have much knowledge on php.. Anyways will do some analysis on this point. Thanks for the answer. – RKM Mar 23 '17 at 07:29
  • not only in php you can try in your known frontend software. updating directly using db is always risky. – Pradeep Kumar Mar 23 '17 at 07:46
  • 3
    doing this in PHP would require to code a complete interface . This is on left hand completely dumb for this purpose, and on right hand not an "answer" to his question. He is specifically asking for help to do this in **MS Access** because he has understood that this will save him a lot of time and I confirm that this is probably the best approach for what he has to do. – Thomas G Apr 10 '17 at 09:06
  • @PradeepKumarPrabaharan: maybe you thought of an external internet webserver, where exposing MySQL to public can be dangerous (if you don't know how to secure), but OP seems to use a local linux server. If it was online, you should have suggested an API solution like REST. – ComputerVersteher Aug 08 '19 at 23:13
0

The answer by Thomas G does an excellent job of describing WHAT we need to do, and even provides some good links.

What we need now is a good, step-by-step description of HOW to accomplish all the steps Thomas G describes.

I've been looking for this for a long time, and still no luck. However, I'm possibly on the track of getting that answer, and if I find it, I'll come back here and post it.

David in Mississippi
  • 97
  • 1
  • 5
  • If you had read the link to the MySQL Connector docs that Thomas provided, you would have found [connector-odbc-usagenotes-apptips-microsoft-access](https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-access.html). Btw this is not an answer!. Should have been a comment or new question! – ComputerVersteher Aug 08 '19 at 23:18
  • I read the link that Thomas provided, and it did not answer the question. The link you provided only discusses "to IMPROVE the link..." it does not discuss how to establish the link, which was my main question. Having been scouring help sources for several days now, I am still unable to get the MySQL ODBC Driver to show up in the MS Access add ODBC data source list. Still chasing that down, and criticizing someone else here won't help solve it. – David in Mississippi Aug 10 '19 at 09:57
  • More step-by-step? Remote-Desktop connection ;-) Maybe you are on Office x64? If not what is unclear? Of course you have to read the linked docs in `Using Access as FE`. – ComputerVersteher Aug 10 '19 at 10:20
  • You missed to tell that driver doesn't show up in ODBC-Admin (Hidden at the end of comment). I need to know What driber us installed (version and bitness), which Office (version and bitness) and what MySQL-Server (version, bitness should be x64). Most likely you installed the wrong bitness of driver (should be same as office) – ComputerVersteher Aug 10 '19 at 10:48
  • Finding your former question https://stackoverflow.com/questions/57401208/seeking-step-by-step-guide-to-connect-ms-access-to-a-mysql-database?noredirect=1&lq=1 shows the need of a discussion in a chat https://chat.stackoverflow.com/rooms/197759/ms-access-odbc-mysql-on-webserver – ComputerVersteher Aug 10 '19 at 11:27