transferspreadsheet is concatinationg, not replacing data

  • Thread starter Thread starter Shivalee Gupta via AccessMonster.com
  • Start date Start date
S

Shivalee Gupta via AccessMonster.com

i am using transferspreadsheet to import like 25 excel files into access tables. i keep updating those excel files and then i have to import them again & then queries, forms & reports work on them.
Big Problem: Transferspreadsheet is not replacing the older text.. it is concatinating each time i click on the import button with the transferspreadsheet code in it. if an excel file has 15 rows, 1st time i import, it shows 15 rows, if i add 5 rows, what i get is 15 rows + 20 rows, wherein i should get only 20 rows.
 
Shivalee,

You need to clear your table before a new import. To do this, add the
following lines of code at the beginning of the command button's procedure:

tblname = "YourTableName"
strSQL = "DELETE * FROM " & tblname
CurrentDb.Excecute strSQL

HTH,
Nikos
 
i have written the same thing you have given me but it gives me error.

run time error 3131
syntax error in From clause

is there anything else also besides what you have given me...any other line of code.
do i have to define/declare tblname or strSQl?

in case of tblname = "YourTableName"
i have added the path :
tblname = "D:\Documents and Settings\shivaleegupta\Desktop\Shivalee\barun project\database\25 table db\usr40"

Please help me...
thanks for all the help nikos,
shivalee
 
Shivalee,

variable tblname should hold the name of the table in Access you are
appending to, so the SQL action query deletes all the records in the
table before appending (all the records, again) from the spreadsheet.
The name of the table must be exactly as it appears in the database
window (enclosed in double quotes).
The value you are assigning looks like the path/name of a file outside
your Access database (or the database itself?), which is wrong.

HTH,
Nikos
 
thanks thanks thanks...a ton. u r great.
thanks nikos,
regards,
shivalee
 
Back
Top