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]

Output it to Word instead? Maybe easier to find whatever text you're looking for....

 

[Legacy 386498]

Output it to Word instead? Maybe easier to find whatever text you're looking for....

I can't output it in Excel or Word. Both "generate" a file that I can't find or I'm doing something wrong here.

I go to Tools, Analyze, Documenter. I select everything (hundred of pages, but at least I have everything), then you can export to Word or Excel, but both seem to generate a file, but when I search the name in Windows Explorer for the entire pc, I can't find it..

 

[Micron]

I haven't experienced that issue; then again I'm not sure I ever created a report with that many pages. It might be failing silently because it's too large? Try it with a bare minimum of requested data just to test a smaller export. If it works then, you know that's the issue. You're only looking for a specific thing that doesn't exist in most of the properties in most of the things you'd be looking at so there's not much point in grabbing everything. Don't forget - I said that if the issue lies in a form or report control expression, this won't find it anyway.

 

[Legacy 386498]

I haven't experienced that issue; then again I'm not sure I ever created a report with that many pages. It might be failing silently because it's too large? Try it with a bare minimum of requested data just to test a smaller export. If it works then, you know that's the issue. You're only looking for a specific thing that doesn't exist in most of the properties in most of the things you'd be looking at so there's not much point in grabbing everything. Don't forget - I said that if the issue lies in a form or report control expression, this won't find it anyway.

Yeah, true. I'll test it tomorrow morning then (I don't remember if I tried it today to be honest). You said that you never experienced that issue, did you tried converting it in Access 97 or was it in Access 2003 to 2016?

 

[jackd]

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

Here's a routine that will review forms and identify the recordsource for each to the immediate window. It may give you a starting point.
Good luck.

'---------------------------------------------------------------------------------------
' Procedure : getFormRecordSource
' Author    : Jack
' Date      : 29-12-2012
' Purpose   : To get the recordsource of each form in the current access database
' Outputs formname and recordsource to the immediate window
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub getFormRecordSource()
          Dim afrm As AccessObject
          Dim frm As Access.Form
10        On Error GoTo getFormRecordSource_Error

20        For Each afrm In CurrentProject.AllForms

30            If Not afrm.IsLoaded Then DoCmd.OpenForm afrm.name, acDesign, , , , acHidden
40            If Len(Forms(afrm.name).RecordSource & "") = 0 Then
50                Debug.Print afrm.name & "  -- " & "**NO ASSIGNED RECORDSOURCE**"
60            ElseIf InStr(Forms(afrm.name).RecordSource, "SELECT ") > 0 Then
70                Debug.Print afrm.name & "  -- " & "  -    SQL      - " & Forms(afrm.name).RecordSource
80            Else
90                Debug.Print afrm.name & "  -- " & "  - Table/Query - " & Forms(afrm.name).RecordSource
100           End If
110           DoCmd.Close acForm, afrm.name
120       Next afrm

130       On Error GoTo 0
140       Exit Sub

getFormRecordSource_Error:

150       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getFormRecordSource of Module AWF_Related"
    End Sub

 

[Micron]

did you tried converting it in Access 97 or was it in Access 2003 to 2016?

most likely was 2016

 

[Schoen]

I also had this problem,
Thanks for sharing
--------------------
apkafe!

 

[Legacy 386498]

I haven't experienced that issue; then again I'm not sure I ever created a report with that many pages. It might be failing silently because it's too large? Try it with a bare minimum of requested data just to test a smaller export. If it works then, you know that's the issue. You're only looking for a specific thing that doesn't exist in most of the properties in most of the things you'd be looking at so there's not much point in grabbing everything. Don't forget - I said that if the issue lies in a form or report control expression, this won't find it anyway.

Converting to Word the two queries that are at fault worked, but I still couldn't find any Description field. I'll create a back up with random values and send it to you.

 

[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 finally modified the data of the database, here's the link: easyupload.io

Hoping you can find the problem easily.

Thanks for taking the time, it's much appreciated.

 

[jackd]

You need a pwd in order to download???