ERROR 3125 when trying to enter design view of a query in 2007

[Ron2006]

I am getting the above error when trying to edit (go to design view)
of an update query in 2007.After I press the OK button nothing happens
and I am unable to get to either the desing view or the SQL view.

"This is not a valid name. Make sure it is a valid parameter or alias
name, that it does not include invalid characters or punctuation, and
that the name is not too long. (Error 3125)"

The above explanation is what is described when I press the "Help"
part of the error. I have seen this also on a query related to a form.
After I made a change in the orderby property (which was not really
desired but had been loaded upon return from the query design accessed
via the form) it ran properly. ( I was able to do this ONLY in
2003.....) In 2007 I was never able to get to the design view.

However the one I have having the problem with now is an update query
and so therefore executing it in any form in 2007 is out of the
question, until I can see what it is complaining about.

!@#$%^&*!@#$%^&*

Any reasonable suggestion would be appreciated.

Ron

 

[Ron2006]

Here is the SQL for the above mentioned query when I go to 2003 and
successfully open the query in design view and then switch to SQL
view.

UPDATE [AMExclusion Table] INNER JOIN [FUTURE DUE ORDERS] ON
[AMExclusion Table].AMName = [FUTURE DUE ORDERS].AM SET [FUTURE DUE
ORDERS].CloseFlag = True, [FUTURE DUE ORDERS].CloseDate = Date(),
[FUTURE DUE ORDERS].ClosedBy = "ZZZ - Close PVC"
WHERE ((([AMExclusion Table].FDDropPVC)=True) AND ((Left(Trim([Future
Due Orders]![Circuitid]),2))="EQ")) OR ((([AMExclusion
Table].FDDropPVC)=True) AND ((Left(Trim([Future Due Orders]!
[Circuitid]),4))="21EQ"));

 

[John Spencer MVP]

You might try eliminating the VBA functions and also using . instead of ! to
separate the table names from the field names.

UPDATE [AMExclusion Table] INNER JOIN [FUTURE DUE ORDERS]
ON [AMExclusion Table].AMName = [FUTURE DUE ORDERS].AM
SET [FUTURE DUE ORDERS].CloseFlag = True
, [FUTURE DUE ORDERS].CloseDate = Date()
, [FUTURE DUE ORDERS].ClosedBy = "ZZZ - Close PVC"
WHERE ([AMExclusion Table].FDDropPVC=True
AND [Future Due Orders].[Circuitid] LIKE "EQ*")
OR ([AMExclusion Table].FDDropPVC=True
AND [Future Due Orders].[Circuitid] LIKE "21EQ*")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ron2006]

I will try that.

However the query was generated by the query wizard in 2003. I did not
modify it.

The frustrating part of this is that I HAVE to use 2003 to change it
since I cannot even get to the query in 2007.

Ron

 

[Ron2006]

I did the following:

1) Went into 2003 and changed the view to SQL.

2) Changed the query to be the following:

UPDATE [AMExclusion Table] INNER JOIN [FUTURE DUE ORDERS] ON
[AMExclusion Table].AMName = [FUTURE DUE ORDERS].AM SET [FUTURE DUE
ORDERS].CloseFlag = True, [FUTURE DUE ORDERS].CloseDate = Date(),
[FUTURE DUE ORDERS].ClosedBy = "ZZZ - Close PVC"
WHERE ((([AMExclusion Table].FDDropPVC)=True) AND ((Left(Trim([Future
Due Orders].[Circuitid]),2))="EQ")) OR ((([AMExclusion
Table].FDDropPVC)=True) AND ((Left(Trim([Future Due Orders].
[Circuitid]),4))="21EQ"));

3) saved and closed the query

4) Went to 2007 in design view for the mdb file.

5) Selecting that query I right clicked it and said to go to design.
It opened in SQL view.

6) I right clicked on the top bar of that window and asked to go to
design view.

7) I GOT THE SAME ERROR MESSAGE AND WAS NOT ABLE TO SEE THE QUERY IN
DESIGN VIEW.

@#$%^&*(@#$%^&*

Ron

 

[Ron2006]

To fellow seachers, I found this and will try it when I get a chance. It was
in the Microsoft groups.

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

 

[Ron2006]

I found this possible solution in the Microsoft Groups and will try it
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