[AccessD] Crossposted - Building a table dynamically -SQL Server

Well...

 >I didn't realize you were working strictly within a db, no real 'non-db' interface.

I have an access database (should really be .net) that has a simple form with about 5 text boxes and 
a button or two.  Server / Database / data table name.  Click, fill the order.  So I can do anything 
I have to in Access.  ATM what I actually do is execute stored procedures on the SQL Server.  More 
or less.

This whole system was a "work in progress" where I started by doing every single thing manually, 
then writing views and stored procedures to automate step one, then automate step 2 another day / 
week, then step three etc.  I originally has about a 40 step checklist.  Now I have no checklist, 
just a form that I click a button or two.

There are some steps which are asynchronous, i.e. the gross data is pulled for an order, all the 
records have to then be exported and run through software running on virtual machines on a different 
server.  Depending on the size of the data set this can take a few minutes to many hours, and be run 
on one or up to three virtual machines.  Once the external program(s) runs, I have to pull the 
processed data back in and "finish" the order.  So it isn't literally "click a button", but it is 
getting darned close.  The parts leading up to the export are a set of SPs, and the parts after the 
export are a second set of SPs.  The export / import itself is a third set of SPs.  And the final 
export to a fixed width file is an Access database that has a link to a fixed named view in SQL 
Server and then stuff in the MDB that performs the export.

This whole thing I was talking about is a small part of the very first process (before the major 
export).

John W. Colby
www.ColbyConsulting.com


Drew Wutka wrote:
> That's true, if it's not broke, don't fix it.
> 
> I was just giving you an idea to think in a different paradigm.
> 
> The way I 'flatten it back out' is pretty simple, just using dynamically
> created SQL statements that fill in Collection/Class groups in the
> business layer.
> 
> If what you are doing is strictly database stuff, with no GUI to speak
> off (so all logic is just inside the db), then my idea doesn't fit your
> issue at all.  My idea relies on a business layer (that would be
> utilized in a GUI like ASP, VB or Access).  I didn't realize you were
> working strictly within a db, no real 'non-db' interface.
> 
> Drew
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Sunday, August 09, 2009 1:36 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Crossposted - Building a table dynamically -SQL
> Server
> 
> Drew,
> 
> How do you "flatten it back out" to pull a million records stored that
> way?  And how do you query 
> the structure.My solution works really well as it is.  I end up with a
> database per order, which I 
> NEED because the client often asks for results from that exact same data
> set later (quite common), 
> when I have only delivered a portion of the records to the client.
> 
> Inside each order database I end up with all of the queries and stored
> procedures (and data of 
> course) required to pull the specific records, and specific fields.
> 
> I understand what you are doing, but I question whether it would be very
> easy to work with both when 
>   filling the "table" and then when pulling specific records out of the
> "table".  It doesn't "feel" 
> like a solution to this specific problem.
> 
> What I was hoping was that someone out there on our lists would have
> done something like I am doing, 
> actually creating a normal table on the fly, but where the table has a
> variable set of fields, and 
> discuss how to do that using temp tables in a stored procedures (in sql
> server).  Temp tables might 
> not even be the right term, a cursor is more what I mean (I think).
> 
> I actually got all of this stuff working, but it is not as
> straightforward as I would wish.  Perhaps 
> "if it ain't broke..." might very well apply here.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> The information contained in this transmission is intended only for the person or entity 
> to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
> Sensitive material. If you are not the intended recipient, please contact the sender 
> immediately and destroy the material in its entirety, whether electronic or hard copy. 
> You are notified that any review, retransmission, copying, disclosure, dissemination, 
> or other use of, or taking of any action in reliance upon this information by persons 
> or entities other than the intended recipient is prohibited.
> 
>