[AccessD] Acc used to resolve SQL Svr Excel prb

JMoss jmoss111 at bellsouth.net
Sun Sep 26 04:20:12 CDT 2004


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




More information about the AccessD mailing list