Asp Forum - Is there a faster way of copying one record from one ADO RecordSet to another?

(Mike Mitchell)

9/3/2011 6:03:00 AM

Currently I have rsSource and rsDest. I have a number of records in
rsSource, and rsDest starts out empty and disconnected. (I create
rsDest from scratch in VB code.)

I need to check something in rsSource's records first, then add each
one that passes the check to rsDest.

I am doing this with:

rsDest.AddNew
rsDest.Fields("AccountNumber") = rsSource.Fields("AccountNumber")
rsDest.Fields("UserName") = rsSource.Fields("UserName")
.... etc etc
rsDest.Update

But this seems inordinately slow.

Is there a quicker way I can say "Copy record #34 from rsSource to
rsDest"?

MM

9 Answers

GS

9/3/2011 2:51:00 PM

MM was thinking very hard :
> Currently I have rsSource and rsDest. I have a number of records in
> rsSource, and rsDest starts out empty and disconnected. (I create
> rsDest from scratch in VB code.)
>
> I need to check something in rsSource's records first, then add each
> one that passes the check to rsDest.
>
> I am doing this with:
>
> rsDest.AddNew
> rsDest.Fields("AccountNumber") = rsSource.Fields("AccountNumber")
> rsDest.Fields("UserName") = rsSource.Fields("UserName")
> ... etc etc
> rsDest.Update
>
> But this seems inordinately slow.
>
> Is there a quicker way I can say "Copy record #34 from rsSource to
> rsDest"?
>
> MM

Would it help if you put the data into an array (instead of rsDest) and
then 'dump' the array into the table or rs?

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Henning

9/3/2011 4:00:00 PM

"MM" <kylix_is@yahoo.co.uk> skrev i meddelandet
news:odg367tjhosdrca0i4dkvakpob9do4foh1@4ax.com...
> Currently I have rsSource and rsDest. I have a number of records in
> rsSource, and rsDest starts out empty and disconnected. (I create
> rsDest from scratch in VB code.)
>
> I need to check something in rsSource's records first, then add each
> one that passes the check to rsDest.
>
> I am doing this with:
>
> rsDest.AddNew
> rsDest.Fields("AccountNumber") = rsSource.Fields("AccountNumber")
> rsDest.Fields("UserName") = rsSource.Fields("UserName")
> ... etc etc
> rsDest.Update
>
> But this seems inordinately slow.
>
> Is there a quicker way I can say "Copy record #34 from rsSource to
> rsDest"?
>
> MM

Will it be faster if you read the complete source record into variables, and
then use a SQL Query to add it to destination? You might already read into
variables for checking.

strSQL = "INSERT INTO `dest.table` VALUES (var1, var2, var3...);
conn.EXECUTE strSQL

/Henning

Henning

9/3/2011 4:05:00 PM

"Henning" <computer_hero@coldmail.com> skrev i meddelandet
news:j3tit5$635$1@dont-email.me...
>
> "MM" <kylix_is@yahoo.co.uk> skrev i meddelandet
> news:odg367tjhosdrca0i4dkvakpob9do4foh1@4ax.com...
>> Currently I have rsSource and rsDest. I have a number of records in
>> rsSource, and rsDest starts out empty and disconnected. (I create
>> rsDest from scratch in VB code.)
>>
>> I need to check something in rsSource's records first, then add each
>> one that passes the check to rsDest.
>>
>> I am doing this with:
>>
>> rsDest.AddNew
>> rsDest.Fields("AccountNumber") = rsSource.Fields("AccountNumber")
>> rsDest.Fields("UserName") = rsSource.Fields("UserName")
>> ... etc etc
>> rsDest.Update
>>
>> But this seems inordinately slow.
>>
>> Is there a quicker way I can say "Copy record #34 from rsSource to
>> rsDest"?
>>
>> MM
>
> Will it be faster if you read the complete source record into variables,
> and then use a SQL Query to add it to destination? You might already read
> into variables for checking.
>
> strSQL = "INSERT INTO `dest.table` VALUES (var1, var2, var3...);
> conn.EXECUTE strSQL
>
> /Henning
>

To fast on post...
strSQL = "INSERT INTO desttable (field1, field2, field3...) VALUES (var1,
var2, var3...);"

/Henning

(Mike Mitchell)

9/3/2011 5:50:00 PM

On Sat, 03 Sep 2011 10:51:08 -0400, GS <gs@somewhere.net> wrote:

>MM was thinking very hard :
>> Currently I have rsSource and rsDest. I have a number of records in
>> rsSource, and rsDest starts out empty and disconnected. (I create
>> rsDest from scratch in VB code.)
>>
>> I need to check something in rsSource's records first, then add each
>> one that passes the check to rsDest.
>>
>> I am doing this with:
>>
>> rsDest.AddNew
>> rsDest.Fields("AccountNumber") = rsSource.Fields("AccountNumber")
>> rsDest.Fields("UserName") = rsSource.Fields("UserName")
>> ... etc etc
>> rsDest.Update
>>
>> But this seems inordinately slow.
>>
>> Is there a quicker way I can say "Copy record #34 from rsSource to
>> rsDest"?
>>
>> MM
>
>Would it help if you put the data into an array (instead of rsDest) and
>then 'dump' the array into the table or rs?

I've taken a different approach altogether. I found (took me three
hours of Googling) how to use ADOX to link an Access table
programmatically from an external mdb to the main mdb, and then I was
able to 'hook' the linked table into a single parameter query. So,
with a (for me!) VERY complex bit of SQL I search the index and get
the data in one operation, and it's very fast indeed.

Result: Retrieve 11,000 records on two search terms (using AND) was
taking 35 secs. Now it takes 1426 ms!

This is on a 2.8 Sempron and ABit mobo from circa 2008 with approx
458 MB RAM and Windows 2000.

MM

GS

9/3/2011 8:02:00 PM

MM explained :
> On Sat, 03 Sep 2011 10:51:08 -0400, GS <gs@somewhere.net> wrote:
>
> I've taken a different approach altogether. I found (took me three
> hours of Googling) how to use ADOX to link an Access table
> programmatically from an external mdb to the main mdb, and then I was
> able to 'hook' the linked table into a single parameter query. So,
> with a (for me!) VERY complex bit of SQL I search the index and get
> the data in one operation, and it's very fast indeed.
>
> Result: Retrieve 11,000 records on two search terms (using AND) was
> taking 35 secs. Now it takes 1426 ms!
>
> This is on a 2.8 Sempron and ABit mobo from circa 2008 with approx
> 458 MB RAM and Windows 2000.
>
> MM

Sounds good! Care to share your approach...?

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

(Mike Mitchell)

9/4/2011 7:49:00 AM

On Sat, 03 Sep 2011 16:02:26 -0400, GS <gs@somewhere.net> wrote:

>MM explained :
>> On Sat, 03 Sep 2011 10:51:08 -0400, GS <gs@somewhere.net> wrote:
>>
>> I've taken a different approach altogether. I found (took me three
>> hours of Googling) how to use ADOX to link an Access table
>> programmatically from an external mdb to the main mdb, and then I was
>> able to 'hook' the linked table into a single parameter query. So,
>> with a (for me!) VERY complex bit of SQL I search the index and get
>> the data in one operation, and it's very fast indeed.
>>
>> Result: Retrieve 11,000 records on two search terms (using AND) was
>> taking 35 secs. Now it takes 1426 ms!
>>
>> This is on a 2.8 Sempron and ABit mobo from circa 2008 with approx
>> 458 MB RAM and Windows 2000.
>>
>> MM
>
>Sounds good! Care to share your approach...?

I will in detail when I've completed the prototype. Basically, it's
very simple: Take the text field on which you want to search as
quickly as possible, separate the individual words, e.g. the, cat,
sat, on, the, mat, then remove noise words on, the etc. Remove
punctuation noise as well, like $£"!;@ etc. Then add each word to a
separate mdb once only in a table (with index in ascending order)
called Words. Have another table called RecordNumbers and each time a
word is added, also add the record number of the record from which the
word was extracted. When another occurrence of the same word is found,
append just its record number to the RecordNumbers table.

Searching: Accept one or more words from the user (at present I'm only
testing with a maximum of two words). Pass the word(s) through the
same rules as when constructing the index. Search for each word in the
index and retrieve the corresponding pointers from the RecordNumbers
table. In an AND conjunction, the record numbers will be the same.Then
finally retrieve the records in question and display them.

MM

GS

9/4/2011 5:15:00 PM

Thank you! Would the following function be useful in your quest?

Function FilterString(ByVal TextIn As String, _
Optional IncludeChars As String, _
Optional IncludeLetters As Boolean = True, _
Optional IncludeNumbers As Boolean = True) As
String
' Filters out all unwanted characters in a string.
' Arguments: TextIn The string being filtered.
' IncludeChars [Optional] Any characters you want to
keep.
' IncludeLetters [Optional] Keeps any letters.
Default=True
' IncludeNumbers [Optional] Keeps any numbers.
Default=True
'
' Returns: String containing only the wanted characters.

Const sSource As String = "FilterString()"

'The basic characters to always keep
Const sLetters As String = "abcdefghijklmnopqrstuvwxyz"
Const sNumbers As String = "0123456789"

Dim i As Long
Dim sRet As String, CharsToKeep As String

CharsToKeep = IncludeChars
If IncludeLetters Then CharsToKeep = CharsToKeep & sLetters &
UCase(sLetters)
If IncludeNumbers Then CharsToKeep = CharsToKeep & sNumbers
'From a Balena sample
For i = 1 To Len(TextIn)
If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then sRet = sRet &
Mid$(TextIn, i, 1)
Next
FilterString = sRet

End Function 'FilterString()

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

(Mike Mitchell)

9/4/2011 5:22:00 PM

On Sun, 04 Sep 2011 13:15:12 -0400, GS <gs@somewhere.net> wrote:

>Thank you! Would the following function be useful in your quest?
>
>Function FilterString(ByVal TextIn As String, _
> Optional IncludeChars As String, _
> Optional IncludeLetters As Boolean = True, _
> Optional IncludeNumbers As Boolean = True) As
>String
>' Filters out all unwanted characters in a string.
>' Arguments: TextIn The string being filtered.
>' IncludeChars [Optional] Any characters you want to
>keep.
>' IncludeLetters [Optional] Keeps any letters.
>Default=True
>' IncludeNumbers [Optional] Keeps any numbers.
>Default=True
>'
>' Returns: String containing only the wanted characters.
>
>Const sSource As String = "FilterString()"
>
> 'The basic characters to always keep
> Const sLetters As String = "abcdefghijklmnopqrstuvwxyz"
> Const sNumbers As String = "0123456789"
>
> Dim i As Long
> Dim sRet As String, CharsToKeep As String
>
> CharsToKeep = IncludeChars
> If IncludeLetters Then CharsToKeep = CharsToKeep & sLetters &
>UCase(sLetters)
> If IncludeNumbers Then CharsToKeep = CharsToKeep & sNumbers
> 'From a Balena sample
> For i = 1 To Len(TextIn)
> If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then sRet = sRet &
>Mid$(TextIn, i, 1)
> Next
> FilterString = sRet
>
>End Function 'FilterString()

At some point I will be revisiting the rules function. At present, I'm
simply using Replace. Sometimes I want to delete a character or
characters altogether, and sometimes replace one character with a
space or a different character.

MM

GS

9/4/2011 6:19:00 PM

MM used his keyboard to write :
> On Sun, 04 Sep 2011 13:15:12 -0400, GS <gs@somewhere.net> wrote:
>
>> Thank you! Would the following function be useful in your quest?
>>
>> Function FilterString(ByVal TextIn As String, _
>> Optional IncludeChars As String, _
>> Optional IncludeLetters As Boolean = True, _
>> Optional IncludeNumbers As Boolean = True) As
>> String
>> ' Filters out all unwanted characters in a string.
>> ' Arguments: TextIn The string being filtered.
>> ' IncludeChars [Optional] Any characters you want to
>> keep.
>> ' IncludeLetters [Optional] Keeps any letters.
>> Default=True
>> ' IncludeNumbers [Optional] Keeps any numbers.
>> Default=True
>> '
>> ' Returns: String containing only the wanted characters.
>>
>> Const sSource As String = "FilterString()"
>>
>> 'The basic characters to always keep
>> Const sLetters As String = "abcdefghijklmnopqrstuvwxyz"
>> Const sNumbers As String = "0123456789"
>>
>> Dim i As Long
>> Dim sRet As String, CharsToKeep As String
>>
>> CharsToKeep = IncludeChars
>> If IncludeLetters Then CharsToKeep = CharsToKeep & sLetters &
>> UCase(sLetters)
>> If IncludeNumbers Then CharsToKeep = CharsToKeep & sNumbers
>> 'From a Balena sample
>> For i = 1 To Len(TextIn)
>> If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then sRet = sRet &
>> Mid$(TextIn, i, 1)
>> Next
>> FilterString = sRet
>>
>> End Function 'FilterString()
>
> At some point I will be revisiting the rules function. At present, I'm
> simply using Replace. Sometimes I want to delete a character or
> characters altogether, and sometimes replace one character with a
> space or a different character.
>
> MM

I understand! Thanks for clarifying...

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Is there a faster way of copying one record from one ADO RecordSet to another?

(Mike Mitchell)

GS

Henning

Henning

(Mike Mitchell)

GS

(Mike Mitchell)

GS

(Mike Mitchell)

GS

x Login to ForumsZone