Importing spreadsheet problems

[72185]

I am trying to make my database user friendly as possible by making it
so the user doesn't have to import the data from a spreadsheet through
"file"- "Get External Data" etc... So I am creating a button that
creates a table from a query, then goes into the macro and open the
sheet and imports the information and then saves the information into
the table. I am getting either one or the other error that depends on
what I tell the macro "TransferSpreadsheet" to do. First is telling
me that there is not "F1" field in the destination table. Although
there is so I decided to change things around and it still says that
error for the table also. In the spreadsheet there is no "F1"
anything, but the cells in column F are filled.

The other thing is I can get it to import the information but when it
gets the loading it all the way it tells me that it cannot load the
information due to key violations, but I have a primary key as "ID"
autonumber and it also says that it might be due to the possibility of
deleting field, field sizes or that my table already has primary key
but it doesn't.

If anyone know of anything to do please inform me, or if you have a
better way of approaching this problem I am open for opinions.

Thanks

-WS

 

[John W. Vinson]

I am trying to make my database user friendly as possible by making it
so the user doesn't have to import the data from a spreadsheet through
"file"- "Get External Data" etc... So I am creating a button that
creates a table from a query, then goes into the macro and open the
sheet and imports the information and then saves the information into
the table. I am getting either one or the other error that depends on
what I tell the macro "TransferSpreadsheet" to do. First is telling
me that there is not "F1" field in the destination table. Although
there is so I decided to change things around and it still says that
error for the table also. In the spreadsheet there is no "F1"
anything, but the cells in column F are filled.

The other thing is I can get it to import the information but when it
gets the loading it all the way it tells me that it cannot load the
information due to key violations, but I have a primary key as "ID"
autonumber and it also says that it might be due to the possibility of
deleting field, field sizes or that my table already has primary key
but it doesn't.

If anyone know of anything to do please inform me, or if you have a
better way of approaching this problem I am open for opinions.

Please post your code. We can't see it from here and have no possible way to
diagnose.

If you're importing into a table with an Autonumber, though, I'd suggest using
the TransferSpreadsheet method to link to the Excel sheet, followed by running
an Append query to append into all fields *except* the autonumber. This will
import the data without attempting to overwrite the autonumber value (which
may be generating the key error).

John W. Vinson [MVP]

 

[Long Live Aaron Kempf]

if you want to import from a spreadsheet then you should use SQL Server and
either DTS or SSIS

 

[BruceM]

Ignore this troll. He has one or two answers that he applies to all
situations.