Method 'OpenConnection' of object '_CurrentProject' failed

[NoWittyName]

I have two different databases written in Access 2002 with SQL server 2000 back-end. Both databases, occasionally give the above error when first starting for the users, but never when I try to run them.
The error number is -2147467259.

I have searched high and low but can find little references to this problem. Anyone out there have any bright ideas on a solution.

I have managed to reduce the frequency by stopping the startup form from running for 5 seconds on open, but it still happens occassionally!

 

[jfischer]

Please provide more information. Are you manually setting the BaseConnectionString in your code to open the connection? Or, are you using the stored connection information that Access keeps for the project? What is your connection string? Do you use SQL Server security or integrated security (Trusted Connection) with Windows NT?

 

[NoWittyName]

Sorry I was not clear,

I use a trusted connection

On both programs I have a live database and test database, so that I can easily switch between the two sets of data I wrote the following code in the form_open event. The system falls over on the indicated line

strConnection = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=Tracker;DATA SOURCE=naboo"

--------------falls over here------------
Application.CurrentProject.OpenConnection (strConnection)
-----------------------------------------
Module1.systemLive = True
If DLookup("SystemAdministrator", "staff", "username='" & Module1.Username & "'&quot Then
If MsgBox("YOU ARE A SYSTEM ADMINISTRATOR DO YOU WISH TO USE THE TEST SYSTEM DATA?", vbYesNo) = vbYes Then
'Use the test system
strConnection = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=TrackerTest;DATA SOURCE=naboo"
Application.CurrentProject.OpenConnection (strConnection)
lblTestSystem1.Visible = True
lblTestSystem2.Visible = True
Module1.systemLive = False
End If

 

[jfischer]

Thanks for the added info. Here are a few things to check and try.

1) Since delaying the startup form for five seconds improved things somewhat, try adding a DoEvents statement in front of the OpenConnection to relinquish control back to the OS for a bit before the Open occurs. The Tables list may not be completely built/refreshed on startup, especially if the list of tables is long and your user has a slower machine than yours, before your OpenConnection occurs. The DoEvents could help with that timing issue.

2) Check for References differences between your and your user's machines. For example, if your machine uses the Microsoft Access 10.0 Object Library (Access 2002), but your user's have the Microsoft Access 9.0 Object Libary (for Access 2000), you could have connection problems.

3) Try distributing the program without any connection information in it to start with. You can run the following routine from the Immediate Window (Ctrl-G) to clear the connection:

Sub subDisconnect()
  Application.CurrentProject.CloseConnection
  Application.CurrentProject.OpenConnection
End Sub

This routine is documented in MS KBase article 202615.

4) You may also have a resource locking problem with the integrated security between Windows NT and SQL Server. If SQL Server is hanging on to some internal resources related to the user, but not to you, it can interfere with their subsequent connections.