Unable to import excel spreadsheet to table

[bugsy]

Hey

I have a procedure that imports excel spreadseet to access table ever today.
today it failed. Forrmat was the same as yesterday.

I did 'copy/paste special/format' from yesterday's file; still today's file wouldn't import.

Tried to copy manually to the table, got the error message about 'field not being in the same format'

Went to table's design view, and changed everything to 'memo', the was able to do copy/paste.
There wer no values with more then 20 characters
I was able to change 'memo' back to text and date/time without loosing any date.

Has anyone ecountered this ? Going changing table, and then chaging it back doesn't sound like the best practice
what can be done ?
can abything be done to excel file ?

 

[rsmonkey]

you copy & paste special your excel spreadsheet every day? thats not good db design at all , forgive me for saying! There are so much easier ways! Does this excel file get replaced everyday or just updated? If it gets updated then create a Link-Table to it there for you'l never have to 'Paste-Special' or anything like that again just run your reports. If the file is replaced everyday then create a 1 button form with the TransferSpreadsheet command. Google or Search these forums for TransferSpreadsheet plenty of info on it.... as for your import problem.. obviously you know its a problem data-types clashing i.e. trying to paste text fields into to integer fields etc! Changing this to Memo's wont help you at all.. Access & VBA get very funny about memo's when trying to maniplate them in code etc... However i suggest you create a proper import script then see if your problem still persists!

 

[bugsy]

you copy & paste special your excel spreadsheet every day? thats not good db design at all , forgive me for saying! There are so much easier ways! Does this excel file get replaced everyday or just updated? If it gets updated then create a Link-Table to it there for you'l never have to 'Paste-Special' or anything like that again just run your reports. If the file is replaced everyday then create a 1 button form with the TransferSpreadsheet command. Google or Search these forums for TransferSpreadsheet plenty of info on it.... as for your import problem.. obviously you know its a problem data-types clashing i.e. trying to paste text fields into to integer fields etc! Changing this to Memo's wont help you at all.. Access & VBA get very funny about memo's when trying to maniplate them in code etc... However i suggest you create a proper import script then see if your problem still persists!

I have the proper 'proper import script' with 'transferSpreadsheet' already !!
>>I have a procedure that imports excel spreadseet to access table ever today.

that was the first line in my message.
There are no integer fields, only text & date.
i'll check 'text' sizes again and set thewm to maximum if they are not
today it failed.

 

[rsmonkey]

more so i would check your date fields... is their a predefined structure for dates i.e. dd/mm/yyyy if so check that out & check no text is trying to be inserted into a date field of course a date can be inserted into a text field though. Also as i said Access gets funny about ' so if you're importing say names like Mc'Bane then it might pull up errors... etc..

Alternately just delete the import table & then run the transferspreadsheet & it should rebuild that table for you with all the right fields etc..