Solved

Method 'Connection of Object' failed

Posted on 2010-11-29
4
1,114 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now