SQL Server Login Prompt & Report

[rookery]

I'm linking to a SQL back-end using SQL authentication from my Access FE.

Everything works fine apart from those reports which contain a lot of data when a SQL Server Login prompt appears halfway through processing. If I then enter the password it continues and works OK but I dont want this prompt to appear.

Anyone know how to get rid of it?

I've heard of this "Remember Password" box but it doesnt appear on my Linked Manager dialog.

 

[FancyPrairie]

You will have to delete your links and the relink them via File | Get External Data | Link Tables... That's where you will see the "Save password" check box.

 

[dhookom]

Consider using pass-through queries and save the login and password in the connection string of the P-T.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rookery]

Thanks for your replies guys.

Prairie is there a way of remembering the password in code? I've already hardcoded the password into the connection string - is this what you mean or is it something different?

 

[FancyPrairie]

There are 2 properties of the connection string:

User ID
Jet OLEDBatabase Password

However, you might want to consider using a data link file. That way, if you ever need to change something you can do it one place. Simply create a new .txt file and then change the file type extension to .UDL. Then open the file. It will bring up a familiar dialog box. Your connection string would then look something like this:

Set cnn = new ADODB.Connection
cnn.Open "File Name = \\path\Your.UDL

 

[rookery]

In my connection string I'm specifying the "UID" and "PWD" already; isnt this enough?

Also (for my sins) I'm using DAO to attach in this DB (legacy thing which I inherited) and dont really want to change it.

 

[rookery]

...isnt there a timeout property somewhere which I can reset because, as I mentioned earlier, this only seems to happen on those reports which take a long while to compile (lots of data)?

I've already set the ODBC Timeout property in the query to zero.

 

[rookery]

...or a way to trap the error?