SQL Server DSN quirk

Topic

New Reply

I am automating an existing Access 2000 frontend with a linked SQL Server backend. When the user runs reports interactively, the ODBC datasource (user DSN) prompts the user for a password the first time the connection is opened (as intended). To run the reports unattended, I use the following code (formatting removed):

Dim cnn As Connection, Dim dbs As Database, strcnn As String, wsp As Workspace
strcnn = “ODBC;DSN=MyDSN;Description=MyDB;UID=MyUser;PWD=MyPSWD;DATABASE=MyDB;AutoTranslate=No”
Set dbs = OpenDatabase(Name:=”MyDB”, Options:=False, readonly:=False, Connect:=strcnn)
Set wsp = DBEngine.CreateWorkspace(Name:=”ODBC”, UserName:=”MyUser”, Password:=”MyPSWD”, UseType:=dbUseODBC)
Set cnn = wsp.OpenConnection(Name:=”MyDB”, Options:=dbDriverCompleteRequired, readonly:=False, Connect:=strcnn)

‘With the connection open, I output the reports to a snapshot file:
DoCmd.OutputTo acOutputReport, “rptMyReport”, acFormatSNP, “MyReport.snp”, False

This works great on my computer: when I run the report interactively, I get the “SQL Server Login”. When I run it programmatically, I don’t.
But on any other computer, I get the “SQL Server Login” prompt demanding the password, and I have no idea why.

I tried different code to do the same, and it always works, but only on my machine. So I’m not sure the code itself is the issue, there must be something else that’s different on my box.

Any Ideas? I’m officially stumped .

Reply | Quote

Replies

When you create the DSN on the other PC’s, did you use the same login Verification ?
If SQL verification ? Did you store the password in the DSN ?

Reply | Quote

Francois,

since the application was already installed and running on other PC’s (w/o the report automation), I first had to recreate the user dsn on my machine before writing the code. I used the same login verification as on the other PC’s. I compared each screen to be 100% sure. The password is not stored.

When the users run the reports interactively (as before), they are prompted for the SQL password (as before). So that part works unchanged, as it should.

But when they attempt to run the reports automatically, that’s when they have the problem: they are still prompted for the password, whereas on my machine, running the code eliminates the login prompt.

Thanks for your help, I’m still w/o a clue .

Reply | Quote

I found a solution on microsoft.public.sqlserver.odbc: create a simple query in design view w/o specifying any tables. Right-click in the blank, grey “tables” area to display the query properties. Add the DSN connection string including the password to the Source Connect String, e.g.
ODBC;DSN=MyDSN;Description=MyDSN;UID=MyUser;PWD=MyPWD;DATABASE=MyDB;AutoTranslate=No;TABLE=tblAnyTable

Now just drag a field from tblAnyTable with a criteria that returns no records (e.g., User ID of -1). The SQL View would look something like this:
SELECT tblAnyTable.EmpNo AS Expr1
FROM MyDB IN ” [ODBC;DSN=MyDSN;Description=MyDSN;UID=MyUser;PWD=MyPWD;DATABASE=MyDB;AutoTranslate=No;TABLE=tblAnyTable]
WHERE (((tblAnyTable.EmpNo)=-1));
Make sure the query returns no records. All youwant is to get connected to the database w/o a password prompt. I even hid the query, so the users won’t see it. Save it as qryDSN or whatever.

For the code, I use
Dim db As Database
Dim rs As Recordset
Dim qdfParmQry As QueryDef

Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs(“qryDSN”)
Set rs = qdfParmQry.OpenRecordset()
‘no records are returned to the recordset
DoCmd.OutputTo acOutputReport, “rptMyReport”, acFormatSNP, “MyReport.snp”, False

after which I close the aplication.

Now I can launch the database at night to run unattended reports w/o a password prompt. This is in essence the same thing I did initially, but this one works on the user’s workstations as well.

Reply | Quote