Invoking the 'login to a SQL Server connection' dialog

[goc9000]

Hello again,

I have a MS Access project that uses a SQL Server connection to retrieve data. It is set up to authenticate the user every time the database is used, which is what I want it to to.

The problem is the timing of the dialog. In 'design mode', Access tries to show the Database window as soon as the file is loaded, realizes that it has to fetch data from the SQL connection, and prompts the user for the password - this is fine. However, the 'release version' has a different setup. Automatic showing of the database window is disabled, and the user controls the app via a customized menu instead. The result is that Access only prompts the user for his SQL Server credentials when a form is opened - this isn't OK. I would like Access to always ask for a SQL logon when the file is opened, even in 'release' mode.

I'm kinda new to using Access and SQL Server together, but I suppose there is some way of programatically invoking the dialog via a macro, right ? If so, what function should I use, and how do I make a macro that is run immediately after opening the file ?

It would also be nice if there was some way of detecting a successful logon, so that it can reinvoke the dialog indefinitely if logon fails.

Thanks for everything

 

[WayneRyan]

goc,

You can have a startup form that has the username/password on it.
You can then set a connnection and test it by reading some record
from a table, or do a DCount or whatever.

The code below uses a trusted connection, but it uses the user's
input to define the Server and Database names. The Trusted_Connection
clause can be swapped with (I think) --> uid=Username;pwd=PassWord

On Error GoTo ErrHandler

strConnection = "Driver={SQL Server};" & _
                "Server=" & Me.txtServer & ";" & _
                "Database=" & Me.txtDatabase & ";Trusted_Connection=yes;"

Set DbConnection = New ADODB.Connection

With DbConnection
  .Mode = adModeReadWrite
  .Properties("Prompt") = adUseClient
  .CommandTimeout = 180
  Call .Open(strConnection)
  End With

MsgBox ("Connection OK")

Exit Sub

ErrHandler:

   MsgBox ("Problem connecting to Server/Database - reenter parameters")

Wayne