Convert C-style long to access field value?

[eselk]

I hardly ever use VBA, so I'm not sure on the syntax, and I'm sure
everything else I'm doing is wrong also (but it works, so I'm not
worried about that for now).

I use Open to open a binary file. I then use Input to read in 1024
bytes, which is one record. I then parse through the data and set
field values for a table in my Access database. I do something like
this:

buffer = Input 1, 1024
offset = 0
for x = 0 to RecordSet.Fields(x).Count - 1
field_size = RecordSet.Fields(x).FieldSize
RecordSet.Fields(x).Value = mid(buffer,offset,field_size)
offset = offset + field_size
next x

It works fine for Text fields, but I'm also trying to get it to work
for "Large Integer" and "Integer" fields. But I'm not sure on the
correct syntax. This is what I tried, but it didn't work:

dim l as long
l = asc(mid(buffer,1,1)) * 256 * 256 * 256
l = l + (asc(mid(buffer,2,1)) * 256 * 256)
l = l + (asc(mid(buffer,3,1)) * 256)
l = l + asc(mid(buffer,4,1))

Anyway, when all of you VBA coders stop laughing and pick yourself up
off the floor (after seeing my awful code), can you please tell me the
correct syntax.

"buffer" is a string returned by Input, which contains a C-style long
value which is stored as 4 bytes, the first byte being the most
signifigant. If I was doing this in C I'd have no problems, since I
could just do this:

long l;
memcpy(&l,buffer,sizeof(l));

What I'm trying to do with all of this is write some code that will
read in a proprietary data format and dump the data into my Access
table. I'd rather do it this way than convert to DBF or ASCII and then
import.

 

[John Nurick]

Is this an endian issue? You say that your data contains a C-style long
with the most significant byte first (i.e. big-endian), and your VBA
code reflects that; but your C snippet appears to do a byte-by-byte copy
in memory, and if that produces the correct results on an Intel
processor then the data must be little-endian.

Also, don't forget that VB longs are signed; you may need to keep an eye

 

[John Nurick]

PS: the VBA LSet statement offers some limited memory-copying powers. I
couldn't work out how to apply it in this situation; I feel that this
ought to work but it doesn't seem to:

Type tString4
S As String * 4
End Type

Type tLong
L As Long
End Type

Public Function StringToLong(S As String) As Long
Dim SS As tString4
Dim LL As tLong
Dim R As String * 4

SS.S = S
LSet LL = SS
StringToLong = LL.L
End Function

 

[eselk]

Is this an endian issue? You say that your data contains a C-style long
with the most significant byte first (i.e. big-endian), and your VBA
code reflects that; but your C snippet appears to do a byte-by-byte copy
in memory, and if that produces the correct results on an Intel
processor then the data must be little-endian.

I must have been in a hurry, because I had it backwards, my data was in
little-endian format. That is also why I didn't notice that my code
with the asc(mid(string,1,1)) was working, just in reverse order. I
probably would have noticed this while debugging if I could get the VBA
debugger to show me hex values for each byte in a string, instead of
those little boxes for every non-ascii char. Any way to see this in
the debugger, for a string? In the meantime I broke out my equation
into multiple lines, so that I could check the value at each step (see
what asc() was returning for each byte).

Also, don't forget that VB longs are signed; you may need to keep an eye
on the most significant bit.

Thanks, that is good to know.

 

[John Nurick]

I
probably would have noticed this while debugging if I could get the VBA
debugger to show me hex values for each byte in a string, instead of
those little boxes for every non-ascii char. Any way to see this in
the debugger, for a string?

Only circuitously, e.g.

Function StringToHex(ByVal S As String) As String
Dim j As Long
Dim strHex As String

For j = 1 To Len(S)
strHex = strHex & Right("0000" & Hex(AscW(Mid(S, j, 1))), 4) & " "
Next
StringToHex = strHex
End Function

and then using debugging statements
...
Debug.Print StringToHex(MyString)
...
(VBA now has conditional compilation: see #Const and #If)