Hi Mirek,
I think ODBC is the right way to connect from Microsoft Access to your MySQL database. But you need to allow Microsoft Access to connect to your MySQL database first. If your MySQL database is hosted with an ISP this is probably not possible. Most ISPs only allow access from your webserver ip address and disable the create user privilege. There is no way around this.
As an alternative you might consider to use a remote database. It is very simple to add a remote database to your WordPress server with WP Data Access and you can access it from Microsoft Access as well.
Does this help?
Peter
Hi Peter.
Thanks so much for your help and support. I am no database specialist. I can use them and make queries and such, but building the data storage is way beyond me.
What I’d like to do with the new system you installed is to have the data usable in Access as well as be the same data the WordPress user data fields. Can both work together on the same tables?
Hi @federationhq,
With the CONNECT storage engine you can query your Access database from WordPress, but you cannot query your WordPress database from Access. To query your WordPress database from Access, you would need to take additional steps. This is outside the scope of WP Data Access and ussually not possible if you are ISP hosted as most ISPs block database access from other IP addresses.
I’m closing this topic. I have been able to implement a connection to MS Access from WP Data Access (on the server of @federationhq). I will add my experience and the necessary steps to the plugin documentation later.
@federationhq Thank you for allowing me to manage your server and implement the first connection to MS Access from WP Data Access. FIRST CONTACT! 😊
Hope this helps,
Peter
Hai Peter,
Sounds like this is the solution I would like to use.
Can you point out where I can find the documentation on how to connect my MSAccess database from my WordPress site?
Thanks.
Jan
Hi Jan,
It is possible to use WP Data Access to connect to MS Access via a proxy. To use this feature, you currently need to setup your own proxy running MariaDB. Here is an introduction:
https://wpdataaccess.com/docs/documentation/remote-database-connections/odbc-jdbc/
There is a tutorial which explains the additional steps to specifically connect to SQL Server here:
https://wpdataaccess.com/docs/documentation/remote-database-connections/connect-to-sql-server/
Although the additional steps are different from connecting to MS Access, you can use this as a head start. The odbc-jdbc page contains some links (see section resources) that can help to connect to MA Access as well.
Once your proxy is connected to your MS Access database, you can add the proxy database as a remote database in WP Data Access and use it in all the tools. You can also connect from your custom PHP code using WP Data Access classes.
I’m working on a public proxy server which allows premium users to connect without the need to setup a proxy themselves. This feature will be available second quarter next year and makes connecting to external databases much simpler.
Please read through the links I provided and let me know if you have any questions.
Best regards,
Peter
Hai Peter,
Thanks for the quick reply.
Looks like I have some studying to do.
No problem as this is a low priority project.
Have a nice weekend!
Best,
Jan
Hi
How is the Proxy server project?
Hi @jbeltraojr,
Sorry, not much progress! I’m recovering from corona. No health issues anymore, but I entirely lack the energy to make progress. Not really me! Anyway, still hoping to get online this summer…
Thanks for asking 😉
Peter
Can you ask a question?
To access an MS Access database, does Access have to be running inside an MS SQL Server?
If yes, could it be SQL Server 2019 Express?
Another question, does WP Data Access only make remote database connections via ODBC and JDBC to WordPress installations running on MariaDB? What if the provider doesn’t have MariaDB?
I thank,
Hi @jbeltraojr,
>>> Another question, does WP Data Access only make remote database connections via ODBC and JDBC to WordPress installations running on MariaDB?
Unfortunately yes! MySQL does not support the CONNECT engine which is needed to connect to a foreign DBMS.
>>> What if the provider doesn’t have MariaDB?
You can hire a low budget server to run MariaDB as a proxy. The setup is explained here:
https://wpdataaccess.com/docs/documentation/remote-database-connections/odbc-jdbc/
See section: Setup running WordPress on MySQL
>>> To access an MS Access database, does Access have to be running inside an MS SQL Server?
You cann access MS Access directly from MariaDB using the CONNECT engine. There is no need to run SQL Server in between. But is recommended to run MariaDB on Windows if you want to connect to MS Access. There are some third party drivers for Linux, but there are expensive while the necessary drivers on Windows are free.
Hope this helps,
Peter
Hai all,
Back again with some additional questions.
Reading the stuf above, I have a different situation where the MS Access database is available in a file.
In my project, I can upload the file as media attachment but now I’m wondering how to access the contents.
Is this upload a correct approach or would it be better to have the file available on a server via some sort of sftp connection?
The same applies for SQLite database files. I know they have different syntax getting data from the database itself, that’s no problem. I’m just trying to get access to the files…
Hope you can help.
Best,
Jan
Hi Jan,
You need to upload your MS Access file to your server and use the connect engine to connect to MS Access. It is not possible to connect to a remote MS Access file.
If you have a premium license you can now use this feature out of the box (starting from WP Data Access version 5.2). This feature is explained here:
https://wpdataaccess.com/docs/premium-data-services/ms-access/
I haven’t tested SQL Lite. I’ll add this one to my to do list. 😉
Hope this helps,
Peter
mirekkaz – I’d like to know if you successfully accessed your WordPress database with Access through ODBC? If yes, could you please share the ODBC driver and if possible also your connection code? Thank you – Frank
Hi @perlfan,
You can connect to MS Access from WP Data Access, but not directly. You need to go through a proxy. The premium version of WP Data Access gives you access to a proxy that allows you to connect to MS Access without the need to install additional drivers. The current version however uses IP based security which turned out to be unusable to most plugin users. A new release is upcoming which uses account based security and solves the IP issue. Hope to have the new release online by the end of this week.
You can also manage your own proxy (without a premium license). The setup is explained here:
https://wpdataaccess.com/docs/remote-database-connections/odbc-jdbc/
Please use the latest version of the Microsoft ODBC driver for MS Access. It supports MDB and ACCDB files. You can download it for free.
Hope this helps,
Peter
Thanks a lot for the advice! Today I managed to connect MS Access with my WordPress database through the free MySQL ODBC Connector 8.0. It works like a charm and doesn’t require anything else. Thank you, Frank
