Problems with linked SQL tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gdonald20 - 31 Oct 2007 11:22 GMT

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

Reply to this Message

Stefan Hoffmann - 31 Oct 2007 11:36 GMT

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 <--

Reply to this Message

gdonald20 - 31 Oct 2007 12:20 GMT

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,
>
[quoted text clipped - 13 lines]
> mfG
> --> stefan <--

Reply to this Message

Stefan Hoffmann - 31 Oct 2007 13:35 GMT

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 <--

Reply to this Message