issue appending data from an excel to an access table (1 Viewer)

Christie

New member
Local time
Today, 09:15
Joined
Sep 9, 2021
Messages
9
I'm trying to append new students from an excel spreadsheet to a current access table. I've used the same excel spreadsheet to add the new students' info into the student table every year but this year not one single bit of info was moved into the database.
This is the error message I received:
1631200623924.png

I hit yes, and nothing was added.

I don't even know where to begin to troubleshoot.
 

Ranman256

Well-known member
Local time
Today, 09:15
Joined
Apr 9, 2015
Messages
4,339
save the excel book to the same place everytime: c:\temp\import.xlsx
link it as an external table.
then make an append query.
run the query, you will get better error messages.

then the steps will be:
1. save xl book to c:\temp\import.xlsx
2. run apd query.
done.
 

Christie

New member
Local time
Today, 09:15
Joined
Sep 9, 2021
Messages
9
save the excel book to the same place everytime: c:\temp\import.xlsx
link it as an external table.
then make an append query.
run the query, you will get better error messages.

then the steps will be:
1. save xl book to c:\temp\import.xlsx
2. run apd query.
done.
The spreadsheet is always in the same place so that won't be an issue. I always chose option 2 "append a copy of the records..." but you are saying I should select option 3 "link to the data source by creating a linked table"? The only issue I see is that I delete the "old data" (previous year 'new students') when I add the new students and I don't want to delete them from the access table.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:15
Joined
Sep 21, 2011
Messages
14,041
If you linked the newdata excel sheet, then you could issue a simple append from that to your table.?

At least make a copy of your DB and try it that way. As @Ranman256 states, you will likely get a more descriptive error message.?
 

Christie

New member
Local time
Today, 09:15
Joined
Sep 9, 2021
Messages
9
save the excel book to the same place everytime: c:\temp\import.xlsx
link it as an external table.
then make an append query.
run the query, you will get better error messages.

then the steps will be:
1. save xl book to c:\temp\import.xlsx
2. run apd query.
done.
This helped give a better error message. I now know that all of my lines have validation rule errors. Thank you!
 

Christie

New member
Local time
Today, 09:15
Joined
Sep 9, 2021
Messages
9
If you linked the newdata excel sheet, then you could issue a simple append from that to your table.?

At least make a copy of your DB and try it that way. As @Ranman256 states, you will likely get a more descriptive error message.?
It absolutely worked to give a better error message. Thanks!
 

Users who are viewing this thread

Top Bottom