When do I open my Recordsets?

  • Thread starter Thread starter Joy M
  • Start date Start date
J

Joy M

Hi -

I was wondering when to open and close my recordsets. I am using DAO for
the first time.

Briefly, I loop through one recordset, and depending on certain field
values, modify/add records in other tables (i.e. recordsets).

I want to use functions for these updates/appends because some code is
reusable.
Also functions are a lot handier for organizing my code.

Do I define and open all the recordsets that I will be needing when I start
the outer loop (and pass them as Function parameters)
then close them at the end of the loop?

Or do I define and open these recordsets in the procedures where I will be
using them, then close them after?
I am talking about 10 recordsets altogether.

Is one way faster than the other? More than one user will be using this
program at a time.

Thanks for your advice. I can really use some help!

Joy
 
Joy said:
I was wondering when to open and close my recordsets. I am using DAO for
the first time.

Briefly, I loop through one recordset, and depending on certain field
values, modify/add records in other tables (i.e. recordsets).

I want to use functions for these updates/appends because some code is
reusable.
Also functions are a lot handier for organizing my code.

Do I define and open all the recordsets that I will be needing when I start
the outer loop (and pass them as Function parameters)
then close them at the end of the loop?

Or do I define and open these recordsets in the procedures where I will be
using them, then close them after?
I am talking about 10 recordsets altogether.

Is one way faster than the other? More than one user will be using this
program at a time.
Click to expand...


Opening a recordset is a fairly heavy duty operation so,
generally, it will be faster to open it before starting a
loop that uses it and close it after the loop ends. OTOH,
if the table is very large and you are only going to operate
on a few records, it may be better to open a severly
restricted recordset inside the loop. In other words, the
general rule may have significant exceptions.

If possible, it is much, much faster to use a few well
designed Update queries to do the work instead of looping
through a large recordset.

Bottom line, the "best" design depends on what and how often
you are doing this "updating".
 
Hi Marshall,

Thanks for your advice. I see what you mean.

I had this whole thing working with Update queries, and now I am switching
it to Recordsets.

What I am doing is processing a batch of Invoices (regarding insurance
policies), to append/update records in other tables.
(Typical 3GL application, right?)

When I used update action queries, I was processing each Invoice record at
the time it was created on the form.
Thus I had the Invoice number (from the form control) to use as criteria in
my action query.

But now, I am told I must process the invoices in a batch.
So in my loop, when I open the Invoice recordset, I use
rsInvoice!invPolicyNo.

Can I use the expression rsInvoice!invPolicy as Criteria for an action
query?
I think not. I think it has to be of the type
Forms![formname]![invPolicyNo]

Please let me know, because action queries are faster and much easier to
use.

Thanks again for your help.

Joy
 
Joy said:
Thanks for your advice. I see what you mean.

I had this whole thing working with Update queries, and now I am switching
it to Recordsets.

What I am doing is processing a batch of Invoices (regarding insurance
policies), to append/update records in other tables.
(Typical 3GL application, right?)

When I used update action queries, I was processing each Invoice record at
the time it was created on the form.
Thus I had the Invoice number (from the form control) to use as criteria in
my action query.

But now, I am told I must process the invoices in a batch.
So in my loop, when I open the Invoice recordset, I use
rsInvoice!invPolicyNo.

Can I use the expression rsInvoice!invPolicy as Criteria for an action
query?
I think not. I think it has to be of the type
Forms![formname]![invPolicyNo]

Please let me know, because action queries are faster and much easier to
use.
Click to expand...


First, I want to say that having to update a bunch of other
tables because of changed information in the invoice table
sounds suspiciously like a rats nest of a normalization
problem. Whatever these updates are doing can most likely
be avoided by using a query that joins the tables whenever
someone wants to see the data in a report.

Second, if these other tables must be updated, I would think
that your original approach was the right way to do it.

Now, to get back your question, you need to understand a
little more about query parameters. It is important to note
that a parameter like [Enter Start Date] and
Forms![formname]![invPolicyNo] are the kind that Access will
deal with automatically when it run the query as a record or
row source or in RunSQL statement. The DAO library that is
the underpinnings of all the Access UI stuff can be used
directly in your VBA procedure and provides several features
that can help out in way that Access does not try to deal
with.

A query parameter can be any term in an expression, just
like a field name and you can have a bunch of them. The
general outline of the code to execute a with using your
values for the parameters is along these lines:

Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs!nameofquery
qdf.Parameters!PolicyID = rsInvoice!invPolicyNo
qdf.Parameters!BatchID = mybatchnum
. . .
qdf.Execute 'dbFailOnError ??
Set qdf = Nothing
. . .
Set db = Nothing

Check Help on the Execute method, you should probaly use the
dbFailOnError argument and you might want to utilize the
RecordsAffected property in some way.

A different alternative is to construct the update query's
SQL statement in the VBA procedure by concatenating the
various pieces and values and executing that string:

Dim db As Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "UPDATE table SET numfield = " & numvalue _
& ", textfield = """ & textvalue & """" _
& ", datefield = " & Format(datevalue, "\#m\/d\/yyyy\#")
db.Execute strSQL, dbFailOnError
 
Hi Marshall,

Thank you again for your excellent advice. You have guided me in the right
direction(!)
I understand what you are saying. Good points!

The next chapter in the book I am using - "Beginning Access 2002 VBA" by
wrox -
deals with the QueryDef object in the DAO hierarchy.

Now I will work through the examples there and (quickly!) gain some
experience,
before tackling my 'real' problem...again )))

Joy
 
Joy said:
Thank you again for your excellent advice. You have guided me in the right
direction(!)
I understand what you are saying. Good points!

The next chapter in the book I am using - "Beginning Access 2002 VBA" by
wrox -
deals with the QueryDef object in the DAO hierarchy.

Now I will work through the examples there and (quickly!) gain some
experience,
before tackling my 'real' problem...again )))
Click to expand...


Sounds like a plan to me. Go for it ;-)
 
Hi Marshall -

(Well, I hope you will see this addition to the thread.)

Yes, to explain, the other tables need to be updated directly or added to.
For example, if the Invoice that was created on the form is for a new
Policy,
then we have to add a record to the Policy table.

The tricky part for me, is I loop through the Invoice table,
find a record with transaction type "New Policy" and I want to use the
numbers
from that specific Invoice record to update/append some other table.

So each record in tblInvoice causes a different set of action queries,
based on the transaction type.
I have to grab the numbers from the Invoice record and use them.

So you suggested the line
qdf.Parameters!PolicyID = rsInvoice!invPolicyNo

Hmm...I still don't really understand where the .Parameter property comes
in,
what it is replacing in the QBE grid.

Right now, my Update query works fine if there is only one(!) record in the
Invoice file.
For example, Update PolicyEffectiveDate with the value InvoiceEffectiveDate,
etc.

Do I take out all the column matchups in the QBE grid and use qdf.Parameters
instead?
Then all I am left with is - UPDATE tblPolicy SET - in my query. Can you do
that?!

Maybe - if I have stated here what I want to do clearly enough - you can
tell me how the .Parameter property works.
Does the .Parameter code tells what 'SET's to occur?
And should I still be using action queries, or not?

Thanks again (very, very much).

Joy
 
Joy said:
(Well, I hope you will see this addition to the thread.)

Yes, to explain, the other tables need to be updated directly or added to.
For example, if the Invoice that was created on the form is for a new
Policy,
then we have to add a record to the Policy table.

The tricky part for me, is I loop through the Invoice table,
find a record with transaction type "New Policy" and I want to use the
numbers
from that specific Invoice record to update/append some other table.

So each record in tblInvoice causes a different set of action queries,
based on the transaction type.
I have to grab the numbers from the Invoice record and use them.

So you suggested the line
qdf.Parameters!PolicyID = rsInvoice!invPolicyNo

Hmm...I still don't really understand where the .Parameter property comes
in,
what it is replacing in the QBE grid.

Right now, my Update query works fine if there is only one(!) record in the
Invoice file.
For example, Update PolicyEffectiveDate with the value InvoiceEffectiveDate,
etc.

Do I take out all the column matchups in the QBE grid and use qdf.Parameters
instead?
Then all I am left with is - UPDATE tblPolicy SET - in my query. Can you do
that?!

Maybe - if I have stated here what I want to do clearly enough - you can
tell me how the .Parameter property works.
Does the .Parameter code tells what 'SET's to occur?
And should I still be using action queries, or not?
Click to expand...


I'm not sure you want to use Update queries for everything
you said you want to do, but that's as good an example as
anything. And, Yes, any query that does something to the
table is an action query. A Select query only retrieves
data from one or more tables.

A parameter in a query is a name used somewhere in the query
that is not the name of a field name in the table that the
query is based on. (For the sake of clarity, I will not try
to replicate the query design grid in a text message.
Instead we will use totally unambiguous SQL statements.)
Your Update query would look something like:

UPDATE tblPolicy
SET PolicyEffectiveDate = IED
WHERE PolicyID = PID

In this example IED and PID are parameters (assuming they
are not fields in tblPolicy). Don't do it, but if you ran
that query from SQL view, you would be prompted for the two
parameter values.

Back to your VBA procedure. Let's assume that you saved
that query with the name FixDate, then the code to set the
parameters and run the query would be:

Dim db As DAO.Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs!FixDate
. . .
Set qdf.Parameters!IED = rsInvoice!InvoiceEffectiveDate
Set qdf.Parameters!PID = rsInvoice!InvPolicyNo
qdf.Execute 'dbFailOnError in case InvPolicyNo is not found
MsgBox "Policy " & rsInvoice!InvPolicyNo & _
" updated to EffectiveDate: " & _
Format(rsInvoice!InvoiceEffectiveDate, "d mmm yyyy") & _
" in " & qdf.RecordsAffected & " records."
. . .
Set qdf = Nothing
Set db = Nothing
 
Hi -

So if I build an SQL statement using parameters in the query SQL View, and
set these parameters in the code before
I call the query, then I don't have to worry about what I am doing in the
query Design View (with the QBE grid).

I see that the parameters are used for both purposes:
(1) some parameter = the actual value that goes in the field of the new row
of the table
(2) take the values from the row (of the old table) with index = some
parameter

Does this how I create the action query and use it?

Thanks,

Joy
 
Joy said:
So if I build an SQL statement using parameters in the query SQL View, and
set these parameters in the code before
I call the query, then I don't have to worry about what I am doing in the
query Design View (with the QBE grid).

I see that the parameters are used for both purposes:
(1) some parameter = the actual value that goes in the field of the new row
of the table
(2) take the values from the row (of the old table) with index = some
parameter

Does this how I create the action query and use it?
Click to expand...


You can use any view you want to create the query, you just
have to know how to get the correct result. I frequently
start out using the grid to get Access to construct the bulk
of the SQL statement and then switch to SQL view for making
small adjustments. We are using SQL view here just because
it is very difficult to communicate the grid view in text
messages.

As I said before, a query parameter is any name that is not
a field in the query's base table.

That's one way to create an action query. You could use VBA
to construct the SQL statement as a string and then Execute
that, but that approach would involve you in learning more
about VBA than you need at this point.

There are also other ways to use use an action query, but
the Execute method is more flexible and gives you more
control over how it runs.

When you try this kind of thing, be sure to test it all on a
copy of your tables. A mistake in an action query can
really make a mess of things.
 
Marshall,

I am looking for some information and came across this thread. You had said
"If possible, it is much, much faster to use a few well designed Update
queries to do the work instead of looping through a large recordset."

I am grabbing some info from an Excel file and building an SQL INSERT INTO
statement to Append the table in my Access Database. It runs kind of slow
and I have been out here trying to find a better way. After each rows of
information from Excel I use db.Execute strSQL, then I set the strSQL = ""
and rewrite it for the next row of the Excel workbook.

Does doing this with the UPDATE over and over see efficent? Or is there a
way to make a mass UPDATE that I could loop down each row and run the query
once?

Thanks in Advance for any assistiance you are able to provide.

-Steve
 
Steven said:
Marshall,
I am looking for some information and came across this thread. You had said
"If possible, it is much, much faster to use a few well designed Update
queries to do the work instead of looping through a large recordset."

I am grabbing some info from an Excel file and building an SQL INSERT INTO
statement to Append the table in my Access Database. It runs kind of slow
and I have been out here trying to find a better way. After each rows of
information from Excel I use db.Execute strSQL, then I set the strSQL = ""
and rewrite it for the next row of the Excel workbook.

Does doing this with the UPDATE over and over see efficent? Or is there a
way to make a mass UPDATE that I could loop down each row and run the query
once?
Click to expand...


If the Excel data is in a named Range, then you can tell
Access to link to it as an external table (File - Get
External Data - Link table). Then you can refer to that
table in a query pretty much the same as a local table:

INSERT INTO accesstable(flda,flsb,...)
SELECT col1,col2,...
FROM linkedexcelrange

If your data is not in a nice regular named range, then you
will probable have to loop throught the excel data in
whatever way you need, which is bound to be slower than a
single query that does it all. In this situation, I would
explore opening a recordset to the destination table and
using the .New and .Update methods to insert the new records
instead of executing a query for each record.
 
Marshall,

Took your advise and used the .AddNew - it is a bit quicker...

To answer some of your questions, it's not a nice named range. I open the
Excel workbook and apply a filter to it based on criteria from an Access
form. Once the filter is applied I copy the
Selection.SpecialCells(xlCellTypeVisible).Select and paste that into a new
workbook which I then loop through to send to Access. What I was once doing
with several lines of code to build the SQL statement with the proper
wrapping for text and numbers is now a simple For Next Array... Thanks...

For those searching this is the code I came up with.

Set xlsApp = CreateObject("Excel.Application")

xlsApp.Visible = False

'Open Workbook as Read-Only
xlsApp.WorkBooks.Open strPath, UpdateLinks:=0, ReadOnly:=True
xlsApp.Sheets("Master BOM Sheet").Select
'Turn off filter if on.
If xlsApp.Sheets("Master BOM Sheet").FilterMode = True Then
xlsApp.ActiveSheet.ShowAllData
End If
'Set my filter based on Quarter requested.
xlsApp.Selection.AutoFilter Field:=3, Criteria1:=strCriteria1,
Operator:=1, Criteria2:=strCriteria2
End_Row = xlsApp.Cells(xlsApp.Rows.Count, 1).End(-4162).Row
xlsApp.Range(xlsApp.Cells(26, 1), xlsApp.Cells(End_Row,
38)).SpecialCells(12).Select
xlsApp.Selection.Copy
xlsApp.WorkBooks.Add
xlsApp.Selection.PasteSpecial Paste:=-4163
xlsApp.Application.CutCopyMode = False
End_Row = xlsApp.Cells(xlsApp.Rows.Count, 1).End(-4162).Row
intRcount = 2

'Clear any old data
db.Execute "DELETE * FROM tblDMLifeCycle;"
Set rs = db.OpenRecordset("tblDMLifeCycle")

Do
'Insert data into the table
intCount = 1
'Load Array with values from Excel
For i = 0 To 37
varExcelInfo(i) = xlsApp.Cells(intRcount, intCount)
intCount = intCount + 1
Next

rs.AddNew
'Take values from Excel Array and plug into Access table.
For i = 0 To 37
rs.Fields(i) = varExcelInfo(i)
Next

rs.Update
'Move down Excel workbook.
intRcount = intRcount + 1
Erase varExcelInfo()
Loop Until intRcount > End_Row

Do While xlsApp.WorkBooks.Count > 0
xlsApp.WorkBooks(1).Close False 'close without saving
Loop

xlsApp.Quit

Set xlsApp = Nothing
Set db = Nothing
Set rs = Nothing
 
You are way beyond me with your Excel code, but I see that
you are placing the filtered data into a new sheet, so it
does end up in a table like range. How about giving it a
try with this kind of query after you copy the filtered
cells to the temp workbook:

strSQL = "INSERT INTO tblDMLifeCycle " & _
"SELECT * " & _
"FROM [Sheet1$] " & _
"IN '' [Excel 5.0;HDR=NO;DATABASE=" & strPath & "]"

Not sure how you identify the workbook in the MDI, but I
think you can use Access's Import - Link to create a linked
table named junk in the temp woorkbook. You can then see
connect string needed for the query's IN clause by using the
debug window:
?CurrentDb.TableDefs!junk.Connect
 
Back
Top