SQL Database via ODBC - password prompt

[PThomas]

One of my colleagues has written a basic access database with linked tables to a SQL server. Each time a user opens it they are prompted to enter login/password information.

Can the database be opened in some way with code and the login information passed to it?

[blazonQC]

Which sql db is it? MySQL? MS SQL Server? You can pass it through the connection strings, but the strings change for every type of sql db.

http://www.connectionstrings.com has a HUGE list of connection strings for db's if you want to check that out.

HTH,
Chad

[PThomas]

Hi Chad

Access 2003 with MS SQL Server 2005, we are currently using a System DSN configured manually in ODBC manager on each client (Win XP).
It would be a lot easier if we can hard code this connection along with login/password to save us having to create this DSN on each PC and the user having to retype the password each time they connect.

I have looked at how it is connected at the moment, it uses the Get External Data, Linked Tables option and then ODBC Data source.

I read quickly through the link you suggested, how would this type of link work as a replacement/to authenticate a link tables solution?

Thanks

Paul

[geekgirlau]

With SQL Server you can setup the security to use trusted network access, and add the necessary users to a group that has permissions to the database. This way the network logon for each user controls whether they have permissions to the data, and they are not required to login to the database separately.

[PThomas]

I presume a local DSN will still need configuring as a data source on each PC though?

Is there any way around this with linked tables?

[geekgirlau]

I'm a bit rusty, but from memory you can set the connection in code on the fly, which negates the need for a local DSN.

[PThomas]

I'm a bit rusty, but from memory you can set the connection in code on the fly, which negates the need for a local DSN.

Any clues as to the command I need to use?

Thanks

Paul

[geekgirlau]

Public declaration for the connection string

'SQL Server ADO Connection String
Public Const DbADOConStr As String = "Provider=sqloledb;" & _
"Data Source=111.11.11.11;" & _
"Initial Catalog=MYDATABASE;" & _
"Integrated Security=SSPI"

In the procedure:
Dim cnn As New ADODB.Connection

cnn.Open DbADOConStr
cnn.Execute (SQLString)

[PThomas]

Thanks for the help, I'll give it a go!

I presume this will work with linked tables?

[debauch]

Someone may have already stated this, but delete the link to the SQL table in access, and when you create the link again to the same table, there is a little checkbox that says 'save password'. So when you call data from the SQL table using Acess, you are not prompted for l/p. I setup this up not long ago, so we could run data without anyone around. It works like a charm.