Question Saved Excel import not respecting configured Indexes (1 Viewer)

PatThePostie · Jul 14, 2017

I'm having an issue whereby I set-up and configure an import of an Excel spreadsheet into Access 2016. I carefully configure which fields I want to have as an index and which fields should be skipped, and then opt to save the import for future use. The import on its first run is successful and the resulting table has the required fields only and the correct indexes. All well and good.

However, after running the saved import on the same spreadsheet any number of subsequent times, Access takes it upon itself to decide what fields it wants to index, and not respect the fields I previously told it to index. After dumping-out and examining the XML config for the saved import, all my config seems to be correct, Access is just not respecting it. This is causing me issues as Access decides to index fields I have not it not to import, and so the subsequent saved import run fails with the error "Cannot delete a field that is part of an index or is needed by the system". :banghead:

So far I have tried the following to rectify the problem, all to no avail:

Delete the destination table for the import before running the saved import.
Delete the destination table for the import, perform a Compact and Repair, then try running the saved import.
Create a new database and recreate the same import.

Has anyone else experienced this? If so, is there any known work around or do I need to code my own Excel spreadsheet import in VBA?

isladogs · Jul 14, 2017

Have you tried linking the spreadsheet then importing to a buffer table.
Then use that to append the data to your final table with indexes.

JHB · Jul 14, 2017

PatThePostie said:
..The import on its first run is successful and the resulting table has the required fields only and the correct indexes. All well and good.

The first run, it's when you create the import?
How do you run the import afterwards, from the menu, from code or ?
Could you post your database with the import and the Excel sheet with some sample data in it?

static · Jul 14, 2017

IndexType is in MSysIMEXColumns. Doesn't seem to make any difference what you set it to though.

PatThePostie · Jul 17, 2017

Thanks for the replies everyone. Let me answer individually below:

Ridders: No, I haven't tried linking the spreadsheet then importing to a buffer table, and then running an update. The problem I have is that the records in the spreadsheet not only change but new ones are added and existing ones are deleted. Would running an Update Query take care of such additions and removals?
JHB: Yes, the first run is when I create the saved import. So maybe my use of the phrase "subsequent runs" is not entirely correct. Oh, and I originally ran it from code whereby I got the unhelpful error "Run-time error 31602: The specification with the specified index does not exist. Specify a different index. 'ImportName'". You get the slightly more helpful errors when you manually run the saved Excel import. When I get some time, I'll try to setup a database and spreadsheet with some sample data in it and post it here, might take me a couple of days to find the time though.
Static: Yes, you're right about MSysIMEXColumns. All the config for which fields to index are all correctly stored in that table, the issue is that Access is just ignoring it all! FYI, you can get a nice XML config document of saved imports by running the following command in the Immediate Window of the VBA editor:

Code:

? CurrentProject.ImportExportSpecifications.Item("Export-Table1").XML

Upon some further investigation, it seems that the fields that Access incorrectly assigns indexes to on runs of saved imports are the exact same fields that Access suggests should be indexed when setting up the import. So rather than respect the user's choice of fields, Access ignores that and uses defaults again. FYI, the default indexes are determined using the registry key "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Access\Settings\AutoIndex on Import/Create", which has the value "ID;key;code;num". Thus, any field names with those text patterns in are assigned by Access to be default suggestions for which keys to index when setting up an import, and also incorrectly used instead of the import config when running saved imports!

isladogs · Jul 17, 2017

You would use a combination of Append for new records, Update for existing records and if necessary Delete for removing outdated records

Sent from my iPhone using Tapatalk

esun136 · Aug 1, 2019

PatThePostie said:
Thanks for the replies everyone. Let me answer individually below:

Ridders: No, I haven't tried linking the spreadsheet then importing to a buffer table, and then running an update. The problem I have is that the records in the spreadsheet not only change but new ones are added and existing ones are deleted. Would running an Update Query take care of such additions and removals?

JHB: Yes, the first run is when I create the saved import. So maybe my use of the phrase "subsequent runs" is not entirely correct. Oh, and I originally ran it from code whereby I got the unhelpful error "Run-time error 31602: The specification with the specified index does not exist. Specify a different index. 'ImportName'". You get the slightly more helpful errors when you manually run the saved Excel import. When I get some time, I'll try to setup a database and spreadsheet with some sample data in it and post it here, might take me a couple of days to find the time though.

Static: Yes, you're right about MSysIMEXColumns. All the config for which fields to index are all correctly stored in that table, the issue is that Access is just ignoring it all! FYI, you can get a nice XML config document of saved imports by running the following command in the Immediate Window of the VBA editor:

Code:

? CurrentProject.ImportExportSpecifications.Item("Export-Table1").XML

Upon some further investigation, it seems that the fields that Access incorrectly assigns indexes to on runs of saved imports are the exact same fields that Access suggests should be indexed when setting up the import. So rather than respect the user's choice of fields, Access ignores that and uses defaults again. FYI, the default indexes are determined using the registry key "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Access\Settings\AutoIndex on Import/Create", which has the value "ID;key;code;num". Thus, any field names with those text patterns in are assigned by Access to be default suggestions for which keys to index when setting up an import, and also incorrectly used instead of the import config when running saved imports!

Hi, I'm new to Access and VB, and just joined this group. I encountered the same problem of "Run-time error 31602" when using "DoCmd.RunSavedImportExport" to import tables from Oracle. How did you solve your problem eventually?

PatThePostie · Aug 15, 2019

esun136 said:
Hi, I'm new to Access and VB, and just joined this group. I encountered the same problem of "Run-time error 31602" when using "DoCmd.RunSavedImportExport" to import tables from Oracle. How did you solve your problem eventually?

I wrote a function that runs immediately after the saved import finishes, which removes all indexes and primary key settings (as set incorrectly by Access during the import process), and then using VBA's XML library it then parses the XML config (obtained by the command quoted in the code section above) and sets key fields and indexes as they should have been set. So basically, I do what Microsoft Access should have done in the first place.

isladogs · Aug 15, 2019

Sound interesting. Others may find it useful.
Can you post the code you've created.

Question Saved Excel import not respecting configured Indexes (1 Viewer)

PatThePostie

New member

isladogs

MVP / VIP

JHB

Have been here a while

static

Registered User.

PatThePostie

New member

isladogs

MVP / VIP

esun136

New member

PatThePostie

New member

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread