william.thompson1 at att.net
william.thompson1 at att.net
Sun Sep 26 12:31:41 CDT 2004
<< clearing these by setting the value to Null >> it would be interesting to see what shortcuts would evolve if a refresh was done this way in Excel. <<what you mean>> I meant to say 'the Excel driver in SQL Server, context of DTS' which is ISAM I think - basically text??. I'll look into that. But anyway, I'm glad to get a second nod regarding how Excel files are updated. I was certain there was no insert / delete, but DTS is too flexible sometimes, and the when the package was written, it was apparently tested. <<Excel Workbook... If you need to keep the old data, just rename the workbook >> SQL Server DTS would be doing this task, and I'm still looking into the shell xp (system stored procedure) that handles this. <<use temp tables>> You're right - the Access DB could be attaching after the data is destroyed/refreshed. I appreciate the help, William N. Thompson -------------- Original message from Gustav Brock : -------------- > Hi William > > > Am I correct to assume that within the context of sql server data transfers > that > > > 1.) An Excel spreadsheet is updateable but Not insert/deletable. > > You can update and, if empty rows are present, add records. > No deletes. However, you can write a simple erase function looping > through the rows and the fields of these clearing these by setting the > value to Null. Indeed, for an attached table (see next) this is very > easy and fast. > > > 2.) While the driver is fairly recent, Excel data can be only be > > pulled by worksheet, not pushed. > > Not quite sure what you mean. If you attach a worksheet or - the > preferred method - a Named Range, you can read and write data in both > Access and Excel. > > > 3.) Because the requirement that four worksheets stay intact in > > each Workbook, the destructive refresh imposes a requirement that > > four Access tables must be used by a macro or some other auto > > process to reconstruct four worksheets. In my humble yet slightly > > uncertain opinion. > > The fastest way to "refresh" a workbook is simply to delete it > completely and create a new copy from a master which is never touched. > Also, this ensures that your workbook never gets bloated or corrupted > by multiple writings. If you need to keep the old data, just rename > the workbook (and move it to an archive folder) instead of deleting > it. > > > I'm good to go on the Access app used as a container to push/ be > > pulled, but I'm wondering if I overlooked anything. Temp tables are > > not the favored method either, so this option would be a last > > resort. The data has to be somewhat 'linked' to the file that is > > generated each week. > > Temp tables can be very useful. It is much faster to manipulate data > in a temp Access/Jet table and - when ready - copy the data to the > attached Excel tables. Just keep the temp tables in a separate file > which you - as for the workbook - can replace by a new and empty copy > of a master each week. > > /gustav > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com