split db + ODBC

[TheShabz]

Hi everyone.


Just a quick question. Does each individual user have to have the ODBC connection to the server on their own computer if the BE of my access db uses linked tables? This question stems from me wondering where the queries and other things actually take place. Are they on the user's machine or on the network drive the BE is located on?

[Datagopherdan]

When a database is split, all the tables are placed in a separate file usually on a shared network drive. The front-end will contain all the objects, Forms, Queries, Reports etc. except for the tables, which will be linked to the back-end file.

Ideally, each user should have their own copy of a front-end on their local machines, which would link to the one back-end file. So, in a development environment, it makes it a snap. You just make changes on the front-end file and then all the users just replace their existing copies with your updated version, which is already linked to the back-end data source.

Hope some of that made sense. Good luck

Dan

[TheShabz]

That part I know. My question is regarding its relationship with an ODBC connection. Example, I use Access as a front end to my SQL Server back end. There are a few things our web app UI doesnt do and so for our convenience I am using Access forms to replace the web app UI. the Access db is full of linked tables to our SQL Server db. Which machine will need the ODBC connection once my db is split? The user, the network, or both? Where does the connection to the SQL Server actually take place, FE or BE of Access?

[Datagopherdan]

You would need to set an ODBC data source using SQL Server as the driver on each of the user's computers. I believe that Windows does come with the driver for SQL Server, so you would not need to download it.

The easiest way would be to use the same data source name on each of the computers, so it is easily updateable.

As far as where to do the connection, I would link it to the front-end.

HTH

Dan
Access Development

[pbaldy]

You can also set up a File DSN on the server. Link your tables using that and you will not have to set up anything on the individual PC's.

[Datagopherdan]

Paul,

Yes, a much simpler way to go. Thanks for pointing that out. Getting late, my brain is fried, LOL. Time to call it a night.

Dan