SQL Server Login requesting password when query is run.

[Charles]

Sporadically, when I run a query on a linked ODBC table, I get a SQL Server
Login prompt where it lists my ODBC UserID and I have to enter my ODBC
password. This creates major problems when I try to run a long sequence of
queries using VBA (it stops running in the middle). Is there a way I can
stop this prompt from occurring?

Thanks

 

[Armen Stein]

Sporadically, when I run a query on a linked ODBC table, I get a SQL Server
Login prompt where it lists my ODBC UserID and I have to enter my ODBC
password. This creates major problems when I try to run a long sequence of
queries using VBA (it stops running in the middle). Is there a way I can
stop this prompt from occurring?

Thanks

Hi Charles,

In your VBA code, you can open a normal DAO recordset on one of your
SQL Server linked tables. It can be very lightweight if you pick a
table with few records, and specify a snapshot type.

Leave that recordset open while all your other queries run. That
should keep the credentials active, avoiding any further prompting.
Remember to close the recordset and set the object to Nothing when
you're done.

If you need to keep the recordset open beyond one procedure, you can
Dim it as a global object.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Charles]

Hi,

This didn't seem to work. The password requests are still popping up. Any
other ideas?

Thanks

 

[Armen Stein]

This didn't seem to work. The password requests are still popping up. Any
other ideas?

I don't know, we use that technique all the time. Maybe you are
actually losing your connection to the database or something. Anyone
have any other ideas?

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Brian Jansen]

For each linked table, check the ODBC string by hovering over the linked table name. If your SQL databases uses SQL authentication, the ODBC string must contain the UID and PWD. If it doesn't then you will get the SQL Server Login prompt.

To change the ODBC string use
something like this
Set tbn = db.CreateTableDef(originalname, dbAttachSavePWD, sourcename, sConnectionString)
db.TableDefs.Append tbn
db.TableDefs.Refresh



Charle wrote:

SQL Server Login requesting password when query is run.
26-Dec-08

Sporadically, when I run a query on a linked ODBC table, I get a SQL Server
Login prompt where it lists my ODBC UserID and I have to enter my ODBC
password. This creates major problems when I try to run a long sequence of
queries using VBA (it stops running in the middle). Is there a way I can
stop this prompt from occurring

Thanks

EggHeadCafe - Software Developer Portal of Choice
JQuery and partial views in an ASP.NET MVC application
http://www.eggheadcafe.com/tutorial...03-84508b0ae3b5/jquery-and-partial-views.aspx