Problems with linked SQL tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
Click to expand...
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 <--
 
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
 
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.
Click to expand...
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 <--
 
Back
Top