DNS / DNS-Less (1 Viewer)

[xDamo66]

Hi.

I have a front-end Accesss 2002 database on a server - with ODBC linked tables to a mySQL database (also on the same server).

I have 6 users - who need to use the Access database and they all have different usernames and passwords to the mySQL database.

Setting up individual User DSNs work for each user - but as they are all sharing the same Access database, the linked-tables (and permissions etc) will change to the last user's DSN.

Which means that others cannot access the data!

Is there a simple way to get each user to access the same database based on their own DSNs?

D

 

[FoFa]

Typically we would setup an Application ID to the BE and use that internally to connect to the ODBC source.

 

[Pat Hartman]

You should create ONE DSN and use it for all users.

 

[xDamo66]

Hi Pat: As all the users have different permissions set in the backend mySQL database, if I just had one DNS can I still have separate usernames and passwords each users?

If so, how would I do that?

Thanks in advance.

D

 

[Pat Hartman]

Choose the "use windows NT authentication" option so you don't have to input a specific userID and password.

 

[xDamian]

I created a single DNS file and the connection worked fine my machine to the server. When I used the same DNS file on another machine, again, the connection was fine but when I tried to enter some data back on my machine, the ODBC Connection failed!

Presumably, it's some sort of permissions issue but according to mySQL server, I have set a general User (ie host = %).

If it's any help, my DSN file is below:

[ODBC]
DRIVER={MySQL ODBC 3.51 Driver}
SERVER=10.0.0.106
DATABASE=mysqldata
UID=User
PWD=10May
PORT=3306
OPTION=3

Do I need to state multi-user / shared option in the DNS file?

 

[Pat Hartman]

Maybe MySQL works differently from other ODBC data sources. I don't have any answer except that my SQL server DNS's do not contain a userID and password and I use NT authentication to avoid this problem.

You should probably find a site that specializes in MySQL and ask your question there.

 

[xDamian]

Thanks anyway, Pat: Will have a look at som mySQL forums.

 

[xDamian]

Managed to solve it!

On the advice of another, I ticked the 'Save Password' box that appears at the foot of the screen that pops up when you're linking tables and, voila, ODBC is now up and running on all machines!

D