Re: Automating login from MS Access front end

Thanks to each of you for your replies. The concepts that you outlined were the same that I had thought of. It was the implementation that got me hung up. I finally found the answer in the Office97 Programmer's Guide at msdn.microsoft.com.

Basically, you have to get Access to logon to Oracle and then terminate the connection. Because Access caches the ODBC logon information it remains available throughout your Access session. To make this happen automatically I created a function which I call from the AutoExec macro so that it runs whenever I start my Access application. Here is the code:

'This function creates a connection to the data warehouse, then closes
it.
'Because Access caches the connect string, the user is never prompted
for the username and password
'to make the connection.
'This must be a function (even though it returns no value) so that it
can be called from a macro.
'This is called from the AutoExec macro so that the connection is made
when opening the database.
Function PreConnect()
  Dim wrkDW As Workspace
  Dim dbsDW As Database
  Dim strConnect As String   

  'Create a string that holds the ODBC connect string   strConnect =
"ODBC;DATABASE=DWTest;DSN=DSN_Name;UID=username;PWD=password"   'set the workspace variable to point to the current workspace   Set wrkDW = DBEngine.Workspaces(0)
  'In this workspace, create a database object that connects using the connect string
  Set dbsDW = wrkDW.OpenDatabase("", False, True, strConnect)   'close the database. The login is still cached so that it is available for any
  'Access object that needs it during the same session   dbsDW.Close
End Function

Obviously the details have been replaces with representations for the actual connect string, but the principle is there. As was mentioned in this thread, this means that you have to manage security for your Access application, but once you know the user should have access to the application it allows you to provide access to the data in Oracle without a separate login.

Thanks again for the pointers! Received on Mon Jan 29 2001 - 16:35:05 CST