Access 2003 truncates code in queries

[Guest]

I'm trying to create queries in Access 2003. Every time I save my code,
Access truncates the code and destroys my query (and as a result, when I try
to run the query, it fails with an error message). Then I open it back up in
design mode and I see that it truncated the query when I saved it. I'm not
talking about it truncating the code when I try to run it. It's truncates it
during a save. And there isn't much code there. They are SQL queries that I
create by going into design mode and then switching to SQL view. I'm thinking
that there must be some sort of bug in Access. I did somehow manage to get
just one query to stay together without being destroyed. But I've tried doing
a copy and paste of my code to a new query and when I save the new query,
Access truncates it. When I tried to just highlight my one good query and
right-click on it and do a Save As, when it saves the new query, it truncates
the code again. This is causing me serious problems and is keeping me from
getting my job done. Below is the code of my one good query....that I can't
duplicate without Access truncating the code again. I really need some help.

SELECT RN, COUNT(1) AS Cnt
FROM [SELECT DISTINCT LCase(RuleName) AS RN
FROM admin

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM dmz2

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM dmz

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM dmzadmin

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM external

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM foreign

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM internaldmznetmaps

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM loadbalancer

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM outbound

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM outboundcustom

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM vpn

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM wwan]. AS Selected_Recs
GROUP BY Selected_Recs.RN;

 

[Guest]

Smells like a corruption issue.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

If your query is indicative, I'd also guess that you have a normalization
problem with all those Union queries. Seems rather excessive; however, I
don't think that's causing the truncation issue as you haven't reached the
max number of tables or characters.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm trying to create queries in Access 2003. Every time I save my code,
Access truncates the code and destroys my query (and as a result, when I try
to run the query, it fails with an error message). Then I open it back up in
design mode and I see that it truncated the query when I saved it. I'm not
talking about it truncating the code when I try to run it. It's truncates it
during a save. And there isn't much code there. They are SQL queries that I
create by going into design mode and then switching to SQL view. I'm thinking
that there must be some sort of bug in Access. I did somehow manage to get
just one query to stay together without being destroyed. But I've tried doing
a copy and paste of my code to a new query and when I save the new query,
Access truncates it. When I tried to just highlight my one good query and
right-click on it and do a Save As, when it saves the new query, it truncates
the code again. This is causing me serious problems and is keeping me from
getting my job done. Below is the code of my one good query....that I can't
duplicate without Access truncating the code again. I really need some help.

SELECT RN, COUNT(1) AS Cnt
FROM [SELECT DISTINCT LCase(RuleName) AS RN
FROM admin

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM dmz2

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM dmz

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM dmzadmin

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM external

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM foreign

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM internaldmznetmaps

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM loadbalancer

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM outbound

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM outboundcustom

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM vpn

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM wwan]. AS Selected_Recs
GROUP BY Selected_Recs.RN;

 

[Jamie Collins]

SELECT RN, COUNT(1) AS Cnt
FROM [<subquery snipped>]. AS Selected_Recs
GROUP BY Selected_Recs.RN;

Just a stab in the dark but shouldn't your subquery be in (parens)
rather than [square brackets]? Also, I would expect the period/stop
immediately following the closing bracket to cause an error.

Jamie.

--

 

[John Spencer]

You could try nested (stacked) queries.

Create your UNION query as a separate named query and then use the named
query in your aggregate query.

You could also try shortening the Union query itself by removing unneeded
spaces and text. Since Access is not case sensitive, you don't need to use
LCase and in a Union query you only need to name the first field. Actually
in this case, you don't even need the alias. Dropping the LCase call should
also speed this up a bit - may not be noticed with a small set of records.

I'm guessing that Access is choking on the size of the subquery's string.
Access ***may*** be limited to either 1K or 2K characters in the subquery
string. You could try the following version of your code and see if this
also gets truncated when you are doing the save.

SELECT RuleName, COUNT(1) AS Cnt
FROM

[SELECT DISTINCT RuleName
FROM admin
UNION ALL
SELECT DISTINCT RuleName
FROM dmz2
UNION ALL
SELECT DISTINCT RuleName
FROM dmz
UNION ALL
SELECT DISTINCT RuleName
FROM dmzadmin
UNION ALL
SELECT DISTINCT RuleName
FROM external
UNION ALL
SELECT DISTINCT RuleName
FROM foreign
UNION ALL
SELECT DISTINCT RuleName
FROM internaldmznetmaps
UNION ALL
SELECT DISTINCT RuleName
FROM loadbalancer
UNION ALL
SELECT DISTINCT RuleName
FROM outbound
UNION ALL
SELECT DISTINCT RuleName
FROM outboundcustom
UNION ALL
SELECT DISTINCT RuleName
FROM vpn
UNION ALL
SELECT DISTINCT RuleName
FROM wwan]. AS Selected_Recs
GROUP BY Selected_Recs.RN;

I'm trying to create queries in Access 2003. Every time I save my code,
Access truncates the code and destroys my query (and as a result, when I
try
to run the query, it fails with an error message). Then I open it back up
in
design mode and I see that it truncated the query when I saved it. I'm not
talking about it truncating the code when I try to run it. It's truncates
it
during a save. And there isn't much code there. They are SQL queries that
I
create by going into design mode and then switching to SQL view. I'm
thinking
that there must be some sort of bug in Access. I did somehow manage to get
just one query to stay together without being destroyed. But I've tried
doing
a copy and paste of my code to a new query and when I save the new query,
Access truncates it. When I tried to just highlight my one good query and
right-click on it and do a Save As, when it saves the new query, it
truncates
the code again. This is causing me serious problems and is keeping me from
getting my job done. Below is the code of my one good query....that I
can't
duplicate without Access truncating the code again. I really need some
help.

SELECT RN, COUNT(1) AS Cnt
FROM [SELECT DISTINCT LCase(RuleName) AS RN
FROM admin

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM dmz2

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM dmz

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM dmzadmin

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM external

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM foreign

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM internaldmznetmaps

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM loadbalancer

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM outbound

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM outboundcustom

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM vpn

UNION ALL

SELECT DISTINCT LCase(RuleName) AS RN
FROM wwan]. AS Selected_Recs
GROUP BY Selected_Recs.RN;