TransferSpreadsheet - how to get it to overwrite

[yitzak]

Hi I'm using TransferSpreadsheet acLink to link an excel sheet into an
Access Db from VB program. It works - but it always adds another linked
table. So every time I run the statement I get xls_file, xls_file2,
xls_file3.

Is there a magic command somewhere to overwrite the exisitng linked
table if already there? like the documentation says it should?

Otherwise how do I know what table I've just added?
searching App.currentDB.tabledefs("xls_file") will always return the
first one - do I have to search created dates?


My workaround:
I tried using acimport, this cures the above problem - but because my
Excel file changes I have to drop the table before every call to
TransferSpreadsheet acimport so the it will create a new table with the
correct fields.

Probably another post but....
Everytime I import an Excel file I drop the table this works. I was
happy with this workaround. I can repeat this cycle many times - the
table gets dropped and populated well. I can even use the linked table
as the source of updates (teh whole purpose!).

However the app sometimes uses a bound data grid to display data from
the linked XL table as soon as I do this - Access/jet gives an error
that it could not lock table when it tries to drop table again. If I
don't display any data it happily lets me cycle through excel files
dropping and creating tables on DB.

N.B. though before deleting table again I clear all references to this
read only datagrid (snapshot recordset). Tried everything closing
datacontrol's recordset obj, recordsource = ""..

Really stuck on this...

 

[yitzak]

Final workaround I put grid into unbound mode. No more table locks.

Found good code here -
http://www.vb-helper.com/howto_dbgrid_unbound.html.

Also Recordset.getRows(x) was very useful to give me an array to
populate the datagrid with.

 

[Klatuu]

I don't know what documentation you read that says it replaces the existing
link. That is incorrect. The behaviour of adding another table with a
number at the end is normal. You mentioned you have trouble when you drop
the table. I would suggest using the DeleteObject method of the Docmd
instead. I use this technique successfully quite often.

Further, I would not suggest binding a form to a linked Excel table. There
are issues with data formatting that may cause you problems. It is also
possible the form could loose the binding information if the table is not
available. A better technique is to create a table with the structure you
need, then when you need to refresh the data from the excel file, do the
following:

1. Delete the data in the Access table
2. TransferSpreadsheet into the Access table from the Excel file.