Access can't find field, so it doesn't convert

[Legacy 386498]

Hi all,

I'm currently converting Access 97 to 2010, every Access converted thus far went smoothly, but Access can't convert one of them saying: Impossible to find the "Description" field.

So I went and look at every table that has a description field and found 6. How to know which one of them is the problem and how to know what the problem is related to? Access doesn't give me a lot of info to go on.

The Access database contains multiple tables, queries, reports, forms, modules and macro.

Thanks in advance.

 

[Legacy 386498]

Well, this is interesting...
First I checked all the macros as that seemed easiest. When I tried to view queries in design view it wouldn't let me, not that I would have wanted to search every one for a single word. More on that in a moment. Then I tried to output the recordsource of the queries to the documenter and it wouldn't let me because the version was too old.

So I wrote a sub in another db to connect to your db and delve into the sql of each of the queries and look for "description " and it found none. Then I modified the search to find "description" and it found lots. I copied and pasted all into a Word document and did a search for "description" and it stopped at every "description_perte" except there was one that looked like

....[T-code_perte_temps_emballage].description
GROUP BY...

So why didn't Word or my code find that instance of "description"? Because it is not - it is "descriptionGROUPBY" with a line wrap in between. I'm surprised this query runs at all because I do not think that Access 2016 would let that slip by, but perhaps that is only true for vba code and not Access sql. If I could alter the design and save it, I would open that query in sql view, insert a space before GROUP BY and test it but I am not allowed to edit the design. You will have to try that.

I wasn't able to convert the database after inserting a space before GROUP BY.

 

[Micron]

I can't open either of those 2 queries as they are reported as being corrupt, however, if they have anything to do with a report named e_tbord_test then it/they may not be.
Make a copy of your db.
go to start>run and enter msaccess /decompile
access should open; right click on this new copy, hold shift key and select open from the shortcut menu you just opened
db should open - do nothing except close access completely
reopen same db (EDIT -SHIFT CLICK AGAIN. DO NOT LET ANY CODE RUN), double click on a module and in the vb editor, click 'compile' and you'll start getting errors. First one is likely
Member already exists in an object module from which this object module derives click OK and the offending code should be highlighted (Dim total53) and the report I mentioned above should be highlighted in the objects pane. If you do ctrl+F (find) and look for the highlighted code you'll find that it's used in several places, some of them incorrectly. Mainly though, the error likely means that there is also an object in the report by the same name. You cannot have an object on a form/report that has the same name as a variable you declare.

There is no gentle way to tell you that your db is a complete mess and you should probably start over. Whoever rebuilds it must not do the same thing as what you have here. There are FAR too many objects that I can only surmise are almost the same because their names are very similar. Especially tables! As many as 6 tables for one thing, having the same fields but the table name has a different number at the end.

I'm afraid I can spend no more time on this, but I wish you luck in solving your issues.