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.

 

[JonXL]

Can you rename each of them ("Description1", "Description2", etc.) and give the process another go?

 

[Micron]

This could also be where some form or report control has an expression that is incorrect; i.e. it uses TableA.Description but while TableA exists, it no longer contains a field named Description. In that case, you will never solve this by renaming fields. You could try that first, but you'd better be able to put their names back as they were or else nothing that relies on those renamed fields will work anymore, and if you do re-establish those names, you still have a problem somewhere in the converted db

To test my guess, if there are not too many forms/reports you could go looking for such an expression (you will have to be forensic about it) or you could remove objects and try again to convert. Rinse and repeat until it works. Then you know which form/report contains the invalid reference. It could be an expression in a control or a control could be bound to the field that can't be found in the conversion. Regardless of what approach you take, you should only be trying to convert copies, not the only version of a file that you have.

 

[Legacy 386498]

Can you rename each of them ("Description1", "Description2", etc.) and give the process another go?

I tried this first, but it didn't work.

This could also be where some form or report control has an expression that is incorrect; i.e. it uses TableA.Description but while TableA exists, it no longer contains a field named Description. In that case, you will never solve this by renaming fields. You could try that first, but you'd better be able to put their names back as they were or else nothing that relies on those renamed fields will work anymore, and if you do re-establish those names, you still have a problem somewhere in the converted db

To test my guess, if there are not too many forms/reports you could go looking for such an expression (you will have to be forensic about it) or you could remove objects and try again to convert. Rinse and repeat until it works. Then you know which form/report contains the invalid reference. It could be an expression in a control or a control could be bound to the field that can't be found in the conversion. Regardless of what approach you take, you should only be trying to convert copies, not the only version of a file that you have.

I have like a 100 tables, 150 queries, 75 forms, etc. so I deleted every form and tried to convert, did the same with macros, modules and queries. 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. So I'm confused as to why Access is saying: Impossible to find the "Description" field.

Also, is there an easy way to know which field from which table was taking to create a form?

 

[Micron]

but there isn't a field name Description between those three tables

That's more or less what I'm saying - but somewhere, something is calling for it. If it's in that problem query, then either the query reference was mis-typed or at some point after it was created someone altered the field name, or removed it all together.

You could try the db Documenter. Choose options so that you get only what you need otherwise the report will probably be 100 pages for you.
Ribbon>Analyze I'd start with all queries if you don't know which one is the issue, no index info, no field properties + sql.
You can export the document to Word if that helps; at least you could use Ctrl+F to find 'description' if that was the prompt.

Note that if the reference to said field is in a form or report, it could be buried in a calculated control or data macro. IIRC the documenter won't find a word in an expression in a control.

Lastly, if you can't find it, you could post a db copy and I probably will find it. If you're concerned about data privacy there is a data randomizer available.

 

[Legacy 386498]

That's more or less what I'm saying - but somewhere, something is calling for it. If it's in that problem query, then either the query reference was mis-typed or at some point after it was created someone altered the field name, or removed it all together.

You could try the db Documenter. Choose options so that you get only what you need otherwise the report will probably be 100 pages for you.
Ribbon>Analyze I'd start with all queries if you don't know which one is the issue, no index info, no field properties + sql.
You can export the document to Word if that helps; at least you could use Ctrl+F to find 'description' if that was the prompt.

Note that if the reference to said field is in a form or report, it could be buried in a calculated control or data macro. IIRC the documenter won't find a word in an expression in a control.

Lastly, if you can't find it, you could post a db copy and I probably will find it. If you're concerned about data privacy there is a data randomizer available.

I'll try the db Documenter as suggested and try to find the description field. If it doesn't work, how do I use the "data randomizer"? I tried searching on google, but didn't find anything. Thanks.

 

[Micron]

Too hard to provide a link on my phone so try to find it at AccessForums.net. It would be under my same user name or you might find it in the code repository forum.

 

[Legacy 386498]

Too hard to provide a link on my phone so try to find it at AccessForums.net. It would be under my same user name or you might find it in the code repository forum.

I found your post on the other forum. How does it work, it goes in every tables ,queries, etc. and changes their values? How should I run it in my database?

As for the db Documenter, I don't know how to export it in Excel. I mean I'm not sure it works, when I do I see that a file name doc_rtpObjects.xls is being created, but it doesn't tell me where it saves it.

 

[Micron]

Create a new query, switch to sql view, paste in UPDATE tbl1 SET tbl1.field1 = randomizedata(tbl1.field1,3);
change tbl1 to your table name, field1 to your field name (that you want to randomize). The number (3) is the number of leading characters you want to leave alone in case it helps to relate records in one table to another. That query will call the function, which you copy into a standard module and save. You have to do this to every field that you want to randomize. I would avoid doing this on date fields.

Finding files is a basic Windows operation. If you don't know how to search your file system, Google is your friend.

 

[Legacy 386498]

Create a new query, switch to sql view, paste in UPDATE tbl1 SET tbl1.field1 = randomizedata(tbl1.field1,3);
change tbl1 to your table name, field1 to your field name (that you want to randomize). The number (3) is the number of leading characters you want to leave alone in case it helps to relate records in one table to another. That query will call the function, which you copy into a standard module and save. You have to do this to every field that you want to randomize. I would avoid doing this on date fields.

Finding files is a basic Windows operation. If you don't know how to search your file system, Google is your friend.

Haha, yeah I know. I searched for it, but couldn't find it, that's why I asked. I'll continue searching for it, but I couldn't find on Google where Access would save it.

If not, I'll send you a PM for the database.