The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

MS Access query corruption

I have been working with MS Access for a while now to design and build an information system and I am getting fed up with Access corrupting my SQL-queries all the time.

Simple, straight-forward queries are no problem,
but I have a few queries with one or more UNIONs that keep getting messed up.

When I type the query and save it, I can open it and it works fine. But when I then reopen it for modification, the () surrounding the SELECT from the UNIONs have been changed to []. The query no longer works of course and I have to change the [] back to ().

But now it's even worse, since it seems to cut part of the statement all together. From the middle, near the end, part of the query goes missing.

Another query just can't be opened at all. Not even for editing, as Access complains there is something wrong with the FROM part.

Anyway, just venting. But if you happen to have a solution that does not involve dumping Access, I am all ears...
Practical Geezer
Thursday, February 17, 2005
Never had that problem before although haven't done much with 2003 if that is the version you are using.

Access does seem to want to parse the stored query for display even when it can't represent it any other way than plain text (no designer).

Try this:
Save each seperate select statement as its own query then do a join of the seperate queries. That should give it less to get confused about.

Select * from CustomersWhoByFish
Select * from CustomersWhoByChips
Thursday, February 17, 2005
Access was not made for this, so you're probably hitting the upper level of the logic that it can handle.

If at all possible, you might consider switching to another database.
KC Send private email
Thursday, February 17, 2005

It's not so much that Access was not designed for this, because it can execute the query perfectly well. It just can't seem to remember it.


Taking the query apart seems to work. I have put the unions in one query and then join with another table in a second query. So far, so good...

BTW, below is the query that Access can't seem to remember. It's the subquery to the right of the inner join that got messed up. Most of the time "(" becomes "[" and ") AS" becomes "]. AS". Note how the dot does not make any sense at all.

SELECT VerrichteTaken.BoekingId, VerrichteTaken.DatumGedaan, VerrichteTaken.SoortTaak, Taaksoorten.SoortHerinnering, Taaksoorten.Presentatietekst
FROM Taaksoorten


SELECT BoekingId, Verzenddatum as DatumGedaan, 12 AS SoortTaak
FROM [Verzonden overeenkomsten]


SELECT BoekingId, Verzenddatum, 10
FROM [Verzonden deelnemerslijsten]


SELECT BoekingId, Verzenddatum, 11
FROM [Verzonden evaluatieformulieren]


SELECT BoekingId, Verzenddatum, 8
FROM [Verzonden facturen]


SELECT BoekingId, Ontvangstdatum, 6
FROM [Verwerkte deelnemerslijsten]


SELECT BoekingId, Ontvangstdatum, 7
FROM [Verwerkte overeenkomsten]

) AS VerrichteTaken ON Taaksoorten.SoortId = VerrichteTaken.SoortTaak;
Practical Geezer
Thursday, February 17, 2005
I have no idea whether it has anything to do with your problem, but I _hate_ the idea of having spaces in tablenames, fieldnames, or any other kind of name in my db.  This is what creates need for the square brackets in your SQL to begin with.  I assume it would be easier for JET/Access to parse if there were no spaces in names and thus no square brackets in the SQL.  Just a thought, could be wrong.  But those spaces are bad news.
Herbert Sitz Send private email
Thursday, February 17, 2005
Maybe Access doesn't understand Dutch.  Have you considered using English table/column names?

Brian Send private email
Thursday, February 17, 2005
Try running the following in a module window.  You'll need to set a reference to DAO.

Public Sub ViewQueries()

    Dim d As DAO.Database: Set d = CurrentDb()
    Dim q As DAO.QueryDef
    For Each q In d.QueryDefs
        Debug.Print "/*****  " & q.Name & "  *****/"
        Debug.Print q.SQL
        Debug.Print ""
End Sub

Grasping at straws here, but by default Access prefixes each field with the table name, even if there's just one table in the query.  You could try changing it as such: [table name].field.

I'm just wondering whether it's your numeric fields names that are causing the problem.
Cowboy coder
Thursday, February 17, 2005
Try and save the union as a separate query. It seems access will "rearrange" the syntax for its design view to parse correctly.
Thursday, February 17, 2005
SELECT BoekingId, Verzenddatum as DatumGedaan, 12 AS SoortTaak
FROM [Verzonden overeenkomsten]


SELECT BoekingId, Verzenddatum, 10
FROM [Verzonden deelnemerslijsten]

Shouldn't that be

SELECT BoekingId, Verzenddatum, 10 AS SoortTaak
FROM [Verzonden deelnemerslijsten]

I've always had problems with union queries when I left off the alias for fields that would normally require them as standalone queries.
Ron Porter Send private email
Thursday, February 17, 2005

Actually, according to SQL syntax no, it does not have to be. The field names will be taken from the first query.
But, it might cause Access to choke anyhow. I will investigate...


Rearanging into separate queries has done the trick. Have had no problems sofar. I think Access might get confused trying to determine the type of query.

Cowboy Coder,

Seems unlikely, but I will look into it.

Ja, maar toen ging Access zich ook nog eens met de inhoud bemoeien :-)


Although the spaces do indeed create the need for the square brackets, it does not seem to be the cause. I had the same problems with table names without spaces.

I guess Access is being to smart for its own good here. It surprises me though, that a 2003 product corrupts data the way Access does here.
Practical Geezer
Thursday, February 17, 2005
Oh and Brian,

reading back the query, I noticed this huge spelling error. Do you think Access might have been messing it up out of spite?
Practical Geezer
Thursday, February 17, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz