Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ PHP, Perl, MySQL and Postgres _ Ms Acess Fe - Mysql Be Connection With Runtime

Posted by: withallthymind Oct 30 2019, 09:04 PM

For the first time, I have successfully split an access d/b with the BE located on a MySQL remote server (accessed via an IP address). The FE (on my machine) is connected using the MySQL ODBC 8.0 Unicode Driver and works well with the remote BE.

My challenge is getting it to work with MS Access Runtime on another (single) PC in different location. Is there a way to accomplish this? My understanding is that the compiled MS Access file (*.accde) is somewhat self-contained to work with Runtime and has the connection information to point to the remote d/b. I've attempted to make it work, but to no success. iconfused.gif Can a compiled FE work on its own with Runtime or is there more that has to be done? Do I have to create a DSN-less connection with VBA?

Thanks in advance for any help.


Posted by: AlbertKallal Oct 30 2019, 09:50 PM

Yes, it should work.

However, you need and want to force access to use DSN-less connection.

Thankfully, that is the default, but ONLY if you choose to use a FILE DSN during the linking process.

If you link all your tables, but MAKE sure you created + used a FILE DSN when you did the linking?

Well, then you can compile to the accDE, and running that accDE with the runtime should work on any machine that is part of your network and has use of the same IP address you used on your development machine when linking.

Now, over time a good many people will adopt some kind of re-link code, and that re-link code will also (should be) DSN-less.

However, if you out of the box link all tables using a FILE DSN, then after linking, access does not use nor care about the DSN - it is ignored, and the DSN can even be deleted, or in your case not present on the machines you plan to deploy to.

I should point out that if you link using a "user" or "system" DSN then all of the above does not apply, and access will not by default create DSN-less links.

So the key concept and trick here is to ensure that you linked the tables using a FILE dsn.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Posted by: withallthymind Oct 31 2019, 08:01 AM

Thanks Albert! yayhandclap.gif I was not using the FILE DSN, so that's where I was going wrong. The "FILE" DSN makes complete sense now. I've already reconfigured the accde file and will try it as soon as my business partner gets into his office. Will keep you posted . . .



Posted by: withallthymind Oct 31 2019, 12:57 PM

Great news! It worked! Using the FILE DSN was the trick. We are up and running with a cool application that serves our specific needs. THANK YOU Albert! notworthy.gif

Posted by: AlbertKallal Oct 31 2019, 01:55 PM

Appreciate the follow up. It is interesting, but it not particular "common" knowledge that Access works rather different when using a FILE dsn as opposed to a system or user DSN.

As a result, most people will suggest that you have to adopt some DSN VBA code to re-link as DSN-less.

It still perhaps over time to consider and adopt some re-link code idea (just like some do even when using Access back ends).

However, it is certainly possible to achieve this goal, and do so without some special VBA re-link code.

So Access can and will create DSN-less links for you, but the trick means you are limited to a FILE dsn, and that's just fine in most cases.

This approach works for SQL server, MySQL etc.

Now that you have this working, then over time you can consider linking the table(s) and NOT have to include the user + password in the tables. Again, not a big issue, but do keep in the back of your mind that you can actually link the tables without including the user+password. This thus increases security a lot. However, it does mean on Access start up you have to ask or get the user + password, (or have that in code). Once the logon is executed, then all of your linked tables (without the user + password) will now work.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada