Problems with linked SQL tables

[gdonald20]

Hi

I have an access 2000 database with some linked SQL server 2000 tables.

Everytime i open the database it always asks for the SQL user and password.

This database has eventually to be deployed round an office and obviously i
don't want the end users knowing these details.

How do i stop this happening, i thought that once you created the dsn with
the SQL user and passwords you didn't have to enter the passwords again.

Thanks in advance for any advice.

Gillian

 

[Stefan Hoffmann]

hi,

I have an access 2000 database with some linked SQL server 2000 tables.
Everytime i open the database it always asks for the SQL user and password.
This database has eventually to be deployed round an office and obviously i
don't want the end users knowing these details.
How do i stop this happening, i thought that once you created the dsn with
the SQL user and passwords you didn't have to enter the passwords again.

The best solution is to use an domain-integrated SQL Server with Windows
authentication.

Otherwise you have to create a passthrough query in VBA, support it with
the user credentials and open it. After that all linked tables with the
same signature will use that credentials.


mfG
--> stefan <--

 

[gdonald20]

Hi

I don't know that a domain-integrated SQL server is.

My SQL server is web hosted so i don't have windows authentication for it.

Can you tell me how to write the passthrough query as i think this might be
my only option.

Thanks

 

[Stefan Hoffmann]

hi Donald,

My SQL server is web hosted so i don't have windows authentication for it. Aha.

Can you tell me how to write the passthrough query as i think this might be
my only option.

Something like:

Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set qdf = New DAO.QueryDef
With qdf
.Connect = "ODBC;DRIVER=SQL Server;" & _
"SERVER=Server[\Instance];" & _
"DATABASE=Catalogue;" & _
"USER=Username;" & _
"PWD=Password"
.Name = "foobar"
.ReturnsRecords = True
.SQL = "SELECT @@version;"
End With

Set rs = qdf.OpenRecordset()
rs.Close
Set rs = Nothing
Set qdf = Nothing

For the correct connection string take a look connectionstrings.com.

mfG
--> stefan <--