topic CCTV Manager: Issue transferring tables from .mdb to .gdb in ArcMap in Water Utilities Questions

CCTV Manager: Issue transferring tables from .mdb to .gdb in ArcMap

Brian_Laws — Thu, 09 Apr 2020 14:20:14 GMT

Our organization is attempting to use CCTV Manager to display the results of our sewer main inspections we receive from our contractor. I installed the Utility Solutions toolbox (version 2.2.4) into ArcGIS Pro (version 2.5) as well as downloaded the sample data to play around with and learn how to use the CCTV tools. We quickly found out we couldn't use the Import Access Tables GP tool because we are still running 32-bit Microsoft Office 365, so digging into the Frequently Asked Questions of the CCTV Manager help I found the workaround:

So then I went into ArcMap (version 10.7.1) and ran the Create File GDB GP tool to create a new file geodatabase and then ran the Table to Table GP tool to transfer all the tables from the Sample_v6.mdb to this new FGDB. I then returned to ArcGIS Pro and tried to run the Generate CCTV Pipe Features GP tool, but received the following errors:

So then I opened up the attribute tables of both the INSPECTIONS and CONDITIONS table and this is what I found:

It seems like the Table to Table tool turned the InspectionID field in the INSPECTIONS table and the ConditionsID field in the CONDITIONS table into an Object ID field, which I cannot use for the Generate CCTV Pipe Features tool. I did some research and found that others had used the Table to Geodatabase GP tool to transfer the tables from the .mdb to .gdb, so I tried that too and got the same result. Not finding any solutions, I decided to try and change the Aliases of the Object ID fields to 'Object ID' and recreate the InspectionID field in the INSPECTIONS table and the ConditionsID field in the CONDITIONS table and then field calculate the values from the Object ID fields. I then ran the Generate CCTV Pipe Features GP tool and it seems to run and give the desired output as expected. Finally I tried the same procedure with the other sample data given and for the Sample_v7.mdb it worked without issues, but with the ITpipes_v1.5.43.mdb I had the same issues with the exact same fields.

So I was just wondering if I am doing anything wrong or if there is a better way to transfer these tables from .mdb to .gdb where it will just add the Object IDs without messing with the original data?

Re: CCTV Manager: Issue transferring tables from .mdb to .gdb in ArcMap

MikeMillerGIS — Thu, 09 Apr 2020 14:59:19 GMT

In access, were those fields Autonumber fields?

Re: CCTV Manager: Issue transferring tables from .mdb to .gdb in ArcMap

Brian_Laws — Thu, 09 Apr 2020 15:04:42 GMT

I don't know, this was sample data that was provided with the CCTV Manager solution and I'm not that proficient in Access. Is there a way to tell if I open the .mdb in Access?

Re: CCTV Manager: Issue transferring tables from .mdb to .gdb in ArcMap

MikeMillerGIS — Thu, 09 Apr 2020 18:24:55 GMT

I reviewed the sample data, the v6 data does have the condition ID field as an autonumber, which ArcMap will treat as ObjectID. The import access table tool that we include handles this issue for you. If you encounter this in and cannot use this tool in ArcGIS Pro, the simplest option is to open the table in Access, add a new number column using Design View and Copy and Paste the values from the Autonumber field into it.

Re: CCTV Manager: Issue transferring tables from .mdb to .gdb in ArcMap

Brian_Laws — Thu, 16 Apr 2020 12:44:25 GMT

Thanks Michael for your help. Since I'm not too good with Access, I exported the tables I needed as an Excel files from Access and then used the ArcGIS Pro GP tool Excel to Table to put them in a .gdb. Then I was able to use the Create CCTV Pipeline Features tool and it generated the content as expected with no issues.

I have also worked with some past CCTV inspection data we received from our contractor. They also had the ConitionID and InspectionID fields as an autonumber as well, so maybe this is a common practice? In that case, perhaps the online help for those running 32-bit Microsoft Office should be revised with another solution that does not involve the Table to Table or Table to Geodatabase GP tools because they will not generate output useable by the Create CCTV Pipeline Features GP tool.