Question regarding Linked Tables (ODBC)

[Sephiroth0327]

Hi All,

I have built various Access Databases (Access 2003) for adhoc reporting needs which have arisen over the last couple years at my firm. Each database has at least one linked table with links out to SQL Server (via an ODBC connection).

When I initially established these linked tables, I was prompted to enter my user id/password for authentication. I assume the linked tables have stored that login info because I was never prompted again.

Recently, it was decided that some of this adhoc reporting will be offloaded onto other users and I don't want them to have to start from scratch. If I send my databases to them, I know they will still work since it will be relying on my username/password for the ODBC connection (assuming they are configured to the ODBC connection in Control Panel>Administrative Tools>Data Sources (ODBC)). That being said, I don't want these to break if I win the lottery and leave the firm.

My question is, how can I update these databases to use different credentials when pulling from the linked tables? Do I need to delete all the linked tables and then re-establish them and enter the new user info? If this is the only way, will this cause my queries to break or will they continue to work if I am sure to keep the linked table names the same?

Thanks!

 

[Sephiroth0327]

Anyone have any bright ideas?

Also, I just realized I created this in the Queries forum when that probably isn't the best place. Not sure if I can move it myself.

 

[Galaxiom]

If you are on a Domain the permissions for allowed users or groups should be set up in the SQL server to use Windows authentication. This is all automatic and no propmting for the password is then required.

I'm not an expert on this but have a look at the connection string property of the linked table in Access. Your credentials will probably be stored there. Removing those parts of the string will cause Access to prompt when connecting.

You can delete your other post by using the edit button under it.

 

[Sephiroth0327]

It is not setup to use Windows authentication. When I was sent instructions from IT on establishing the ODBC connection, they specifically instructed me to not use Windows Authentication. Perhaps this is because the password I use to authenticate to SQL Server is different than my Windows/Network password?

Either way, we are not using Windows Authentication.

 

[Sephiroth0327]

How do I view the connection string though? I'm not familiar with that.

 

[Galaxiom]

My mistake. Connection strings are used in queries. They allow the server to be queried without linking the table.

The username and password for a linked table will be in the ODBC settings.

I have only used Windows authentication so I am not really familiar with the other way. However I think that if you leave out the username and password in the ODBC setup the user will be prompted for their credentials.