Cannot delete a field taht is a part of an index or is needed by the system (1 Viewer)

jaryszek

Registered User.
Local time
Today, 06:34
Joined
Aug 25, 2016
Messages
756
Hi,

i am importing using wizard Excel table and skipping all columns from 10 expect 2 of them.

After that i am saving steps and have only 2 columns (without any indexes).

Problem is when i am trying to run saved step i am getting error:


and instead of 2 columns my whole table is imported (so skipped column was not saved within imported steps).

How to solve the problem?
I can import whole table and use queries but maybe i am doing something wrong.

Please help,
Best wishes,
Jacek
 

Attachments

  • Screenshot_6.jpg
    Screenshot_6.jpg
    69.3 KB · Views: 1,047
  • SourceTestData.xlsx
    10.3 KB · Views: 130
  • Database15.accdb
    644 KB · Views: 98

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 28, 2001
Messages
26,999
One possibility is to link the Excel worksheet as a table and then use a query to selectively grab only the columns you want.

You MIGHT need to look up how to dynamically link and unlink tables if you want to do that.

Another method would be to import everything to Access in a temporary table and then use a query to grab what you wanted from the temp table. If you intend to do this very frequently, you would need to learn how to create a temporary 3rd database file to hold the temporary table so that you can avoid bloating your database enough to require it to undergo frequent compact & repair operations.
 

jaryszek

Registered User.
Local time
Today, 06:34
Joined
Aug 25, 2016
Messages
756
thank you The_Doc_Man,

You MIGHT need to look up how to dynamically link and unlink tables if you want to do that.

oo sounds nice. This is better than creating temporary tables?
Why to unlink table and link once again? Can i link only one time? What is the purpose of unlinking?

If you intend to do this very frequently, you would need to learn how to create a temporary 3rd database file to hold the temporary table so that you can avoid bloating your database enough to require it to undergo frequent compact & repair operations.

This is very important what you said here. I didnt think that this can be very nice solution for database bloating. Wow.

So creating one additional database and how to link ? Create link lie from FE to BE for this tables?

Best Wishes,
Jacek
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 28, 2001
Messages
26,999
The comments I made have to do with how often you need to do this operation. If it is a one-time thing, manually import the spreadsheet to a staging table (or temporary table if you prefer that title), then write a query to do the transfer of what you wanted, then delete the temp table. Then close the DB, copy it (for safety), then compact and repair the database. If that works, you are done and can delete the copy or call it a "backup" file or something.

BUT... if you have to do this on a repeated basis, that kind of handling can be a problem, particularly if you eventually intend to share this database with others. In the case where this import process needs to be repeated even just a few times, you would not want to "drag around" all of the spreadsheet (even as an externally linked table) if you were not going to be using it. So in this case, the idea would be to decide when you need to do this whole process. You set up a form or macro to drive it.

Inside this process, you would link to the external spreadsheet as thought it were a table. Note that this is actually safe with regard to the spreadsheet since Access will not let you update a spreadsheet linked as a table. So now you have this linkage. Write an INSERT INTO style of query that takes data from the linked spreadsheet and adds it to your table. (Presumably, if you were doing this on a regular basis, this destination table has already been created.) OK, run the action query. But now, you have this linked table dangling off your database that nobody else needs. And YOU don't need it any more once the import is complete, so you unlink it.

Doing it this way, there IS no immediate need for a compact & repair since no temporary table was created then erased and no table was deleted. These latter actions are the most common sources of database bloat that would eventually lead to the need for a compact & repair operation. Remember, the more you have to diddle with the database manually, the more chances you have to make an error and break something, even when doing something so simple as a compact & repair.

The other possibility, using a 3rd database file, is to make an empty database that is set up like you need it with empty tables - defined but no data in them. When the time comes, you COPY that database to a convenient location, link to the tables, do the import INTO THESE TEMP TABLES. Now run your query to extract what you wanted. Then unlink the tables from that copy of the 3rd file. And DELETE THE FILE. Now the fact that it got bloated is immaterial because you cleaned up the bloat in one operation!

Either of the two approaches I mentioned would work to eliminate database bloat making your primary database files slower and uglier. Bulk deletes and structural deletions almost always make a database file ugly and in frequent need of compact & repair operations, which as noted earlier, are to be avoided.
 

jaryszek

Registered User.
Local time
Today, 06:34
Joined
Aug 25, 2016
Messages
756
Thank you The_Doc_Man. Awesome explanation.

Ok i have to often take data from imported temp tables and use them, so i will have to use one of your 2 methods.

1. If they are similar - which should i choose? Linking inside one database or having separate database will be better idea? Or differences are so small that this is no matter?

2. Second question - why this error is occuring? Why this is not saving skipped column via imported steps wizard?

Best wishes,
Jacek
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 28, 2001
Messages
26,999
I cannot answer why the error is occurring because I so rarely use this feature. Either of the methods I outlined uses a separate query AFTER the spreadsheet is linked or imported, and the separate query is guaranteed to only import the stuff you wanted. Then, you break the link (and if you used a temporary file, delete it) and go on about your business.

Also, I never open someone else's database. Therefore, I did not look at the setup for your import operation. It is just that I have been burned too often, and often enough it was by something intended to be innocent - but turned out to not be so innocent. For that reason, I don't open any external database lightly.
 

jaryszek

Registered User.
Local time
Today, 06:34
Joined
Aug 25, 2016
Messages
756
The_Doc_Man,

thank you.
Maybe others can help ?

Did you have similar situation?
What about my database?

Please help,
Best,
Jacek
 

Users who are viewing this thread

Top Bottom