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.

 

[Micron]

If this is a 97 version as your first post suggests, I will not be able to open it. I just remembered that, sorry. I have an old laptop that would do it, but its battery is kaput. Got an old IBM Thinkpad battery lying around?

I just thought of someone that might have an older version that will open it.

 

[Legacy 386498]

You need a pwd in order to download???

I just sent you a PM.

If this is a 97 version as your first post suggests, I will not be able to open it. I just remembered that, sorry. I have an old laptop that would do it, but its battery is kaput. Got an old IBM Thinkpad battery lying around?

I just thought of someone that might have an older version that will open it.

I don't have any old battery lying around sorry. Keep me updated if the other person you have in mind can open/solve it.

 

[jackd]

I downloaded and got you can't open the file-- it was made with a previous version of Access ( or similar wording).
Sorry.

 

[Legacy 386498]

I downloaded and got you can't open the file-- it was made with a previous version of Access ( or similar wording).
Sorry.

Yes, you need Access 97 to open it.

 

[Micron]

Turns out my issue isn't battery related but loose or defective RAM. I will see what I can do to find the issue.

 

[Micron]

The problem was coming from a query who's link to three tables, but there isn't a field name Description between those three tables.

which query was that?

 

[Micron]

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.

 

[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 don't understand why you can't go in design view, weird. Are you opening it with Access 97 or 2007-2010? I can see in design view in 97, but not 2010 on my end. I can shoot you the documenter for every queries or any other objects if you want, I already saved them, because that's one of the thing you suggested me to do first to look for "description". I could also try reuploading the database so you can make modifications on your end.

Do you have the name of the query? The one you gave me is a table (T-code...). I'll open it in SQL view tomorrow and try your suggestion.

 

[Micron]

Because I don't have a version old enough. Old enough to open, not old enough to alter design without converting. Can't convert because....well, you know why.

I think it is R-std_employe_perteT_emballage_P2_final

I'm not confident that this will be the problem because the query runs anyway. I've never seen so many objects in one db. I don't speak French at all really, but I think I can grasp enough that it sure looks like there's a lot of unnecessary duplication in everything, and that's adding a lot to the high degree of difficulty here.

Since I last wrote I've been working on a way to loop over controls in forms to check their recordsource but it's quite complicated and exacerbated by the sheer number of objects. After form controls would be done, if the issue isn't found, then what? Reports? If you don't know what I mean by that, then consider that a form control has a recordsource. If that recordsource is a little query that's only native to the control (such as a combo or listbox list) you won't find it in any query. If it's an expression like =DLookup("description", "FieldNameHere") you'll never find it in a query either. Nor will you if it's on a report or form where the control =[someField] & "," & [description].

I've been at this for about 2 1/2 hours now (mostly trying to loop over forms and their controls) and I'm not sure how much time I can devote to this. It would be different if this were my db and my job, but there is only so much one can do with his free time. You might have to methodically delete (in A DB COPY) say, 10 forms at a time and note which ones you did in a batch, then attempt to convert. Keep going until it works. If never, then continue with reports, same idea. If it never works, then forms, attempting to convert after each batch. Once it works (if ever) you will only know that it is one of the last 10, then will have to start over with a new copy and delete 1,2 or whatever you like, testing as you go until you narrow this down to one. Then you know what to look in, but I'm not sure you know what to look for.

Having said all that, I recall you said it would convert after removing one query and I asked which one that was so that I could try it, but I don't recall getting an answer. See post 26...

 

[Legacy 386498]

Because I don't have a version old enough. Old enough to open, not old enough to alter design without converting. Can't convert because....well, you know why.

I think it is R-std_employe_perteT_emballage_P2_final

I'm not confident that this will be the problem because the query runs anyway. I've never seen so many objects in one db. I don't speak French at all really, but I think I can grasp enough that it sure looks like there's a lot of unnecessary duplication in everything, and that's adding a lot to the high degree of difficulty here.

Since I last wrote I've been working on a way to loop over controls in forms to check their recordsource but it's quite complicated and exacerbated by the sheer number of objects. After form controls would be done, if the issue isn't found, then what? Reports? If you don't know what I mean by that, then consider that a form control has a recordsource. If that recordsource is a little query that's only native to the control (such as a combo or listbox list) you won't find it in any query. If it's an expression like =DLookup("description", "FieldNameHere") you'll never find it in a query either. Nor will you if it's on a report or form where the control =[someField] & "," & [description].

I've been at this for about 2 1/2 hours now (mostly trying to loop over forms and their controls) and I'm not sure how much time I can devote to this. It would be different if this were my db and my job, but there is only so much one can do with his free time. You might have to methodically delete (in A DB COPY) say, 10 forms at a time and note which ones you did in a batch, then attempt to convert. Keep going until it works. If never, then continue with reports, same idea. If it never works, then forms, attempting to convert after each batch. Once it works (if ever) you will only know that it is one of the last 10, then will have to start over with a new copy and delete 1,2 or whatever you like, testing as you go until you narrow this down to one. Then you know what to look in, but I'm not sure you know what to look for.

Having said all that, I recall you said it would convert after removing one query and I asked which one that was so that I could try it, but I don't recall getting an answer. See post 26...

The two queries I need to delete to successully convert the database are R-Rapport_appro and R-Rapport_appro_girau, but I don't understand why. It was only R-Rapport_appro_girau I needed to delete the first time, but after I needed to delete these two, don't know what changed.