0

Im having trouble using the MySQL ODBC connector. I have a MS access frontend that im trying to connect to a MySQL DB. It connects fine when running the frontend and DB on my dev machine. I have moved the front end and DB onto the clients server. The clients assess the fornt end via a shared folder on the individual user PCs. I have set up a file DSN ODBC connection as below. When i open the front end on one of the clients PCs i get the ODBC--call failed. How do i get the linked tables to look at the server MySQL DB considering ms access is not installed on the server. I have tried to set uo the file DSN via the users pc i.e. open linked table manager and open the File DSN get the ODBC--call failed on connect.

File DSN set up

[ODBC]
DRIVER=MySQL ODBC 5.3 Unicode Driver
UID=root
PASSWORD=root
DFLT_BIGINT_BIND_STR=1
PORT=3306
DATABASE=productionlist_be
SERVER=localhost

thanks in advance Kelly

infused
  • 24,000
  • 13
  • 68
  • 78
Kelly
  • 31
  • 2
  • 4

1 Answers1

1

Welcome to stack-overflow Kelly.

You are missing few points.

  1. The MySQL database Server must be accessible by all your clients.
    1. Either a local machine hosting MySQL server or from internet its up to you
    2. Must have a static IP or domain name.
  2. Access Front-Ends should not be shared but sent to all of your clients/employees
    1. (This way you are achieving true "multi user access" and thats the main idea behind front and back ends)
  3. All of your client PC must have MySQL ODBC driver installed
  4. All of your clients must have required version of Access or Access Runtime installed

Only after setting up all this, you can think about distributing your application to your clients.

Modify your File DSN replace the localhost with the MySQL Database server ip like

SERVER=SERVER_NAME_OR_IP

Also it is best to refresh the odbc links via VBA code you will find much help here: How do you programmatically update a linked table in Access that will refresh data types too?

OR Relinking database tables: Access, VBA

hope this helps to get started :)

Community
  • 1
  • 1
Krish
  • 5,917
  • 2
  • 14
  • 35