Solved Import from Excel to Access - unable to overwrite (1 Viewer)

hhag

Member
Local time
Today, 19:56
Joined
Mar 23, 2020
Messages
69
Hi,
I'm about to import a spreadsheet from Excel into Access. The table in Access is empty (no records) due to that I've deleted all records. The table had some records previously.
I've exported the table structure, filled it with info and then tried to import it back to Excel. Now I get a message; "It's not possible to overwrite the table/query"
I've gone through all data types, also choosed the primary key from the table I'm about to import. Index with no duplicates.
I do beleive I've gone through every 'inch' of this.... now I need some additional advice. I've cleaned-up the data as well.

Rgds,
 

isladogs

MVP / VIP
Local time
Today, 18:56
Joined
Jan 14, 2017
Messages
18,186
Due to a lawsuit against MS almost 20 years ago, you cannot directly edit an existing spreadsheet from Access.
Instead you can export the table as an Excel file
 

Micron

AWF VIP
Local time
Today, 14:56
Joined
Oct 20, 2018
Messages
3,476
Maybe you have established a relationship between this and some other table, and that is what is preventing you from exporting? It's not clear to me if you're getting the error message from Excel or Access. If it's Excel, then ignore my suggestion.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:56
Joined
Feb 19, 2013
Messages
16,553
I'm about to import a spreadsheet from Excel into Access. .......I've exported the table structure, filled it with info and then tried to import it back to Excel.
Do you mean 'import it back from excel'?

If so, what method are you using?
 

hhag

Member
Local time
Today, 19:56
Joined
Mar 23, 2020
Messages
69
Hi,
Yes, import it back from Excel. I've first exported the table structure from Access to Excel, and then added info in the table in Excel. Now when finally ready I want the info imported into Access - without success. I'm in Access when getting the error message. I try different methods in Access, none works.
I've tried:
1: "Import source data to a new table or to a existing tables that already exists (overwrite)" and
2: "Add a copy of the records in a selected table".

When going for no 1 I get the message in Access that it's not possible to overwrite. (Addressed table is empty = no records).
When going for no 2 I get the message "Out of range".

I've with the best of my knowledge tried to go through all data types, index etc and I actually don't know what's wrong.

(N.B.! I've a swedish version of Acess so I've done a direct translation into english with the best of my knowledge :rolleyes: )

Do someone has any idea?

Rgds
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:56
Joined
Feb 19, 2013
Messages
16,553
OK so you are not using code but instead the routines in the external data ribbon?

Think you need to provide some visual examples of exactly what you are doing - the message for #1 makes sense if you are trying to 'update' existing data but that existing data does not exist. The second message implies your excel spreadsheet is trying to 'supply' more columns than match the table.

If you are using the ribbon functionality it is not the best way to get data into access if this is a regular process. Better to use VBA or SQL.

You could try copy/paste from excel to access
 

Micron

AWF VIP
Local time
Today, 14:56
Joined
Oct 20, 2018
Messages
3,476
I've first exported the table structure from Access to Excel, and then added info in the table in Excel
I have to wonder why not just edit/add the data in Access instead of this back and forth thing.
 

hhag

Member
Local time
Today, 19:56
Joined
Mar 23, 2020
Messages
69
@Micron; I have several records I want to import to Access, so it would be tremendous time consuming if I should edit data manually.
I've read about the transfer Spreadsheet method - I'll dig into this tomorrow I guess.
 

Micron

AWF VIP
Local time
Today, 14:56
Joined
Oct 20, 2018
Messages
3,476
So I guess that means you have a way of editing automatically in Excel that you can't do in Access. After reviewing post 5, I now have to wonder why you just don't link the spreadsheet to Access. You're not editing the Excel data on the Access side anyway, it seems. Never mind...I just wonder a lot.
 

hhag

Member
Local time
Today, 19:56
Joined
Mar 23, 2020
Messages
69
I managed after I've doubled checked field names and data types.
Thanks all! 🙏
 

zeroaccess

Active member
Local time
Today, 13:56
Joined
Jan 30, 2020
Messages
671
If you do not need to retain the original table, you could just delete the table and let Access create a new one with the import. It's decent at picking data types, in my experience, if your Excel file was set up correctly (no numbers formatted as text, please!)
 

Users who are viewing this thread

Top Bottom