Connect to SQL server 2008 database across LAN from Acess 2007 Frontend

[melands2012]

Hi All,

I am trying to connect my access front end to a SQL server 2008 database across LAN for which I will be using windows authentication. My front end is already developed in Access 2003 format.
I can connect to the database from Management studio.
I'm confused by all those methods out there; Adodb, Odbc, and also not sure if I have to use Access data projects instead of simply using .mdb as the front end.
Once the connection is done, I have to keep my frontend in a shared drive and then nearly 10 users will need to get a copy of this front end module on to their computers and will use it to connect to the backend. So I'm not sure where to copy the 'DSN' file if I'm to use one.
Can somebody please tell me which is the best method for me in this situ?

 

[Steve R.]

You will need to do some additional research. But as a brief outline, you need to establish an ODBC Connection. The first step is define the ODBC connection, which can be found in the "Control Panel" under "Administrative Tools", "Data Sources". In my case, SQL Server is defined by a fixed IP address, that may not be your case.

After the ODBC connection is established, you use your front end to LINKto the SQL Server Tables under the Access toolbar under "External Data", "More", "ODBC Databse".

"Access data projects", appears to be more relevant as means of managing your SQL Server tables. Also, if you are really advanced, which I am not, as a means of doing significant processing of the data using SQL on the SQL server itself before transferring the data to the front-end.

You wrote: "my frontend in a shared drive and then nearly 10 users". Seems confusing. The back-end (SQL Server) would be on the shared drive. I would assume that each user would have their own workstation with a copy of the Access front-end on that workstation.

 

[melands2012]

Thank you Steve!
Let me take the confusion away:
My SQL backend is in a server across our LAN. As a practice, when I finished develop my front end it would also be kept in a seperate shared drive, so the users get a copy of it to their machines.
Also I had already made a ODBC connection via a DSN and the connection was Ok - which means I can connect to the database from my machine as the DSN is in my machine. My question if I am to go forward with the ODBC method, do I have to make a DSN in all the work stations, or else where should I create and keep this "DSN" file? Making DSN in all workstations will not going to be a good idea im my situ as users going to be using the frontend from their home computers too

 

[HiTechCoach]

I would not use an ADP. This is a depreciated feature start in Access 2007 and is not longer being developed.

When you are ready to deploy, you can convert the linked tables to Using DSN-Less Connections

I have lots of great info on my site. See Microsoft SQL Server

 

[sjdeetz]

You can also use ADO but people on this site warn about SQL injection attacks.
I think you have to ask yourself if the users on your LAN are going to type in an SQL string to compromise your network. If the answer is not really then ADO has some possibilities. I have used ADO and it seems faster than linked tables via ODBC. I only pull small recordsets into Access to work with. However using ADO involves a lot more development work than using ODBC.