Access with an SQL Server backend.

My question is this: "Does anyone know where to set up the connectivity for an access database to store data on an Sql Server?"Hey Lamot,

Sure thing.

Go into access and choose the tables tab. Right click a white space and choose "Link Tables" in the "Files of type" drop down, choose ODBC databases. You may have to create a new DSN which is no problem, choose "New" choose "SQL Server" (normally at the bottom) next, give it a name, next, finish, choose your server, next, choose your authentication method, next, choose database for this connection, next test it if you want (the connection) finally choose finish.

You are now presented with a list of tables on the sql server for the connection you just made, you can select any or all that you require, make sure you check the option "Save Password" and you will now see the tables linked in as if they were access tables, though they are represented by a globe.

Hope you find this useful,

Barry Andrew|||It was the answer, but I have tried that. I cannot get at the data source on the server. We have tight security here, that maybe the problem. Is there a way I can convince the Network Engineer to allow this? It is the only way I can get something like this to work properly.|||

lamont_23 wrote:

It was the answer, but I have tried that. I cannot get at the data source on the server. We have tight security here, that maybe the problem. Is there a way I can convince the Network Engineer to allow this? It is the only way I can get something like this to work properly.

Hi Lamont,

First off, if you are using WindowsNT Authentication check that the account you are using is in the security node of sql server.

If you are using domain security, you may also want to check the user of the NTWindows Account is added to the appropriate security group with the appropriate permissions.

If you are using sql authentication, check you are using the right user name and password...

Security is huge I could go on forever, first off try the above then keep us up to date with where you are up to.|||

I checked that out to make sure it was that security node, and it wasn't. But, it has been switched to recognize me as an admin. Now, I go to the database to link it to the SQL Server Database on the server.

And I get the old heave/ho, first I go to a window that is asking for my DSN name. If I put something in or ignore it will then wisk me off to the next screen asking me to create a Data Source Name. The whole time I was surfing through the server attempting to get the correct path of the database that I supposedly set up. Well I did, but just has a couple of tables for now.

That does seems to take to well, telling me that the file name entered is invalid. Help...