RE: TransferSpreadsheet - how to get it to overwrite
- From: Klatuu <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 1 Sep 2005 06:57:11 -0700
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.
"yitzak" wrote:
> 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...
>
>
.
- References:
- TransferSpreadsheet - how to get it to overwrite
- From: yitzak
- TransferSpreadsheet - how to get it to overwrite
- Prev by Date: Exporting data from Access97 to .txt file
- Next by Date: Export to Excel With Password
- Previous by thread: Re: TransferSpreadsheet - how to get it to overwrite
- Next by thread: Re: Text fields in Access reports garbled when exported to Excel
- Index(es):
Relevant Pages
|
