william.thompson1 at att.net
william.thompson1 at att.net
Sun Sep 26 12:48:43 CDT 2004
Jim, Good point. I'd like to be doing just that, and have an Access app already set up. I'd also like to log some of the process in a little xml file. I've got a bunch of code in several different places that I need to bring together to make it happen. This situation - the SQL Svr DTS package was apparently written some time ago, tested with a not-so-realistic set of data. It was certain to break on 'Delete from Workbook' script in the transform. I was almost sure (now very sure) there was no insert / delete in ISAM, but DTS is too flexible sometimes, and the when the package was written, it was apparently tested. The Access app I'd like to be similar to your situation - flexible and be able to handle any data source - SQL Server, Oracle, Excel, Text, and XML - soon. I imagine it will take some time for DTS packages in a typical Enterprise to be written with this in mind. Oracle RDMS are probably further along in terms of XML. Bill -------------- Original message from "JMoss" : -------------- > Why not start a macro and then format the sheets and save the macro, then > copy the vba code from the macro into an access module, that lets you > recreate the formatting? Then read the data from sql server into recordsets > in access and use automation to create the spreadsheet. I am doing this with > linked Oracle Financials tables and it works quite well. > > Jim > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of > william.thompson1 at att.net > Sent: Sunday, September 26, 2004 12:17 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Acc used to resolve SQL Svr Excel prb > > > List, > I have a question about refreshing files that, for various reasons, I'm > reluctant to create a job to delete in order to replace the data in them. > I'm thinking that Access will be the desired tool to replace/contain data > used in a multi-sheet workbook, but am not clear on a few things about SQL > Svr refreshes. (The workbook's format and sheets layout has to stay > intact). > > One of the steps in a DTS package is used to run a sql statement against the > worksheet - but the sql statement doesn't do anything in this case - it was > simply set up to fit into the context of a series of DTS packages for > purposes of destructively refreshing sheets within a set of Excel > spreadsheets on a file share based on a customized set of strings in DTS. > One package replaces the data in each worksheet. > > 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. > 2.) While the driver is fairly recent, Excel data can be only be pulled by > worksheet, not pushed. > 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. > > 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. > > Any comments/suggestions appreciated. What would we do without Excel huh? > > Bill Thompson > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com