Error in 2007 but not in 2003 - suggestions please

[Ron2006]

Here is the scenario.

An application runs just fine in 2003 but I am getting two different
types of errors in the when trying to do things in the same mdb in
2007.

Error 1:
- multiple tabs on a form
- each tab has independent subforms and query
- The subform record source is empty until the tab is touched at which
time it is loaded IF the source was empty (meaing the tab had NOT been
touched before)
- If I touch tab 1 - source is loaded - query executes correctly -
records appear
- then if I touch any other tab I get an error message indicating that
the query cannot find tablename.fieldname asking for a value.
- If I don't cancel it, the query executes but I get this message
twice.
- If I then touch tab 1 again, the same combination of errormessages
now appear.
- This occurs no matter which tab is the first tab touched and no
matter which tab is the second one touched.
- THE FIELD THAT IS REFERENCED IS NOT IN ANY OF THE QUERIES INVOLVED
IN ANY OF THE SUBFORMS.

Related Error 2:
- If I enter the DB in design mode and go to any of the queries
involved in these tabs the following happens:
- If I execute the query, it runs properly.
- If I then right click and ask for SQL view of this query, I can see
it.
- If I however right click it and ask for the design view I get the
following error message:
'' is not a valid name. Make sure that it does not include invalid
characters or punctuation and
that it is not too long.
- After I press the OK button, the message disappears BUT the the
design view for the query DOES NOT appear.

I have updated Office 2007 to include all of the latest updates.

The same mdb operates properly with no errors at all when executed
under Access 2003.

Ron

 

[Sab64]

Ron2006,

i had the same problem when i upgraded from 2003 to 2007 using SQL Server
2000 as a backend db. on the forms that i was getting an error on (record
not found in this query) i had to create and save the forms query in the
query section (i used a SQL statement in the record source originally, then
changed it to the query name), that seemed to fix the problem.

 

[Ron2006]

Sab64,

Thank you for responding. I will keep that in mind, although in this
case I had queries already and they ran.

However, my next posting, I think points to what may have been the
cause, and Maybe if attaching a new query to the form would have
cleared the possible cause.

Please see my next posting to see what I found.

Ron

 

[Ron2006]

Here is what I found and possibly another solution to the problem.

1) Things that I saw in in 2003.
It seemed to often happen that when I was in a form, and went to
the data tab on the properties window, and went to the query wizard,
when I returned to the form Access would place a field name in the
"Order By" property on that data tab. Sometimes I would go down and
empty that field because typically it was not the real sequence that I
requested in the query. Very often I did NOT erase the field name. I
really never looked at the field names much since it did not seem to
affect how the query returned the information when actually used in
the form.

2) In this case, last night, when I was looking at the mdb/form in
2003, low and behold, I saw the name of the field that was being
requested (one that was NOT in the query itself) was sitting in that
field on a couple of the queries.

3) I went through ALL of the forms used on those tabs and erased that
(and any other) field name that was in that property.

4) I then went back to 2007, and miracle of miracles, everything ran
fine. I no longer get the error messages AND I can get into the
Designer view of the Query.

So Far, the problem has not affected any form/query that is NOT a part
of a tabbed form, BUT I have not reviewed all of the forms yet.

Doing as Sab64 suggested may indeed, be effectively doing the same
thing. An anomaly in 2003 affecting execution in 2007, being resolved
(cleared) when doing the operation in 2007 that does not have that
particular anomaly.

WHY this can cause a problem is beyond me, but at least we may have a
way around it.

Ron

 

[Ron2006]

I found this as a possible solution (in microsoft Groups) {I am having
this problem and will try the answer when I get a chance)

=========================

Hi Lindsay,

If it is a 2003 or earlier database and your have Access 2003 or
earlier, you can relink the tables using the earlier version.

Alternatively, to fix it in Access 2007 do something like this:

Public Sub DealWithApostrophes()

Dim dbCurrent As DAO.Database

Set dbCurrent = CurrentDb
With dbCurrent.TableDefs("tblJohn's Table")
..Connect = Replace(.Connect, "'", "")
..RefreshLink
End With
Set dbCurrent = Nothing

End Sub

Where "tblJohn's Table" is a table in a database with a name with an
apostrophe such as "C:\John's Data\John's Data.accdb". Fix the entire
path
of the database so that there are no apostrophes anywhere. Note that
the
table name can continue to have apostrophes. Then with the above code
in a
regular (not class, form or report) module, place your cursor anywhere
inside
of the code and press F5 to run it.

Good Luck!

Clifford Bass
=======================
Ron