Prev: GDI32 functions
Next: UDT in POM as Parameter
From: John Simpson on 24 Mar 2010 12:25
Help!!!

I have a situation where I am re-populating elements in a
user-defined type to update an Access database table row.

I have redefined the type as a string 'strNewData', but I don't have
a clue how to load the recordset row with strNewData as a
single string, if indeed it can be done. I hope that the above
makes some kind of sense. I've done this a million times by
loading each column separately, but I'm trying to minimize
and optomize the code.

Here's what I have.

with jcvend
.lockedits = false
.edit
.???? = strNewData
.update
end with

Any help will be greatly appreciated.

John

__________ Information from ESET Smart Security, version of virus signature database 4971 (20100324) __________

The message was checked by ESET Smart Security.

http://www.eset.com



From: Paul Clement on 24 Mar 2010 13:28
On Wed, 24 Mar 2010 12:25:46 -0400, "John Simpson" <jasimp(a)earthlink.net> wrote:

� Help!!!
�
� I have a situation where I am re-populating elements in a
� user-defined type to update an Access database table row.
�
� I have redefined the type as a string 'strNewData', but I don't have
� a clue how to load the recordset row with strNewData as a
� single string, if indeed it can be done. I hope that the above
� makes some kind of sense. I've done this a million times by
� loading each column separately, but I'm trying to minimize
� and optomize the code.

You have to add the data one column at a time. I don't know what the data in strNewData looks like
but the code would be something like the following

�
� Here's what I have.
�

with jcvend
.lockedits = false
.edit
.fields("Col1").Value = Value from strNewData
.fields("Col2").Value = Another value from strNewData
.fields("Col3").Value = Still another value from strNewData
.update
end with


Paul
~~~~
Microsoft MVP (Visual Basic)
From: Jeff Johnson on 24 Mar 2010 13:34
"John Simpson" <jasimp(a)earthlink.net> wrote in message
news:%23pHfm62yKHA.4492(a)TK2MSFTNGP05.phx.gbl...

> I have a situation where I am re-populating elements in a
> user-defined type to update an Access database table row.
>
> I have redefined the type as a string 'strNewData', but I don't have
> a clue how to load the recordset row with strNewData as a single string,
> if indeed it can be done. I hope that the above
> makes some kind of sense. I've done this a million times by
> loading each column separately, but I'm trying to minimize
> and optomize the code.
>
> Here's what I have.
>
> with jcvend
> .lockedits = false
> .edit
> .???? = strNewData
> .update
> end with
>
> Any help will be greatly appreciated.

If I understand you correctly, you have the contents of a structure inside a
single string. Supposedly there is some sort of delimiter. Now you need to
break this string back apart because the data is stored in the database in
multiple columns, each corresponding to an element of the structure. If all
this is correct, then the answer is: you're screwed. You're going to have to
parse out the values yourself and assign them to the individual columns. Of
course, this could be handled in a moderately automated fashion, such as by
breaking the string into an array and then having a mapping between the
array indices and the column indices of the table, but there's simply no
built-in DAO function that will do this for you, which is what I assume you
were hoping for.


From: Helmut Meukel on 24 Mar 2010 15:50
"Jeff Johnson" <i.get(a)enough.spam> schrieb im Newsbeitrag
news:eeSM5g3yKHA.5360(a)TK2MSFTNGP06.phx.gbl...
> "John Simpson" <jasimp(a)earthlink.net> wrote in message
> news:%23pHfm62yKHA.4492(a)TK2MSFTNGP05.phx.gbl...
>
>> I have a situation where I am re-populating elements in a
>> user-defined type to update an Access database table row.
>>
>> I have redefined the type as a string 'strNewData', but I don't have
>> a clue how to load the recordset row with strNewData as a single string, if
>> indeed it can be done. I hope that the above
>> makes some kind of sense. I've done this a million times by
>> loading each column separately, but I'm trying to minimize
>> and optomize the code.
>>
>> Here's what I have.
>>
>> with jcvend
>> .lockedits = false
>> .edit
>> .???? = strNewData
>> .update
>> end with
>>
>> Any help will be greatly appreciated.
>
> If I understand you correctly, you have the contents of a structure inside a
> single string. Supposedly there is some sort of delimiter. Now you need to
> break this string back apart because the data is stored in the database in
> multiple columns, each corresponding to an element of the structure. If all
> this is correct, then the answer is: you're screwed. You're going to have to
> parse out the values yourself and assign them to the individual columns. Of
> course, this could be handled in a moderately automated fashion, such as by
> breaking the string into an array and then having a mapping between the array
> indices and the column indices of the table, but there's simply no built-in
> DAO function that will do this for you, which is what I assume you were hoping
> for.


Hi John,

I've used DAO since Access 2.0, never used RDO or ADO, and I ever
missed a function to populate a recordset from a corresponding UDT or
vice versa.

I once found a tip about using the 16-bit API function Hmemcpy to copy
the content of a record into a string variable and then from there into the UDT.
Or the other way round.
That looked fine so I run a test with it:
Record with 27 fields, 1000 times. VB3 compiled, 386DX-40, 20 MB RAM
210 sec for the API solution
7.67 sec assigning each field separately to the corresponding UDT.fields.
I was totally disappointed and never repeated the test in 32 bit on a modern
machine.

In fact, reading your post and the answers I remembered faintly this API
solution and searched my code archive.
I didn'd find all the code I used, I had only saved one part together with a
short note about the test results. I had even converted ths part to VB5.

Here is this snippet:

Declare Sub CopyMemory Lib "KERNEL32" Alias "RtlMoveMemory" _
(hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)

Function GetCurrRec(rs As Recordset) As String
Dim i As Integer
Static FieldStr As String
Static recStr As String

recStr = ""
'Step through each field in the current record and accumulate
'the contents of each field into a string
For i = 0 To rs.Fields.Count - 1
'Pad out to the right size
FieldStr = Space(rs.Fields(i).Size)
Select Case rs.Fields(i).Type
'Copy the binary representation of the field to a
'string (FieldStr)
Case 1, 2 'Bytes
CopyMemory ByVal FieldStr, CInt(rs.Fields(i).Value),
rs.Fields(i).Size
Case 3 'Integers
CopyMemory ByVal FieldStr, CInt(rs.Fields(i).Value),
rs.Fields(i).Size
Case 4 'Long integers
CopyMemory ByVal FieldStr, CLng(rs.Fields(i).Value),
rs.Fields(i).Size
Case 5 'Currency
CopyMemory ByVal FieldStr, CCur(rs.Fields(i).Value),
rs.Fields(i).Size
Case 6 'Singles
CopyMemory ByVal FieldStr, CSng(rs.Fields(i).Value),
rs.Fields(i).Size
Case 7, 8 'Doubles
CopyMemory ByVal FieldStr, CDbl(rs.Fields(i).Value),
rs.Fields(i).Size
Case 9, 10 'String types
CopyMemory ByVal FieldStr, ByVal CStr(rs.Fields(i).Value),
Len(rs.Fields(i).Value)
Case 11, 12 'Memo and long binary
FieldStr = rs.Fields(i).GetChunk(0, rs.Fields(i).FieldSize())
End Select
'Accumulate the field string into a record string
recStr = recStr & FieldStr
Next
'Return the accumulated string containing the contents of all
'fields in the current record
GetCurrRec = recStr
End Function

Now looking into the code I realize the accumulation of the field strings into
the
record string might have costed the most time.

IIRC, it was an article from Microsoft, so maybe you can get it somewhere.

Helmut.

From: John Simpson on 25 Mar 2010 16:05

"John Simpson" <jasimp(a)earthlink.net> wrote in message
news:%23pHfm62yKHA.4492(a)TK2MSFTNGP05.phx.gbl...
> Help!!!
>
> I have a situation where I am re-populating elements in a
> user-defined type to update an Access database table row.
>
> I have redefined the type as a string 'strNewData', but I don't have
> a clue how to load the recordset row with strNewData as a single string,
> if indeed it can be done. I hope that the above
> makes some kind of sense. I've done this a million times by
> loading each column separately, but I'm trying to minimize
> and optomize the code.
>
> Here's what I have.
>
> with jcvend
> .lockedits = false
> .edit
> .???? = strNewData
> .update
> end with
>
> Any help will be greatly appreciated.
>
> John

Thanks to all that replied. As Jeff Johnson put it, 'I'm screwed!' So
I continue doing the way I've been doing it for years.

John


__________ Information from ESET Smart Security, version of virus signature database 4974 (20100325) __________

The message was checked by ESET Smart Security.

http://www.eset.com



 | 
Pages: 1
Prev: GDI32 functions
Next: UDT in POM as Parameter