Switchboard malfunctions after action queries opened

[NES]

I've set up a DB in Access 2003 that works fine in all respects. Moving it to
Access 2007 shows an odd problem. A sub menu (switchboard) selections that
initiate action queries (append, update) suddenly are unable to function
properly with the two bottom most menu selections. They are simply dead. One
is supposed to simply display a form that shows contact information. The
other selection returns to the previous (main) menu (switchboard). Prior to
running the queries these work fine. In Access 2003 there are not problems.

Are there any reports of a bug of this nature in 2007?

 

[Allen Browne]

First thing would be to see whether your database is in a trusted location.
If not, you can add it through:
* Office Button (top left)
* Access Options (bottom of dialog)
* Trust Center (left)
* Trust Center Settings (button)
* Trusted Locations (left)
* Add new location (button)

More info about adapting to A2007:
http://allenbrowne.com/Access2007.html

 

[NES]

Thanks Allen. Yes, I just double checked, and my system has been added. In my
original message I incorrectly stated that one of the menu selections display
a form. Actuall, both of these bottom menu selections move you to other
switchboards -- the last being to the default switchboard.

It only happens AFTER running action queries. Prior to that they work fine.
--
Norm Shimmel
Butler, PA

First thing would be to see whether your database is in a trusted location.
If not, you can add it through:
* Office Button (top left)
* Access Options (bottom of dialog)
* Trust Center (left)
* Trust Center Settings (button)
* Trusted Locations (left)
* Add new location (button)

More info about adapting to A2007:
http://allenbrowne.com/Access2007.html

 

[Allen Browne]

How are the action queries run?

RunSQL? OpenQuery?
Execute? DAO or ADO?

What do the queries do?
Are DDL (creating/modifying/dropping tables/indexes/columns)?
Append? Delete? Insert?

 

[NES]

One of the queries runs a simple update to a table, reducing inventory by on
one or more items. It's run by a macro that:
Runs the update query
Opens the table that held the temporary update values
Selects all records
Deletes them
Closes the table.

The other macro is more elaborate and extensive:
Repeats the operations shown above with an update query from a different
table.
Repeats the operations shown above with an append query from a different
table
Opens one other table that held temporary data for another but associated
macro.
Deletes the data
Closes that table.

 

[NES]

An addendum to my previous note. In the process of trying to isolate the
problem, I entered the Switchboard Manager (in 2007) and moved the two
non-functioning menu items nearer the top. Having done that, the entire
program froze. So I closed it and reoped it in version 2003. The changes I
made to the switchboard showed up, and the applications worked normally as it
always does in version 2003.

I do believe the problem lies with the switchboard manager, but then, I'm no
the expert here -- by any means. <s>

 

[Allen Browne]

Okay, I've no idea what's gone wrong during that process, but if you are
looking for a workaround, you might be able to just Execute a couple of SQL
statements instead of opening tables and so on.

The core idea is to create a SQL string that does what you need, and execute
it. This example deletes all records from Table1:
Dim db As DAO.Database
Dim strSql As String
Set db = CurrentDb()
strSql = "DELETE FROM Table1;"
db.Execute strSql, dbFailOnError
Set db = Nothing

This example subtracts 1 from the Qty field in Table2, for Product 99:
strSql = "UPDATE Table2 SET Qty = Qty - 1 WHERE Product = 99;"
(The other lines are the same as above.)

To get the SQL statement you need, mock up a query, and switch it to SQL
View (View menu, in query design.)

For more info on Execute, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Allen Browne]

The other possibility is that there is a problem with the VBA binary. This
can happen, especially when switching back and forth between versions.

A decompile might fix it, but try this sequence in order:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

7. Still in the code window, choose Options on the Tools menu. On the
General tab, make sure Error Trapping is set to:
Break on Unhandled Errors
and the Compile on Demand is unchecked.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
the code syntax is compilable, and the VBA options are set to show errors
and avoid this kind of corruption.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

 

[NES]

Allen, I've never been successful in DOS with using the quotes in path names.
No matter where I put the quotes, it returns an error message. I tried again
with your suggestion, replacing the last part of your command with the path
to my DB (I had set it on the desktop for this) and all I get are DOS
messages telling me that these are not valid commands, etc. When I'm at the
C:\ prompt, I can't even move to the Program files directory with CD
C:\"Program files". Instead I get an error message.

 

[Allen Browne]

Oh: so it's a matter of how to refer to the paths correctly?

The quotes go around the entire name, e.g.:
"C:\Program Files\Some Folder\Some File.mdb"

The way to check you have it correctly is to open the Immediate Window
(Ctrl+G) and use Dir() to check if the file exits, e.g.:
? Dir("C:\Program Files\Some Folder\Some File.mdb")
If you get the name right, that returns the file name.
If you get the path wrong, you get an error message.
If you get no response at all, the file was not found.

I don't recall if you said this was on Vista, but some of these folders
(like Program Files) are virtualized.

 

[NES]

Allen,
I finally got it to do the decompile and followed your instructions. As
before, the DB works in 2003. However, when I look at the primary form in
design view, I see that all my dropdown boxes have flagged errors - No such
control source exists in the field list (I believe I've quoted that
correctly). But staring me in the face is the field list and they are there.

I'm about ready to scrap this DB and the several hundred hours I have in it.
I don't know what has caused the problem. But all of my backups were made
after the problem surfaced. As it stands, it's unuseable in version 2007.

Thanks for all your help. The clinic will have to look elsewhere.

 

[NES]

Allen,
Thanks for all your help on this. I finally went in with Version 2003, and
"corrected" all of the Contol Source field that it thought were wrong, and
were not. It's working find under that version.

Verions 2007 stops dead at the opening switchboard. Every button give the
same message.."The expression O CLick you entered as the event property
setting produced the following error: The expression you entered has a
function name that Medications Database can't find."

Even the Exit button on the switchboard.

When I go to the primary screen, I find that Several buttons and a search
box won't work. Not even the exit button there works.

I'm giving up with the option of having this work with Access 2007. I don't
think the problem is solvable because the error message is vague. I don't
know what to do beyond this, nor do I know how to explain this to the people
I'm doing it for.

Anyway, it draws to a conclusion. Thank you so very very much for your time
and help.