Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Bay Area, California, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Server DSN quirk

    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=My PSWD;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)
    <snip>

    'With the connection open, I output the reports to a snapshot file:
    DoCmd.OutputTo acOutputReport, "rptMyReport", acFormatSNP, "MyReport.snp", False
    <cleanup snipped>

    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 <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server DSN quirk

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

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Bay Area, California, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server DSN quirk

    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 <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>.

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Bay Area, California, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server DSN quirk

    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=My PWD;DATABASE=MyDB;AutoTranslate=No;TABLE=tblAnyTab le

    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=My PWD;DATABASE=MyDB;AutoTranslate=No;TABLE=tblAnyTab le]
    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
    <snip cleanup>
    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.
    <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •