ODBC over Network (1 Viewer)

[lostside]

Hi!

I was using a front end/back end in access over a network, around 100 users conected to the same access back end, each one wtih a copy of the front end localy.
Now ive started to migrate the back end to Mysql, it's all set up, i connected the front end trough ODBC all works well on my machine, my question is:
Will I have to install and set up ODBC driver in every workstation so they can connect to the back end? is there a way to give each user a copy of the front end wothout any aditional configuration?

tks

 

[pbaldy]

What I do with SQL Server back end tables is create a File DSN on the server and link using that. Then there's no setup required on the user's PC.

 

[lostside]

What I do with SQL Server back end tables is create a File DSN on the server and link using that. Then there's no setup required on the user's PC.

That whould be perfect.

A file with Dns-less? can you post an example?

And how do I select the file inside acess.

Tks

 

[Banana]

Just to clarify:

1) If you're using MySQL and not SQL Server, you'll need to distribute MySQL ODBC driver since they are not installed on Windows PC by default as is the case with SQL Server.

2) I wouldn't call File DSN a DSN-less connection, exactly. As Pbaldy said, it's convenient for sharing but it's still a DSN, just in a file format rather than in registry as is the case with System/User. DSN-less connection would be essentially building your own connection string within code. Doug has an example of building a DSN-less connection.

 

[lostside]

Just to clarify:

1) If you're using MySQL and not SQL Server, you'll need to distribute MySQL ODBC driver since they are not installed on Windows PC by default as is the case with SQL Server.

2) I wouldn't call File DSN a DSN-less connection, exactly. As Pbaldy said, it's convenient for sharing but it's still a DSN, just in a file format rather than in registry as is the case with System/User. DSN-less connection would be essentially building your own connection string within code. Doug has an example of building a DSN-less connection.

I'm familiar with DSN-Less connections.

So using this file i whouldn't need to install Mysql ODBC in every workstation? that's my goal.

Can someone point me in the right way to make this file?

Should i configure a DSN in the server and put the DSN name on the file?

 

[Banana]

Actually, regardless of how you connect, whether with DSN or not, regardless what type of DSN you use, you are still required to ensure that there are client software installed for the RDBMS of your choice.

You may instead program in your front-end file to check that the MySQL ODBC driver is installed and if not, do a download & installation for users' convenience but it still has to be installed nonetheless.

DSN is merely a descriptor of how one can locate a database, and what ODBC driver to use. It isn't going to be of any help if there's no ODBC driver for it.

 

[lostside]

Actually, regardless of how you connect, whether with DSN or not, regardless what type of DSN you use, you are still required to ensure that there are client software installed for the RDBMS of your choice.

You may instead program in your front-end file to check that the MySQL ODBC driver is installed and if not, do a download & installation for users' convenience but it still has to be installed nonetheless.

DSN is merely a descriptor of how one can locate a database, and what ODBC driver to use. It isn't going to be of any help if there's no ODBC driver for it.

So thers no way to achive this whithout installing the ODBC driver in each workstation right?

well i guess i'll have to stay with access back end, or develop a web based front end.

 

[Banana]

Well, if client installation is such hinderance, the option is to use SQL Server Express which is free and most Windows already has SQL Server ODBC drivers installed. With 2008 R2, you can have up to 10 GB database.

 

[lostside]

Well, if client installation is such hinderance, the option is to use SQL Server Express which is free and most Windows already has SQL Server ODBC drivers installed. With 2008 R2, you can have up to 10 GB database.

Yes client instalation is out of question as there are too many workstations with limited premissions and they change frequently.

I'll have a look at SQL Express. Always thougt MySQL whould be better and also i've been using it for a long time for the web.

Tks for your help

 

[Banana]

FWIW - if you already have a existing MySQL infrastructure and you don't want to lose, you may want to see if SQL Server's linked server allow you to work with MySQL tables, most likely as a part of replication schema so your Access client can sync with MySQL data. (I don't know if it's even possible to link to SQL Server's tables that are linked to MySQL and I'm not going to bet that it is feasible anyway)

 

[lostside]

Hi guys,

I'm now using MS SQL 2008 R2.

I need some help now with the connection of the front end, i've tried dsn, dsn less and connection string in a file, the problem is when i close the DB and open it again the tables are not linked to the backend.

Can someone help on that?

thanks

 

[dfenton]

You'll have to explain in detail what you've attempted already that hasn't work. Otherwise, everybody trying to answer will just be guessing, and likely repeating what you already know.