SQL server login from Access

[Ratter]

I have upsized an Access application to a SQL server back end and I cannot login using VBA code.

I have produced a really simple test set up with just one attached table and I am using this code directly from my autoexec

Dim cn As New ADODB.Connection
Set cn = New ADODB.Connection

Dim strQuery As String
strQuery = "Provider='sqloledb';Data Source='reddwarf';Initial Catalog='SISExpress';User Id='Test';Password='test'"
cn.Open strQuery

If cn.State = adStateOpen Then MsgBox "Connected"

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblStudents")

Now, as far as I can see it connects OK and returns adStateOpen

BUT as soon as I try to run a query of any sort Access tries to connect again and insists on using Trusted Connection (which isn't going to work because we have a Novell network)

Anybody got any ideas? As far as I can see and I've done a fair bit of Googling and read many posts on here - this is a sensible thing to try - but why doesn't it work?

I might add that if you uncheck the trusted connection from the error dialog you can then log in manually with no problems. A colleague has written a simple test in .NET which connects to the same database without problem.

 

[wh00t]

try accessing your recordsets this way instead

rst.open "SELECT * FROM tblStudents", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

 

[Ratter]

Thanks for that but it didn't work - I just get a login failure.

I'm still confused as to what is happening because surely if it returns success when connecting, I don't understand why it tries to connect again when you actually try to access a table by running a query.

What I really want to do is to use the forms and queries I already have (which use DAO rather than ADO) and to log in automatically.

Is there a setting somewhere that I've missed that insists on Trusted Connection being used? If so it must be in Access somewhere because I can connect in .NET automatically without any problems.

Rewriting the whole app in .NET isn't something I fancy

 

[DataMiner]

Any answers to this question? I am having the same problem. My DSN file contains the SQL Server user ID and password, but users are still getting asked for this when they login into the Access frontend. HOw to I make this stop?

ONe thing I noticed, although the uid and pwd show up in the DSN file, they do NOT appear in the connection string for the linked tables. Is this the problem?