Switchboard Error when Converting from Access 97 to 2007 (1 Viewer)

[Databases]

I have two machines, and a database stored on a network drive.

The first machine has office 2003.

The second machine has office 2007.

We have a database that is in .mdb access 97 format that we are trying to convert to 2007 format. However the switchboard will not work when we convert it. I am getting a Runtime 13 Error.

I have tried converting the 97 format to 2000-2003 format, and it works just fine, however when we try to convert to the 2007 format, we keep getting the error. It doesn't matter if we convert it from 97 to 2003 then to 2007 or try and convert it from 97 straight to 2007.

Anything I am doing wrong or is there anything I can do?

When I move to the 2007 machine I can give the code that has the error in it.

Thanks..



also the 97 version is 33 mb but when i convert it drops to about 8mb? is there an issue here too?

 

[georgedwilkinson]

You might try deleting all the switchboard stuff and re-running the switchboard wizard. This would mean re-creating your entire switchboard.

My guess, based off of a post last week, is that they've made major changes to the switchboard for 2007.

 

[Databases]

here is the debug version: bolded, underlined, and redded the highlighted part in the debugger.

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.

' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True


End Sub

Private Sub Form_Current()
' Update the caption and fill in the list of options.

If Me!ItemText = "Main Menu" Then
Me.MenuTitle.Picture = "H:\Student_Emp\Jobs Database\images\main.bmp"
ElseIf Me!ItemText = "Edit" Then
Me.MenuTitle.Picture = "H:\Student_Emp\Jobs Database\images\editmenu.bmp"
ElseIf Me!ItemText = "Reports" Then
Me.MenuTitle.Picture = "H:\Student_Emp\Jobs Database\images\reports.bmp"
ElseIf Me!ItemText = "Administration" Then
Me.MenuTitle.Picture = "H:\Student_Emp\Jobs Database\images\admin.bmp"
ElseIf Me!ItemText = "Listing" Then
Me.MenuTitle.Picture = "H:\Student_Emp\Jobs Database\images\listing.bmp"
ElseIf Me!ItemText = "Vacancy Analysis" Then
Me.MenuTitle.Picture = "H:\Student_Emp\Jobs Database\images\vacancy.bmp"
End If

Me.Caption = Nz(Me![ItemText], "")
FillOptions

End Sub

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

' The number of buttons on the form.
Const conNumButtons = 8

Dim dbs As Database
Dim rst As Recordset
Dim strSQL 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 dbs = CurrentDb()
strSQL = "SELECT * FROM [Switchboard Items]"
strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
strSQL = strSQL & " ORDER BY [ItemNumber];"
Set rst = dbs.OpenRecordset(strSQL)

' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rst.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else
While (Not (rst.EOF))
Me("Option" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
rst.MoveNext
Wend
End If

' Close the recordset and the database.
rst.Close
dbs.Close

End Sub

Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.

' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8

' An error that is special cased.
Const conErrDoCmdCancelled = 2501

Dim dbs As Database
Dim rst As Recordset

On Error GoTo HandleButtonClick_Err

' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset)
rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn

' If no item matches, report the error and exit the function.
If (rst.NoMatch) Then
MsgBox "There was an error reading the Switchboard Items table."
rst.Close
dbs.Close
Exit Function
End If

Select Case rst![Command]

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst![Argument]

' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rst![Argument], , , , acAdd

' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rst![Argument]

' Open a report.
Case conCmdOpenReport
If rst![Argument] = "Job Order Form" Then
DoCmd.OpenReport rst![Argument], acPreview, "Open Position Query"
Else
DoCmd.OpenReport rst![Argument], acPreview
End If

' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "WZMAIN80.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions

' Exit the application.
Case conCmdExitApplication
DoCmd.Quit

' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rst![Argument]

' Run code.
Case conCmdRunCode
Application.Run rst![Argument]

' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."

End Select

' Close the recordset and the database.
rst.Close
dbs.Close

HandleButtonClick_Exit:
Exit Function

HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If

End Function

 

[georgedwilkinson]

Change this:

Set rst = dbs.OpenRecordset(strSQL)

To this:

Debug.Print strSQL
MsgBox (strSQL)
[COLOR=Red][U][I][B]Set rst = dbs.OpenRecordset(strSQL)[/B][/I][/U][/COLOR]

Put a breakpoint on the line "Debug.Print strSQL", and when the break occurs, hit F8 and look in the Immediate window (usually under the code). There will be a string of text that starts with "SELECT". Copy that entire string and paste it back here.

While you have it copied to the clipboard, open a query editor window and select the SQL syntax view. Paste the clipboard to the SQL window and run it. What happens?

With those 2 pieces of information, you're a little bit closer to the answer.

 

[Databases]

did what you said, and got this:

http://img69.imageshack.us/img69/8128/breakpoint.png

 

[georgedwilkinson]

That "looks" normal. What did you get in the query window?

You'll need to compare those results with the actual data/fields in the table [Switchboard Items]. If all is well there, I may have sent you on a wild goose chase (but at least you'll know what's NOT wrong).

 

[Databases]

bah... ty....

 

[georgedwilkinson]

bah... ty....

What happened? Did you figure it out? If so, what was it? If not, what do you still need help with?

 

[gemma-the-husky]

i have no problems with converted switchboards in A2007

if its failiing on that line maybe you don't have Data Access Objects set as a reference. have a look at that. (or maybe you have ActiveX Data Objects saet as a reference ABOVE the DAO reference

the other thing is that the code you are runnnig at the top of the current event doesn't look like standard - so I assume you have changed trhe switchboard code - although from the look of it, it shouldnt give you any errors

 

[boblarson]

By the way, this database should be split with a copy of the frontend on EACH user's machine and not being run from a shared file on the network. Having a copy of the frontend on each machine should help immensely (as well as protect against corruption).

 

[Databases]

yeah, i don't get to make the rules - i just do what they ask, I am a student aide here at school and it is a job for my school.

Well, is there a reason it is going from 33 mbs in 97 format or 2003 format, to 8 mb in 2007 format? hopefully i am having a conversion error and the switchboard will work fine if i figure out how to convert correctly...


i hope...

 

[Databases]

problem is, in 97 it works fine, if i convert 97 to 2003 it also works fine!!!

when i convert to 2007 from either the 97 version or the 2003 version, it breaks. i am assuming it is a part of the conversion problem?

 

[boblarson]

ALSO, make sure to change these:

Dim dbs As Database
Dim rst As Recordset

to this:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

 

[Khalid_Afridi]

ALSO, make sure to change these:

Dim dbs As Database
Dim rst As Recordset

to this:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

you are right boblarson, it's usually good way to tell the database which engine should control it.

DAO object is used for access default database and ADO recordset is used to connect to SQL server database; referencing only a 'Database' is confusing the database engine and is not a good technique.

 

[boblarson]

you are right boblarson, it's usually good way to tell the database which engine should control it.

DAO object is used for access default database and ADO recordset is used to connect to SQL server database; referencing only a 'Database' is confusing the database engine and is not a good technique.

Well, and actually we want to do it because in 97 DAO was the default, in 2003 both ADO and DAO were checked in the references by default and in 2007 DAO (but as ACE) it is the default. And if you have ADO being used anywhere then you need to disambiguate. It is good to do that always anyway, but very important if both are being used. Of course it was not available in 97 but if any changes had been made to the database while in 2000, 2002, or 2003 and ADO was added, it would be a problem.

 

[Databases]

well...

















































































IT WORKED! THANKS!

 

[boblarson]

IT WORKED! THANKS!

Glad we could help