Switchboard Error

[LadyDi]

I have inherited a database that contains a switchboard that was set up, I believe using a wizard. There is a table called Switchboard Items and there is a form called Switchboard. However, this is not working properly. I keep getting a message that states: "Compile error: Error in loading DLL" and it highlights the word "Connection" in the code. I have never set up a switchboard in this manner, so I don't know what to do with it. Normally, when I set up a switchboard, I use my own programming and do not need the Switchboard Items table. Is there a simple way to get rid of this message and get the switchboard to work as it did before without completely rewriting it? I'm attaching a picture of the error message I get when I open my database.

 

[boblarson]

Before going further. What happens if you just go to the VBA window and click DEBUG > COMPILE?

 

[LadyDi]

I get the same error message when I click Debug > Compile. It highlights a line of code that looks like this: Set con = Application.CurrentProject.Connection. con is defined as an Object.

 

[boblarson]

Not sure which version you are on but you might need to use

Set con = CurrentProject.AccessConnection

 

[LadyDi]

I am using Access 2010.

I took the .Connection out of the code, and now when I try to open the Switchboard, I am getting another error message. This one states: Run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." When I click on Debug again, it highlights this line of code: rs.Open stSql, con, 1 ' 1 = adOpenKeyset. The line of code above this one reads Set rs = CreateObject("ADODB.Recordset").

If I just click on Debug > Compile (without trying to open the form), I don't get any error messages.

 

[missinglinq]

Guessing that this app was moved from another machine and probably originated in an earlier version of Access, I'd check for Missing References:

If you haven't checked for this kind of thing before, here are Doug Steele's detailed instructions on how to troubleshoot the problem:

Access Reference Problems

Linq ;0)>

 

[Pat Hartman]

Some versions of the switchboard use ADO and that is the reference that is missing.

 

[LadyDi]

I checked and I don't have any missing references. The references that are listed are Visual Basic for Applications, Microsoft Access 14.0 Object Library, OLE Automation, Microsoft DAO 3.6 Object Library, and Microsoft ActiveX Data Objects 2.1 Library. I tried adding references for Microsoft Office 14.0 Object Library and Microsoft ActiveX Data Objects 2.8 Library. Those had no effect on this. I have copies of the database on two different network drives. One works fine and the other doesn't. I compared the references and they are the same. I tried copying the database that works to the second network drive. However, when I do that, that database stops working. I've tried just copying the form and table for the switchboard from the database that works to the one that doesn't, but that didn't work either. Is there something else I can do? The code it doesn't like is this:

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")

I've removed and re-added the word connection each time I made a change to the references, still with no avail. Do you have any suggestions?

 

[noboffinme]

I have had this problem before & after trying many different options, I copied the database (in case I completely screwed it up) & then deleted the Switchboard form & table from the copied DB file.

I then used the Wizard again to recreate the Switchboard with the objects already in the DB.

As a learning exercise, I compared the code to the failing code & found it was a compatibility issue between older & newer versions & anyway, it's now all good.

Hth