Method 'Connection' of object '_CurrrentProject' failed.

[Gammachaser]

I have a Function that I have been using to determine what other workstations are logged into a database using the CurrentProject.Connection. It worked well until Access 2010 got pushed to my pc and now I get the subject line error message. I also had Access97 and Access2003 on my pc so the IT folks here told me to get rid of the other versions because they were causing conflicts. I can understand that and since I had other problems that looked like corruption, I got rid of both older versions and some of the problems persisted. So, I had Access2010 reinstalled and all of the other problems went away, but I still get this message. The real kicker is it still works on two other machines I have tested it on that also got upgraded.

Here is the code (much of which I got somewhere on this site.)

Function LoggedInPCs() As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

While Not rs.EOF
    Select Case rs!Connected
        Case True
            LoggedInPCs = LoggedInPCs & Trim(rs!Computer_Name)
            While Right(LoggedInPCs, 1) = Chr(0)
                LoggedInPCs = Left(LoggedInPCs, Len(LoggedInPCs) - 1)
            Wend
            Select Case Left(rs!Computer_Name, Len(fOSMachineName)) = fOSMachineName
                Case True
                    LoggedInPCs = LoggedInPCs & " (This Computer)"
            End Select
            LoggedInPCs = LoggedInPCs & vbCrLf
    End Select
    rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set cn = Nothing
End Function

The databse is split, copies run independenly on each machine. Indications are that something is wrong on my pc, but since I had Access2010 reloaded and all other problems went away, I am stumped. The failure is on the line 'Set cn = CurrentProject.Connection' Any ideas? I am not very strong on ADO...

 

[vbajock]

I'd start by inserting a Stop before the Set cn command and then display this to the Immediate Window:
? currentproject.Connection.ConnectionString

What do you get? If it is valid, see if a work around can come out of using the cn.open method and the connection string.

 

[vbajock]

I'd start by inserting a Stop before the Set cn command and then display this to the Immediate Window:
? currentproject.Connection.ConnectionString

What do you get? If it is valid, see if a work around can come out of using the cn.open method and the connection string.

 

[vbajock]

I'd start by inserting a Stop before the Set cn command and then display this to the Immediate Window:
? currentproject.Connection.ConnectionString

What do you get? If it is valid, see if a work around can come out of using the cn.open method and the connection string.

 

[Gammachaser]

There appears to be an echo in here...

I have done that... I get an error whenever I refer to the connection property of the current project. In break mode the message is Error in DLL, but this is not the first time I have gotten conflicting messages and neither one is really correct. To make sure the DLL was OK I had Access reloaded... but to no avail. Maybe there is a corrupt DLL that did not get reinstalled, but I can't see why it would not.

Anyone know which DLL would be responsible?

 

[vbajock]

Sorry about the repeats, one of the servers I work just does that for some reason, and this is the only site that doesn't surpress the repeated postings.

Have you tried going to Tools/References and making sure you are using the most current ADO version? You should be using 2.6 and above on 2010, I believe on Windows 7/Office 2010 machines it is 2.8 as the default.

You can find a diagnostic here:

http://www.microsoft.com/en-us/download/details.aspx?id=1953

You can find other tools here:

http://social.msdn.microsoft.com/Search/en-US?query=mdac download&Refinement=95&ac=3

If you are using an older operating system with a 2010 installation that could be your problem.

IIRC, if you do a Windows Update on your machine manually, you get an "optional" upgrade of database components if those on your machine are obsolete.