Login to linked tables at beginning of code

[nigelbloomy]

I have some vba code that does some work, then starts using a linked table that requires a login (ODBC to a back end Oracle database), and then starts using another linked table that requires another login. Right now my code runs fine. It's just that for the user, they have to sit and watch the code run for a little bit and then they have to login. The code runs a little bit more and then they have to login again. I would like it if I could have something in the code that calls those linked tables and gets the user to login at the beginning of the code instead of having to wait so long. This has also been a problem if the user types their password incorrectly. They are already halfway through the code before the error happens. Every Google search leads me into creating login forms or other things that aren't related. I just need something that lets Access know these are the linked tables the user needs to login into throughout the code, so have them login at the beginning instead of in the middle of the code.

Also, what would be a good way to handle the incorrect password issue if we could catch that problem at the beginning of the code?

[ranman256]

Why not just connect the tables with permanent password, but have the user login at the start of the app?
Eliminate the backend login time, but still login to the front using Windows authentication.
Valid users get into the db. If fails, exit the db.

'usage:
If WindowsLogin(txtUser, txtpASS, txtDomain) Then
   docmd.openform "frmMainMenu"
Else
   docmd.quit
End If




Public Function WindowsLogin(ByVal strUserName As String, ByVal strpassword As String, ByVal strDomain As String) As Boolean
        'Authenticates user and password entered with Active Directory.


        On Error GoTo IncorrectPassword
        
        Dim oADsObject, oADsNamespace As Object
        Dim strADsPath As String
        
        strADsPath = "WinNT://" & strDomain
        Set oADsObject = GetObject(strADsPath)
        Set oADsNamespace = GetObject("WinNT:")
        Set oADsObject = oADsNamespace.OpenDSObject(strADsPath, strDomain & "\" & strUserName, strpassword, 0)
        
        WindowsLogin = True    'ACCESS GRANTED
        
ExitSub:
        Exit Function
        
IncorrectPassword:
        WindowsLogin = False   'ACCESS DENIED
        Resume ExitSub
End Function

[nigelbloomy]

I ended up adding this code to the beginning:

On Error GoTo ErrHandle
Set rTemp = CurrentDb.OpenRecordset("SELECT TOP 1 LinkedTable.* FROM LinkedTable;", dbOpenDynaset)
Set rTemp = Nothing

The error handler checks for error number 3151 which is what happens when the user enters the wrong password.

Ranman your suggestion is way beyond what I understand. I just wanted something to force the login to happen early in the code. Once the user logs in, then Access remembers the connection.

I tried to use something with CurrentDb.connect, but I ended up having to do a simple query that returns 1 line. It seems like there should be an easy way to have Access open the connection to a linkedTable just by naming the table. I couldn't figure it out though.

[ranman256]

create a new OCBC connection and SAVE PASSWORD. This will allow users to bypass the waiting.
The code uses the windows login verification to ensure they are a valid user.

But if this is beyond your scope, then you may be stuck with the delay.