When using a Microsoft Access XP dataproject (.adp), how do i
manipulate a large recordset in VBA without having access load the
entire recordset in memory (it is a large recordset with over 2
million records). I have to run some VBA code against my table as SQL
Server doesnt support using user functions within stored procedures,
but using the standard open statement, i get out of memory due to
access trying to load the whole thing into memory. I basically want
access to step through the recordset record by record altering each
one with visual basic. |
Request for Question Clarification by
hammer-ga
on
30 Oct 2002 04:51 PST
Can you post a snippet of your code including your open statement? I'd
like to see what method you are using to get your recordset.
Also, you can do more processing than you might think inside a stored
procedure. You can also call other stored procedures from inside a
stored procedure. Can you detail a bit of what you're trying to do?
Perhaps I can help.
Which version of SQL Server do you use?
|
Clarification of Question by
den2002-ga
on
30 Oct 2002 10:22 PST
SQL Server 7.0
This is just one occurance of why i want to be able to step through my
records, but there are many more occurances when i could need to do
this.
===========================
Function AddAdrKey()
Dim rec1 As New ADODB.Recordset
Dim ProgCtr As Integer
Dim recctr As Integer
On Error Resume Next
rec1.Open "PILDTA", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
ProgCtr = 1
Do Until rec1.EOF
With rec1
![Street] = addresskey(rec1!Address)
.Update
End With
rec1.MoveNext
ProgCtr = ProgCtr + 1
Loop
rec1.Close
End Function
Function addresskey(TextIn As String)
'trims the address number from the address
Dim current As String
Dim ctr As Integer
Dim length As Integer
Dim holder As String
If Nz(TextIn, "") = "" Then
holder = ""
GoTo done
End If
ctr = 1
length = Len(TextIn)
Do Until ctr > length
current = Mid(TextIn, ctr, 1)
Select Case current
Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", " "
holder = holder
Case Else
holder = holder & current
End Select
ctr = ctr + 1
Loop
done:
addresskey = Trim(holder)
End Function
|
Request for Question Clarification by
hammer-ga
on
30 Oct 2002 12:32 PST
First a couple of suggestions:
1. Using On Error Resume Next is asking for trouble. You are not only
giving up your ability to handle errors, you are actually hiding their
occurence. I strongly recommend you put in some simple error handling.
2. Dim rec1 As New ADODB.Recordset is extremely inefficient. You want
to break this into two lines, like so -
Dim rec1 As ADODB.Recordset
Set rec1 = New ADODB.Recordset
I know it doesn't look much different, but VBA will be doing much less
work.
3. You have a memory leak. You are closing your recordset, but you
also need to explicitly deallocate it.
rec1 = Nothing
4. You only actually need two fields from your table. Your memory
problems may go away if you only retrieve those two fields instead of
opening the entire table.
5. Be explicit about your return value types rather than making VBA
try to figure it out. It's faster that way.
Function addresskey(TextIn As String)
would be faster as
Function addresskey(TextIn As String) As String
****************************
OK, now that we've gotten *that* out of the way...
What you describe can be done inside your stored procedure. There is
very good reason to do so. The VBA is not contributing anything to
this process that is not available from SQL Server. You should
generally avoid dragging millions of records across tiers and networks
if you don't need to.
Will you accept a stored procedure showing how to do this as an
answer, or do you still want a VBA way to get a record, or chunk of
records at a time?
|
Clarification of Question by
den2002-ga
on
30 Oct 2002 13:38 PST
I actually need to know how to modify a large SQL Server 7 recordset
with VBA. Not so much for the specific problem of removeing the
numbers (I can think of at least one way to do that with the replace
command). I have existing Access databases that rely on calling user
functions that I want to convert to SQL Server, so this is something I
would like to be able to do.
Thanks.
btw, I dont normally use on error resume next. That got left in there
from something else i was trying. :-)
|
Clarification of Question by
den2002-ga
on
30 Oct 2002 13:39 PST
So the answer to your last question is: I still want to know how to
have Access process a chuck at a time
|
Request for Question Clarification by
hammer-ga
on
31 Oct 2002 05:03 PST
I came in this morning to write you some code and discoved that slk_ga
posted a comment last night that basically describes how you chunk a
recordset. ADP only allows you a client-side cursor. That is why it
drags the entire recordset into memory. You have more choices if you
can use a server-side cursor, but ADP does not make that available.
Therefore, since you are going to get all of what you ask for, you
have to manage your own bookkeeping, by using unique id fields or
special marker fields to get your records in managed chunks.
I'm not sure what your skill level is, and I don't want to charge you
for an answer if slk_ga's comment is enough to answer your question.
Please let me know if you want more detail than the comment provides.
|
Clarification of Question by
den2002-ga
on
31 Oct 2002 06:18 PST
well, the good thing is that most of the time i will be able to get
away with using criteria to chuck my recordsets. the only time i
really have to alter the whole table is when i buy a new list. If ADP
doesn't support Client Side Cursors, what does?
|
