Remove login prompt with SQL linked tables in Access

Question

I have an issue similar to this one: SQL Server 2008: ODBC connection problems

But mine is unique because I already have the "Save Password" option checked when I link my tables, AND it works fine unless I try to open more than one query at a time.

Steps to recreate:

1) Link a SQL table to an Access 2003 front-end, my DSN looks like this:

[ODBC]
DRIVER=SQL Server
UID=ACD
WSID=ACD
APP=ACD
SERVER=xx.xx.xxx.xx,1053
Description=ACD Connection to SQL Server
Pwd=XXXXXXXX

At first I didn't have the PWD line, it doesn't seem to make a difference with or without that.

2) Open 1 query that uses the linked table, no login prompt

3) Open 2nd query while first one still open, get this error followed by login prompt:

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed. 
The login is from an untrusted domain and cannot be used with Windows authentication.

I can open as many tables as I want directly and I don't get a password prompt.

If I login once, no more prompts until I restart Access, but this isn't an option for my app.

I'm using SQL server auth only, not setup for Windows auth.

Thanks for editing my post, I did try using the code block format option for the DSN part, but it was still making everything on a single-line, weird. — eselk
– eselk, Commented Feb 8, 2012 at 16:51

Matt Donnan · Accepted Answer · 2012-02-08 16:29:03Z

1

You could try using a DSN-Less connection to your SQL server, microsft have a support page for this: http://support.microsoft.com/kb/892490

This should stop the prompts

answered Feb 8, 2012 at 16:29

Matt Donnan

5,0233 gold badges23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eselk Over a year ago

+1 - This looks like a good option if it resovles the problem. This is for an internal project and my boss just gave me some more "real" work, so might be a bit before I can test and accept this answer. I will get to it though, thanks.

Jerzy Gebler · Accepted Answer · 2014-10-10 08:41:16Z

0

You have to save the password in you connection (see parameter dbAttachSavePWD)

Dim MyTable As TableDef
Set MyTable = CurrentDb.CreateTableDef(TableName, dbAttachSavePWD, SourceTableName, ConnectionString)
CurrentDb.TableDefs.Append MyTabl

for example to attach sql table [audit].[Details] as AuditDetails you can use the following code:

Dim MyTable As TableDef
Set MyTable = CurrentDb.CreateTableDef
    (
      "AuditDetails",
      dbAttachSavePWD,
      "audit.Details",
      "ODBC;DRIVER={SQL Server};APP=TransFlow®;SERVER=sqlServerName;DATABASE=dbName;UID=userName;PWD=password"
    )
CurrentDb.TableDefs.Append MyTabl

edited Oct 10, 2014 at 8:41

answered Jun 5, 2013 at 11:31

Jerzy Gebler

9899 silver badges13 bronze badges

Collectives™ on Stack Overflow

Remove login prompt with SQL linked tables in Access

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related