Solved

Method 'Connection of Object' failed

Posted on 2010-11-29
4
1,121 Views
Last Modified: 2013-11-28
I am opening a switchboard in MS Access 2007 which has not changed for a few years but I am now getting an error when the form opens:

Runtime error '-2147418113 (8000ffff)'
Method 'Connection of Object'_CurrentProject'  failed.

The debug line that is a problem is:

 Set con = Application.CurrentProject.Connection

see entire code attached.

Any ideas?
Private Sub FillOptions()
' Fill in the options for this switchboard page.

    ' The number of buttons on the form.
    Const conNumButtons = 8
    
    Dim con As Object
    Dim rs As Object
    Dim stSql As String
    Dim intOption As Integer
    
    ' Set the focus to the first button on the form,
    ' and then hide all of the buttons on the form
    ' but the first.  You can't hide the field with the focus.
    Me![Option1].SetFocus
    For intOption = 2 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("OptionLabel" & intOption).Visible = False
    Next intOption
    
    ' Open the table of Switchboard Items, and find
    ' the first item for this Switchboard Page.
    Set con = Application.CurrentProject.Connection
    stSql = "SELECT * FROM [Switchboard Items]"
    stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
    stSql = stSql & " ORDER BY [ItemNumber];"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 1   ' 1 = adOpenKeyset
    
    ' If there are no options for this Switchboard Page,
    ' display a message.  Otherwise, fill the page with the items.
    If (rs.EOF) Then
        Me![OptionLabel1].Caption = "There are no items for this switchboard page"
    Else
        While (Not (rs.EOF))
            Me("Option" & rs![ItemNumber]).Visible = True
            Me("OptionLabel" & rs![ItemNumber]).Visible = True
            Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
            rs.MoveNext
        Wend
    End If

Open in new window

0
Comment
Question by:snailcat
  • 3
4 Comments
 
LVL 84
ID: 34234243
Have you performed maintenance on the database lately? If not:

1) Make a backup.
2) Compact the Database. In Access 2003, click Tools - Database Utilities - Compact. In Access 2007, click the Office Button - Manage - Compact.

If that doesn't work, you might try to Decompile the database. To do that, build a Shortcut with this as the Target:

"full path to msaccess.exe" "full path to your database" /decompile

Now Compact again, then open the VBA Editor and click Debug - Compile. Fix any errors, and continue doing this until the menuitem is disabled. Now compact again.

If that doesn't work, then you might try building a new, blank database and importing everything to that database.
0
 
LVL 12

Author Comment

by:snailcat
ID: 34234572
LSMConsulting--thanks for the ideas.  I had already tried to  compact and repair.
I tried the decomplie/compile without success and also tried importing into a new blank database but this one form is still an issue.

I have older versions of the database that have never been an issue and have not been opened for many months and I am getting the same error for that form in the code described.


Any other thoughts?
0
 
LVL 12

Accepted Solution

by:
snailcat earned 0 total points
ID: 34234781
I repaired my MS Access and the problem is gone.

Thanks for the input.
0
 
LVL 12

Author Closing Comment

by:snailcat
ID: 34272836
Tried expert comments which did not work.  Then did a repair on MS Access on my own and that fixed the problem.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question