Re: Which is best - recordsets/VBA V SQL statements for updating data

Now I am intrigued and seriously would like to know of a suitable
alternative.

One typical requirement was a client who required a report that forecast
materials requirements for a 12 month period. This required the analysis of
forecasted sales and current inventory to determine expected production for
the period. That information was then used to determine materials, through a
BOM, and other production requirements. On the report the result had to be
displayed the same result in three formats, two graphs and a table, and no,
they must have it on one report.

This description makes is sound reasonable simply, but believe me it wasn't.
There are many system defaults that need to be checked and incorporated, and
complex calculations that draw data from many areas.

I know this is probably not enough information, but how would you approach a
task like this.

Jeff

"Lyle Fairfield" <lylefairfield@xxxxxxx> wrote in message
news:1136404026.564635.11620@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Bri wrote:
>
>> That's just as useless a response as your original one.
>
> I hesitate to ask you to explain a situation where one might need a
> temporary table. I suppose that it's quite possible that in some
> situations one might get to the point of requiring a temporary table. I
> have never created or experienced those situations. But to debate them,
> one might have to delve back a long way to the initial design and
> conceptual framework of the application. IMO, such an examination would
> be almost impossible on CDMA.
>
> But I do not think one should say here on CDMA, temporary tables have a
> legitimate use, without allowing someone else to say that in his/her
> opinion temporary tables are unnecessary in any well designed
> application. That is my opinion, but I am perfectly happy to be shown
> to be wrong. But, please, understand that if you say "In Situation 'A'
> Temporary Tables are required" that Situation 'A' and all its
> precedents must be defendable.
>
> As to my original response I am sorry that you found it useless. I'm
> including it here so that you can expand on that, and perhaps point out
> if some parts are more useless than others for my edification:
>
> "the original one"
>
> *******
>
> SQL is better than Recordset Updating and Vice Versa.
>
> When using a Server DB, SQL happens on the Server. Suppose the SQL is
> demanding and takes 5 minutes to run. Suppose 20 Users call the SQL (on
> discrete sets of records) right now. Can the Server machine deal with
> the demand? Probably, it can. But is this more efficient than each User
> getting his/her set of records and processing them on the Client
> machine? Who knows? It may take some thought, perhaps even testing to
> decide.
>
> Sometimes we inherit non-normalized DBs. The data is related but there
> is no clear rule that defines the relationship. We find we have to
> examine (with a function in code) much of the data in each record to
> find which record in Table1 relates to which record in Table2. And then
> we have to do the work, eg, create genealogical html files and upload
> them to our genealogy site. Is this a job for SQL or for Recordset
> manipulation? I use Recordset manipulation.
>
> Sometimes we process data in multiple scans. During these scans we are
> trying, testing, discarding and choosing. After each scan we assess our
> results with respect, not to one row, but to the whole data. Is a
> Recordset a good tool for this? I think so. If we are going to change
> the value of something 47 times, do we want to write each of those
> changes to our DB? Disconnecting the Recordset and doing our work in
> memory seems a better idea to me.
>
> With recent versions of MS-SQL one can do almost anything that one can
> do with a Recordset and VBA on the Client machine, with a Stored
> Procedure or UDF on the Server machine, using loops, conditional,
> cursors or whatever. The first situation I describe above becomes quite
> important. How much work do you want the Server machine to do? How much
> work do you want the Client machine to do? The Server machine is
> serving many Clients. The Client machine is probably serving one
> Client.
>
> Disconnected Recordsets, reconnection and UpdateBatch make your
> question much more pertinent than it was a few years ago. With them we
> can have a mini independent data store on our Client machines. We can
> play with it forever, then reconnect it and save our changes in an
> instant.
>
> And of course, with a clever use of Table and Index Creation within
> Transactions we can effect disconnected Recordsets in DAO. [We always
> Rollback the Transaction]. Nothing is ever written to the DB (unless we
> want it to be). Such procedures while demanding to code can be
> extremely fast and powerful. (They can blow SQL away after the Tables
> and Indexes have been created, but we have to have enough work for them
> to so that this advantage makes the time required for their creation
> worthwhile).
>
> After all that, I must note that I estimate that I use SQL 95%of the
> time.
>
> ******
>


.

Relevant Pages

  • Re: Fill combo boxes from Access tables
    ... ' Open the file containing the client details ... Dim MyArray() As Variant ... office from the combobox for it to populate the text boxes (txtAddress1 & ... ' Set the number of Columns = number of Fields in recordset ...
    (microsoft.public.word.vba.general)
  • Re: Client side printing
    ... xp_cmdshell is executed on the server machine, which has no knowledge of the client who submitted ... Perhaps you can specify the printer name using UNC naming so the print is done on ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Custom marshalling of ADO.NET dataset to ADO Recordset
    ... >>> translates between a dataset and a recordset when a dataset is returned ... >> client transform the XML. ... >>> Sub MoveFirst() ... >>> ReadOnly Property RecordCount() As Integer ...
    (microsoft.public.data.ado)
  • Re: Combo box
    ... this routine loads a listbox with client details stored in a table in ... ' Open the file containing the client details ... Dim MyArray() As Variant ... ' Set the number of Columns = number of Fields in recordset ...
    (microsoft.public.word.vba.general)
  • Re: Combo box
    ... ' Open the file containing the client details ... Dim MyArray() As Variant ... ' Set the number of Columns = number of Fields in recordset ...
    (microsoft.public.word.vba.general)