|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
[Access newbie]
I'm trying to export legacy data from an Access 2000 database to
tab-delim (en route to cleaning it in Excel). I've problems as some text
won't export as it was stored in BLOB fields. Why I don't know - it's
only small amounts of plain text - 3-4 paragraphs at max.
I'm wondering if I could add a new memo field to the table in question
and then iterate the table using VB/VBA to read the BLOB text and paste
it into the new field for each record? I've seen code to read BLOB data
at http://support.microsoft.com/?kbid=103257 Once the data's in text
form I can delete the BLOB field and use DoCmd.TransferText to export
the data (I want the text and the other (key) ID field in the table.
Does this idea hold water? I don't need to worry about the health of the
source MDB file once dine, as soon as we've ripped the data
successfully, the MDB's no longer needed. Thus some code I can run from
the VBA IDE will be fine - no fancy controls needed. Indeed, as I know
nothing much about Access (though I'm OK on the VB/VBA side), the less
d/b magic the better <g>. Alternatively, if this can be done in one go -
either to text or Excel, all the better.
Any ideas?
Regards
Mark
|
|
Hi Mark,
The code at KB103257 is overkill for what you describe. If you've just
got small amounts of text to copy from a blob field to a memo you can
use something like this air code:
Dim rsR As DAO.Recordset
'Open recordset with the two fields
Set rsR = CurrentDB.OpenRecordset( _
"SELECT BlobField, MemoField FROM MyTable;")
With rsR
Do Until rsR.EOF
.Edit
.Fields("MemoField").Value = _
.Fields("BlobField").GetChunk(0, 10000)
.Update
Loop
.Close
End With
One thing to be careful about, though. In my experience most software
that can read tab-delimited text files chokes if there are carriage
returns and linefeeds in the data. It's safer to export as CSV.
On Mon, 4 Sep 2006 19:01:36 +0100, "Mark Anderson"
<mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote:
[Quoted Text] >[Access newbie] > >I'm trying to export legacy data from an Access 2000 database to >tab-delim (en route to cleaning it in Excel). I've problems as some text >won't export as it was stored in BLOB fields. Why I don't know - it's >only small amounts of plain text - 3-4 paragraphs at max. > >I'm wondering if I could add a new memo field to the table in question >and then iterate the table using VB/VBA to read the BLOB text and paste >it into the new field for each record? I've seen code to read BLOB data >at http://support.microsoft.com/?kbid=103257 Once the data's in text >form I can delete the BLOB field and use DoCmd.TransferText to export >the data (I want the text and the other (key) ID field in the table. > >Does this idea hold water? I don't need to worry about the health of the >source MDB file once dine, as soon as we've ripped the data >successfully, the MDB's no longer needed. Thus some code I can run from >the VBA IDE will be fine - no fancy controls needed. Indeed, as I know >nothing much about Access (though I'm OK on the VB/VBA side), the less >d/b magic the better <g>. Alternatively, if this can be done in one go - >either to text or Excel, all the better. > >Any ideas? > >Regards > >Mark >
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
John,
"John Nurick" <j.mapSoN.nurick[ at ]dial.pipex.com> wrote in message
news:edvof2dqj52v4hhh2j48mgjiicjbfnll9a[ at ]4ax.com...
[Quoted Text] > Hi Mark,
>
> The code at KB103257 is overkill for what you describe. If you've just
> got small amounts of text to copy from a blob field to a memo you can
> use something like this air code:
>
> Dim rsR As DAO.Recordset
> 'Open recordset with the two fields
> Set rsR = CurrentDB.OpenRecordset( _
> "SELECT BlobField, MemoField FROM MyTable;")
>
> With rsR
> Do Until rsR.EOF
> .Edit
> .Fields("MemoField").Value = _
> .Fields("BlobField").GetChunk(0, 10000)
> .Update
> Loop
> .Close
> End With
>
> One thing to be careful about, though. In my experience most software
> that can read tab-delimited text files chokes if there are carriage
> returns and linefeeds in the data. It's safer to export as CSV.
Thanks, I'll give that a try
Regards
Mark
>
>
> On Mon, 4 Sep 2006 19:01:36 +0100, "Mark Anderson"
> <mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote:
>
>>[Access newbie]
>>
>>I'm trying to export legacy data from an Access 2000 database to
>>tab-delim (en route to cleaning it in Excel). I've problems as some
>>text
>>won't export as it was stored in BLOB fields. Why I don't know - it's
>>only small amounts of plain text - 3-4 paragraphs at max.
>>
>>I'm wondering if I could add a new memo field to the table in question
>>and then iterate the table using VB/VBA to read the BLOB text and
>>paste
>>it into the new field for each record? I've seen code to read BLOB
>>data
>>at http://support.microsoft.com/?kbid=103257 Once the data's in text
>>form I can delete the BLOB field and use DoCmd.TransferText to export
>>the data (I want the text and the other (key) ID field in the table.
>>
>>Does this idea hold water? I don't need to worry about the health of
>>the
>>source MDB file once dine, as soon as we've ripped the data
>>successfully, the MDB's no longer needed. Thus some code I can run
>>from
>>the VBA IDE will be fine - no fancy controls needed. Indeed, as I know
>>nothing much about Access (though I'm OK on the VB/VBA side), the less
>>d/b magic the better <g>. Alternatively, if this can be done in one
>>go -
>>either to text or Excel, all the better.
>>
>>Any ideas?
>>
>>Regards
>>
>>Mark
>>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
|
|
John,
[Quoted Text] > Dim rsR As DAO.Recordset
> 'Open recordset with the two fields
> Set rsR = CurrentDB.OpenRecordset( _
> "SELECT BlobField, MemoField FROM MyTable;")
>
> With rsR
> Do Until rsR.EOF
> .Edit
> .Fields("MemoField").Value = _
> .Fields("BlobField").GetChunk(0, 10000)
> .Update
> Loop
> .Close
> End With
>
Running this fails at the .Fields("MemoField") line with Error 3421
(Data type conversion error). As the Memo field is text, I tried
wrapping right side in a CStr() coercion, i.e.:
= CStr(.Fields("BlobField").GetChunk(0, 10000))
....aiming to coerce the Variant output of GetChunk into text but this
yields Error 3702 (The width of a Unicode text column must be an even
number of bytes).
I also found this code and tried using it to replace the .Fields line:
Function CopyLargeField(fldSource As Field, _
fldDestination As Field)
' Set size of chunk in bytes.
Const conChunkSize = 32768
Dim lngOffset As Long
Dim lngTotalSize As Long
Dim strChunk As String
' Copy the photo from one Recordset to the other in 32K
' chunks until the entire field is copied.
lngTotalSize = fldSource.FieldSize
Do While lngOffset < lngTotalSize
strChunk = fldSource.GetChunk(lngOffset, conChunkSize)
fldDestination.AppendChunk strChunk
lngOffset = lngOffset + conChunkSize
Loop
End Function
But then the parent sub fails at (the same place effectively) at line
CopyLargeField:
With rsR
Do Until rsR.EOF
.Edit
CopyLargeField .Fields("BlobField"), .Fields("MemoField") .Update
Loop
.Close
End With
With error 13 "Type Mismatch".
Am I missing something obvious?
Regards
Mark
|
|
On Tue, 5 Sep 2006 11:19:17 +0100, "Mark Anderson"
<mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote:
[Quoted Text] >John,
>
>> Dim rsR As DAO.Recordset
>> 'Open recordset with the two fields
>> Set rsR = CurrentDB.OpenRecordset( _
>> "SELECT BlobField, MemoField FROM MyTable;")
>>
>> With rsR
>> Do Until rsR.EOF
>> .Edit
>> .Fields("MemoField").Value = _
>> .Fields("BlobField").GetChunk(0, 10000)
>> .Update
>> Loop
>> .Close
>> End With
>>
>
>Running this fails at the .Fields("MemoField") line with Error 3421
>(Data type conversion error).
I'd try something like this, assigning the chunk to a Variant:
Dim varBlob As Variant
...
varBlob = .Fields("BlobField").GetChunk(0,10000)
.Fields("MemoField").Value = cStr(varBlob)
...
Also: are you absolutely certain that the BLOB field actually contains
text and not a "package" (OLE wrapper) containing text?
As the Memo field is text, I tried
>wrapping right side in a CStr() coercion, i.e.:
> = CStr(.Fields("BlobField").GetChunk(0, 10000))
>
>...aiming to coerce the Variant output of GetChunk into text but this
>yields Error 3702 (The width of a Unicode text column must be an even
>number of bytes).
>
>I also found this code and tried using it to replace the .Fields line:
>
>Function CopyLargeField(fldSource As Field, _
> fldDestination As Field)
>
> ' Set size of chunk in bytes.
> Const conChunkSize = 32768
>
> Dim lngOffset As Long
> Dim lngTotalSize As Long
> Dim strChunk As String
>
> ' Copy the photo from one Recordset to the other in 32K
> ' chunks until the entire field is copied.
> lngTotalSize = fldSource.FieldSize
> Do While lngOffset < lngTotalSize
> strChunk = fldSource.GetChunk(lngOffset, conChunkSize)
> fldDestination.AppendChunk strChunk
> lngOffset = lngOffset + conChunkSize
> Loop
>
>End Function
>
>But then the parent sub fails at (the same place effectively) at line
>CopyLargeField:
>
>With rsR
> Do Until rsR.EOF
> .Edit
> CopyLargeField .Fields("BlobField"), .Fields("MemoField") .Update
> Loop
> .Close
>End With
>
>With error 13 "Type Mismatch".
>
>Am I missing something obvious?
>
>Regards
>
>Mark
>
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
John,
[Quoted Text] > I'd try something like this, assigning the chunk to a Variant:
>
> Dim varBlob As Variant
> ...
> varBlob = .Fields("BlobField").GetChunk(0,10000)
> .Fields("MemoField").Value = cStr(varBlob)
> ...
IIRC that's something I tried already <g>, but I'll give it another go.
> Also: are you absolutely certain that the BLOB field actually contains
> text and not a "package" (OLE wrapper) containing text?
No. How might I test for that?. The database is from a defunct image
database called ImageAX - the storage format is a basic Access 2000 MDB.
Regards
Mark
"John Nurick" <j.mapSoN.nurick[ at ]dial.pipex.com> wrote in message
news:j0irf25jfp2bsoe1u0nr3im5miso75get7[ at ]4ax.com...
> On Tue, 5 Sep 2006 11:19:17 +0100, "Mark Anderson"
> <mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote:
>
>>John,
>>
>>> Dim rsR As DAO.Recordset
>>> 'Open recordset with the two fields
>>> Set rsR = CurrentDB.OpenRecordset( _
>>> "SELECT BlobField, MemoField FROM MyTable;")
>>>
>>> With rsR
>>> Do Until rsR.EOF
>>> .Edit
>>> .Fields("MemoField").Value = _
>>> .Fields("BlobField").GetChunk(0, 10000)
>>> .Update
>>> Loop
>>> .Close
>>> End With
>>>
>>
>>Running this fails at the .Fields("MemoField") line with Error 3421
>>(Data type conversion error).
>
> I'd try something like this, assigning the chunk to a Variant:
>
> Dim varBlob As Variant
> ...
> varBlob = .Fields("BlobField").GetChunk(0,10000)
> .Fields("MemoField").Value = cStr(varBlob)
> ...
>
> Also: are you absolutely certain that the BLOB field actually contains
> text and not a "package" (OLE wrapper) containing text?
>
>
> As the Memo field is text, I tried
>>wrapping right side in a CStr() coercion, i.e.:
>> = CStr(.Fields("BlobField").GetChunk(0, 10000))
>>
>>...aiming to coerce the Variant output of GetChunk into text but this
>>yields Error 3702 (The width of a Unicode text column must be an even
>>number of bytes).
>>
>>I also found this code and tried using it to replace the .Fields line:
>>
>>Function CopyLargeField(fldSource As Field, _
>> fldDestination As Field)
>>
>> ' Set size of chunk in bytes.
>> Const conChunkSize = 32768
>>
>> Dim lngOffset As Long
>> Dim lngTotalSize As Long
>> Dim strChunk As String
>>
>> ' Copy the photo from one Recordset to the other in 32K
>> ' chunks until the entire field is copied.
>> lngTotalSize = fldSource.FieldSize
>> Do While lngOffset < lngTotalSize
>> strChunk = fldSource.GetChunk(lngOffset, conChunkSize)
>> fldDestination.AppendChunk strChunk
>> lngOffset = lngOffset + conChunkSize
>> Loop
>>
>>End Function
>>
>>But then the parent sub fails at (the same place effectively) at line
>>CopyLargeField:
>>
>>With rsR
>> Do Until rsR.EOF
>> .Edit
>> CopyLargeField .Fields("BlobField"), .Fields("MemoField")
>> .Update
>> Loop
>> .Close
>>End With
>>
>>With error 13 "Type Mismatch".
>>
>>Am I missing something obvious?
>>
>>Regards
>>
>>Mark
>>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
|
|
Mark,
Something like
...
varBlob = .Fields("BlobField").GetChunk(0,10000)
Debug.Print varBlob
If you get the text you're expecting, then that's what the field
contains.
If you get anything else (e.g. a ream of binary code with the text maybe
discernible towards the end) you've got some kind of package or OLE
object from which you'll have to extract the text. There's some cunning
stuff at www.lebans.com that will help with that.
On Tue, 5 Sep 2006 23:32:43 +0100, "Mark Anderson"
<mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote:
[Quoted Text] >John,
>
>> I'd try something like this, assigning the chunk to a Variant:
>>
>> Dim varBlob As Variant
>> ...
>> varBlob = .Fields("BlobField").GetChunk(0,10000)
>> .Fields("MemoField").Value = cStr(varBlob)
>> ...
>
>IIRC that's something I tried already <g>, but I'll give it another go.
>
>> Also: are you absolutely certain that the BLOB field actually contains
>> text and not a "package" (OLE wrapper) containing text?
>
>No. How might I test for that?. The database is from a defunct image
>database called ImageAX - the storage format is a basic Access 2000 MDB.
>
>Regards
>
>Mark
>
>
>
>"John Nurick" <j.mapSoN.nurick[ at ]dial.pipex.com> wrote in message
>news:j0irf25jfp2bsoe1u0nr3im5miso75get7[ at ]4ax.com...
>> On Tue, 5 Sep 2006 11:19:17 +0100, "Mark Anderson"
>> <mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote:
>>
>>>John,
>>>
>>>> Dim rsR As DAO.Recordset
>>>> 'Open recordset with the two fields
>>>> Set rsR = CurrentDB.OpenRecordset( _
>>>> "SELECT BlobField, MemoField FROM MyTable;")
>>>>
>>>> With rsR
>>>> Do Until rsR.EOF
>>>> .Edit
>>>> .Fields("MemoField").Value = _
>>>> .Fields("BlobField").GetChunk(0, 10000)
>>>> .Update
>>>> Loop
>>>> .Close
>>>> End With
>>>>
>>>
>>>Running this fails at the .Fields("MemoField") line with Error 3421
>>>(Data type conversion error).
>>
>> I'd try something like this, assigning the chunk to a Variant:
>>
>> Dim varBlob As Variant
>> ...
>> varBlob = .Fields("BlobField").GetChunk(0,10000)
>> .Fields("MemoField").Value = cStr(varBlob)
>> ...
>>
>> Also: are you absolutely certain that the BLOB field actually contains
>> text and not a "package" (OLE wrapper) containing text?
>>
>>
>> As the Memo field is text, I tried
>>>wrapping right side in a CStr() coercion, i.e.:
>>> = CStr(.Fields("BlobField").GetChunk(0, 10000))
>>>
>>>...aiming to coerce the Variant output of GetChunk into text but this
>>>yields Error 3702 (The width of a Unicode text column must be an even
>>>number of bytes).
>>>
>>>I also found this code and tried using it to replace the .Fields line:
>>>
>>>Function CopyLargeField(fldSource As Field, _
>>> fldDestination As Field)
>>>
>>> ' Set size of chunk in bytes.
>>> Const conChunkSize = 32768
>>>
>>> Dim lngOffset As Long
>>> Dim lngTotalSize As Long
>>> Dim strChunk As String
>>>
>>> ' Copy the photo from one Recordset to the other in 32K
>>> ' chunks until the entire field is copied.
>>> lngTotalSize = fldSource.FieldSize
>>> Do While lngOffset < lngTotalSize
>>> strChunk = fldSource.GetChunk(lngOffset, conChunkSize)
>>> fldDestination.AppendChunk strChunk
>>> lngOffset = lngOffset + conChunkSize
>>> Loop
>>>
>>>End Function
>>>
>>>But then the parent sub fails at (the same place effectively) at line
>>>CopyLargeField:
>>>
>>>With rsR
>>> Do Until rsR.EOF
>>> .Edit
>>> CopyLargeField .Fields("BlobField"), .Fields("MemoField")
>>> .Update
>>> Loop
>>> .Close
>>>End With
>>>
>>>With error 13 "Type Mismatch".
>>>
>>>Am I missing something obvious?
>>>
>>>Regards
>>>
>>>Mark
>>>
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
John,
[Quoted Text] > Something like
> ...
> varBlob = .Fields("BlobField").GetChunk(0,10000)
> Debug.Print varBlob
>
> If you get the text you're expecting, then that's what the field
> contains.
Bingo! I get row after row of "?????????" and then Access hangs (app not
responging). The source table has noly 20 records, FWIW.
> If you get anything else (e.g. a ream of binary code with the text
maybe
> discernible towards the end) you've got some kind of package or OLE
> object from which you'll have to extract the text. There's some
> cunning
> stuff at www.lebans.com that will help with that.
>
Great, I got the ExtractInventoryOLE project from the Lebans site and
ran it. It opened/configured OK but when run I unfortunately get a
messagebox "Overflow", one per record. I'm running it on Access
2003/XP - I wonder if that matters?
Does Stephen Lebans read this N/G or should I start-over in a new one to
see if I can debug the above?
Thanks for all help thus far, I *really* appreciate it.
Regards
Mark
|
|
|
|
ASCII Text is being stored in the BLOB(Long Binary Field). I'm sure there
are many ways to convert it but this worked the first time. I have imported
the relevant table from your MDB into the ExtractInventoryOLE MDB. I then
changed the Record Source prop of the ExtractOLEobjectFromTable form to
point to your IaxText table. I then changed the Control Source of the OLEPIC
Bound OLE Frame control to point to the DESCTEXT field of the IaxText table.
Finally I modifed the existing Sub behind the ExtractOLEobjectFromTable form
to this:
Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
Dim a() As Byte
Dim b() As Byte
Dim x As Long
Dim lTemp As Long
Dim sl As String
Dim blRet As Boolean
Dim sExt As String
Dim sFileExist As String
' This is an optional param we pass to fGetContentsStream.
' It will contain the original file name of the
' object when embedded as a Package.
Dim PackageFileName As String
Dim iFileHandle As Integer
' Load our Structured Storage Library
' Let's see if the StrStorage.DLL is available.
blRet = LoadLib()
If blRet = False Then
' Cannot find StrStorage.dll file
Exit Sub
End If
lTemp = LenB(Me.OLEPic.Value)
ReDim a(0 To lTemp - 1)
ReDim b(0 To lTemp - 1)
' Copy the contents of the OLE field to our byte array
a = Me.OLEPic.Value
' Make a copy of the original data
b = a
'blRet = fGetContentsStream(a(), sExt, PackageFileName)
'If blRet = True Then
'
' If sExt = "pak" Then
' ' If a file was dragged from the Explorer window
' ' it will have a Package object Filename of NULL
' ' inserted by Shell.DLL
' ' Catch and give a temp file name
' If Len(PackageFileName & vbNullString) < 3 Then
' PackageFileName = "OLE-ExtractDraggedFromExplorer" &
"." & "bmp"
' End If
'
' iFileHandle = FreeFile
' sl = "C:\" & PackageFileName
' sFileExist = Dir(sl)
' If Len(sFileExist & vbNullString) > 0 Then
' Kill sl
' End If
'
' Open sl For Binary Access Write As iFileHandle
' Put iFileHandle, , a
' Close iFileHandle
' Else
'
' iFileHandle = FreeFile
' sl = "C:\" & sExt & UBound(a) & "." & sExt
' sFileExist = Dir(sl)
' If Len(sFileExist & vbNullString) > 0 Then
' Kill sl
' End If
' Open sl For Binary Access Write As iFileHandle
' Put iFileHandle, , a
' Close iFileHandle
' End If
'
'
' Dim StartRegisteredApp As Boolean
'
' StartRegisteredApp = True
' ' Do we open the exported OLE object in the
' ' Application registered for this file type on this system?
' If StartRegisteredApp = True Then
' ' Some apps require vbNullString for the first parameter,
' ' other apps require "open" for the first parameter
' ShellExecuteA Application.hWndAccessApp, vbNullString, sl,
vbNullString, vbNullString, 1
' End If ' "open"
'End If
' Below is for debugging.
' If you have an OLE object that is not currently
' supported by this project then:
' 1) Uncomment out the code below.
' 2) Use this form to try to extract the OLE object.
' 3) Email me the two "dat" files generated below
' Stephen[ at ]lebans.com
' Subject: Unsupported OLE format
iFileHandle = FreeFile
sl = "C:\OLE-field-ALL" & ".dat"
sFileExist = Dir(sl)
If Len(sFileExist & vbNullString) > 0 Then
Kill sl
End If
Open sl For Binary Access Write As iFileHandle
Put iFileHandle, , b
Close iFileHandle
'iFileHandle = FreeFile
'sl = "C:\OLE-field-CONTENTS" & ".dat"
'sFileExist = Dir(sl)
'If Len(sFileExist & vbNullString) > 0 Then
' Kill sl
'End If
Open sl For Binary Access Write As iFileHandle
Put iFileHandle, , a
Close iFileHandle
Dim sTmp As String
'sTmp = StrConv(a, vbNarrow)
For x = 0 To UBound(b)
sTmp = sTmp & Chr(b(x))
Next x
Me.Text12.Value = sTmp
Exit_cmdSave_Click:
' Release structured storage library
UnLoadLib
Exit Sub
Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click
End Sub
--
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"Mark Anderson" <mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote in message
news:e2c9oba0GHA.1568[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > John,
>
>> Something like
>> ...
>> varBlob = .Fields("BlobField").GetChunk(0,10000)
>> Debug.Print varBlob
>>
>> If you get the text you're expecting, then that's what the field
>> contains.
>
> Bingo! I get row after row of "?????????" and then Access hangs (app not
> responging). The source table has noly 20 records, FWIW.
>
> > If you get anything else (e.g. a ream of binary code with the text
> maybe
>> discernible towards the end) you've got some kind of package or OLE
>> object from which you'll have to extract the text. There's some cunning
>> stuff at www.lebans.com that will help with that.
>>
>
> Great, I got the ExtractInventoryOLE project from the Lebans site and ran
> it. It opened/configured OK but when run I unfortunately get a messagebox
> "Overflow", one per record. I'm running it on Access 2003/XP - I wonder if
> that matters?
>
> Does Stephen Lebans read this N/G or should I start-over in a new one to
> see if I can debug the above?
>
> Thanks for all help thus far, I *really* appreciate it.
>
> Regards
>
> Mark
>
>
>
|
|
Stephen,
"Stephen Lebans"
<ForEmailGotoMy.WebSite.-WWWdotlebansdot...[ at ]linvalid.com> wrote in
message news:erNYNtc0GHA.4252[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > ASCII Text is being stored in the BLOB(Long Binary Field). I'm sure
> there are many ways to convert it but this worked the first time. I
> have imported the relevant table from your MDB into the
> ExtractInventoryOLE MDB. I then changed the Record Source prop of the
> ExtractOLEobjectFromTable form to point to your IaxText table. I then
> changed the Control Source of the OLEPIC Bound OLE Frame control to
> point to the DESCTEXT field of the IaxText table.
I modified the ExtractSingleOLEobjectFromTable as it's to only one that
seems to match your "ExtractOLEobjectFromTable form" mentioned above. I
also when I attach the code as below and run the form I notice the
Genus/Code/Row Number boxes all show #Name?. When I click the forms
button it errors out it this line:
Me.Text12.Value = sTmp
with error "Method or Data member not found.
Even if this did run surely I'd have to run this form for every record
and there will be about 150,000+ records to process (albeit split over
several MDBs).
Have I missed a step? Surely it's the
ExtractInventoryAllOLEobjectsFromTable form I should be using.
Regards
Mark
> Finally I modifed the existing Sub behind the
> ExtractOLEobjectFromTable form to this:
>
> Private Sub cmdSave_Click()
> On Error GoTo Err_cmdSave_Click
>
> Dim a() As Byte
> Dim b() As Byte
> Dim x As Long
> Dim lTemp As Long
> Dim sl As String
> Dim blRet As Boolean
> Dim sExt As String
> Dim sFileExist As String
>
> ' This is an optional param we pass to fGetContentsStream.
> ' It will contain the original file name of the
> ' object when embedded as a Package.
> Dim PackageFileName As String
>
> Dim iFileHandle As Integer
>
> ' Load our Structured Storage Library
> ' Let's see if the StrStorage.DLL is available.
> blRet = LoadLib()
> If blRet = False Then
> ' Cannot find StrStorage.dll file
> Exit Sub
> End If
>
> lTemp = LenB(Me.OLEPic.Value)
> ReDim a(0 To lTemp - 1)
> ReDim b(0 To lTemp - 1)
>
> ' Copy the contents of the OLE field to our byte array
> a = Me.OLEPic.Value
>
> ' Make a copy of the original data
> b = a
>
> 'blRet = fGetContentsStream(a(), sExt, PackageFileName)
> 'If blRet = True Then
> '
> ' If sExt = "pak" Then
> ' ' If a file was dragged from the Explorer window
> ' ' it will have a Package object Filename of NULL
> ' ' inserted by Shell.DLL
> ' ' Catch and give a temp file name
> ' If Len(PackageFileName & vbNullString) < 3 Then
> ' PackageFileName =
> "OLE-ExtractDraggedFromExplorer" & "." & "bmp"
> ' End If
> '
> ' iFileHandle = FreeFile
> ' sl = "C:\" & PackageFileName
> ' sFileExist = Dir(sl)
> ' If Len(sFileExist & vbNullString) > 0 Then
> ' Kill sl
> ' End If
> '
> ' Open sl For Binary Access Write As iFileHandle
> ' Put iFileHandle, , a
> ' Close iFileHandle
> ' Else
> '
> ' iFileHandle = FreeFile
> ' sl = "C:\" & sExt & UBound(a) & "." & sExt
> ' sFileExist = Dir(sl)
> ' If Len(sFileExist & vbNullString) > 0 Then
> ' Kill sl
> ' End If
> ' Open sl For Binary Access Write As iFileHandle
> ' Put iFileHandle, , a
> ' Close iFileHandle
> ' End If
> '
> '
> ' Dim StartRegisteredApp As Boolean
> '
> ' StartRegisteredApp = True
> ' ' Do we open the exported OLE object in the
> ' ' Application registered for this file type on this system?
> ' If StartRegisteredApp = True Then
> ' ' Some apps require vbNullString for the first parameter,
> ' ' other apps require "open" for the first parameter
> ' ShellExecuteA Application.hWndAccessApp, vbNullString, sl,
> vbNullString, vbNullString, 1
> ' End If ' "open"
> 'End If
>
> ' Below is for debugging.
> ' If you have an OLE object that is not currently
> ' supported by this project then:
> ' 1) Uncomment out the code below.
> ' 2) Use this form to try to extract the OLE object.
> ' 3) Email me the two "dat" files generated below
> ' Stephen[ at ]lebans.com
> ' Subject: Unsupported OLE format
>
>
> iFileHandle = FreeFile
> sl = "C:\OLE-field-ALL" & ".dat"
> sFileExist = Dir(sl)
> If Len(sFileExist & vbNullString) > 0 Then
> Kill sl
> End If
>
> Open sl For Binary Access Write As iFileHandle
> Put iFileHandle, , b
> Close iFileHandle
>
> 'iFileHandle = FreeFile
> 'sl = "C:\OLE-field-CONTENTS" & ".dat"
> 'sFileExist = Dir(sl)
> 'If Len(sFileExist & vbNullString) > 0 Then
> ' Kill sl
> 'End If
>
> Open sl For Binary Access Write As iFileHandle
> Put iFileHandle, , a
> Close iFileHandle
>
> Dim sTmp As String
> 'sTmp = StrConv(a, vbNarrow)
> For x = 0 To UBound(b)
> sTmp = sTmp & Chr(b(x))
> Next x
>
> Me.Text12.Value = sTmp
>
>
> Exit_cmdSave_Click:
> ' Release structured storage library
> UnLoadLib
> Exit Sub
>
> Err_cmdSave_Click:
> MsgBox Err.Description
> Resume Exit_cmdSave_Click
>
> End Sub
|
|
The existing fields on the form will of course show "??????" because I had
you change the Record Source of the form to point to your table. What else
would you expect?
The error generated is because I added a TextBox control named "Text12" to
the form and forget to mention it.
I was not trying to produce a production ready solution for you. I simply
showed you exactly how to extract a single record. Just loop through the
entire recordset to convert each row.
--
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"Mark Anderson" <mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote in message
news:%23lBAcIf0GHA.4408[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > Stephen,
>
> "Stephen Lebans" <ForEmailGotoMy.WebSite.-WWWdotlebansdot...[ at ]linvalid.com>
> wrote in message news:erNYNtc0GHA.4252[ at ]TK2MSFTNGP03.phx.gbl...
>> ASCII Text is being stored in the BLOB(Long Binary Field). I'm sure there
>> are many ways to convert it but this worked the first time. I have
>> imported the relevant table from your MDB into the ExtractInventoryOLE
>> MDB. I then changed the Record Source prop of the
>> ExtractOLEobjectFromTable form to point to your IaxText table. I then
>> changed the Control Source of the OLEPIC Bound OLE Frame control to point
>> to the DESCTEXT field of the IaxText table.
>
> I modified the ExtractSingleOLEobjectFromTable as it's to only one that
> seems to match your "ExtractOLEobjectFromTable form" mentioned above. I
> also when I attach the code as below and run the form I notice the
> Genus/Code/Row Number boxes all show #Name?. When I click the forms button
> it errors out it this line:
>
> Me.Text12.Value = sTmp
>
> with error "Method or Data member not found.
>
> Even if this did run surely I'd have to run this form for every record and
> there will be about 150,000+ records to process (albeit split over several
> MDBs).
>
> Have I missed a step? Surely it's the
> ExtractInventoryAllOLEobjectsFromTable form I should be using.
>
> Regards
>
> Mark
>
>
>
>> Finally I modifed the existing Sub behind the ExtractOLEobjectFromTable
>> form to this:
>>
>> Private Sub cmdSave_Click()
>> On Error GoTo Err_cmdSave_Click
>>
>> Dim a() As Byte
>> Dim b() As Byte
>> Dim x As Long
>> Dim lTemp As Long
>> Dim sl As String
>> Dim blRet As Boolean
>> Dim sExt As String
>> Dim sFileExist As String
>>
>> ' This is an optional param we pass to fGetContentsStream.
>> ' It will contain the original file name of the
>> ' object when embedded as a Package.
>> Dim PackageFileName As String
>>
>> Dim iFileHandle As Integer
>>
>> ' Load our Structured Storage Library
>> ' Let's see if the StrStorage.DLL is available.
>> blRet = LoadLib()
>> If blRet = False Then
>> ' Cannot find StrStorage.dll file
>> Exit Sub
>> End If
>>
>> lTemp = LenB(Me.OLEPic.Value)
>> ReDim a(0 To lTemp - 1)
>> ReDim b(0 To lTemp - 1)
>>
>> ' Copy the contents of the OLE field to our byte array
>> a = Me.OLEPic.Value
>>
>> ' Make a copy of the original data
>> b = a
>>
>> 'blRet = fGetContentsStream(a(), sExt, PackageFileName)
>> 'If blRet = True Then
>> '
>> ' If sExt = "pak" Then
>> ' ' If a file was dragged from the Explorer window
>> ' ' it will have a Package object Filename of NULL
>> ' ' inserted by Shell.DLL
>> ' ' Catch and give a temp file name
>> ' If Len(PackageFileName & vbNullString) < 3 Then
>> ' PackageFileName = "OLE-ExtractDraggedFromExplorer" &
>> "." & "bmp"
>> ' End If
>> '
>> ' iFileHandle = FreeFile
>> ' sl = "C:\" & PackageFileName
>> ' sFileExist = Dir(sl)
>> ' If Len(sFileExist & vbNullString) > 0 Then
>> ' Kill sl
>> ' End If
>> '
>> ' Open sl For Binary Access Write As iFileHandle
>> ' Put iFileHandle, , a
>> ' Close iFileHandle
>> ' Else
>> '
>> ' iFileHandle = FreeFile
>> ' sl = "C:\" & sExt & UBound(a) & "." & sExt
>> ' sFileExist = Dir(sl)
>> ' If Len(sFileExist & vbNullString) > 0 Then
>> ' Kill sl
>> ' End If
>> ' Open sl For Binary Access Write As iFileHandle
>> ' Put iFileHandle, , a
>> ' Close iFileHandle
>> ' End If
>> '
>> '
>> ' Dim StartRegisteredApp As Boolean
>> '
>> ' StartRegisteredApp = True
>> ' ' Do we open the exported OLE object in the
>> ' ' Application registered for this file type on this system?
>> ' If StartRegisteredApp = True Then
>> ' ' Some apps require vbNullString for the first parameter,
>> ' ' other apps require "open" for the first parameter
>> ' ShellExecuteA Application.hWndAccessApp, vbNullString, sl,
>> vbNullString, vbNullString, 1
>> ' End If ' "open"
>> 'End If
>>
>> ' Below is for debugging.
>> ' If you have an OLE object that is not currently
>> ' supported by this project then:
>> ' 1) Uncomment out the code below.
>> ' 2) Use this form to try to extract the OLE object.
>> ' 3) Email me the two "dat" files generated below
>> ' Stephen[ at ]lebans.com
>> ' Subject: Unsupported OLE format
>>
>>
>> iFileHandle = FreeFile
>> sl = "C:\OLE-field-ALL" & ".dat"
>> sFileExist = Dir(sl)
>> If Len(sFileExist & vbNullString) > 0 Then
>> Kill sl
>> End If
>>
>> Open sl For Binary Access Write As iFileHandle
>> Put iFileHandle, , b
>> Close iFileHandle
>>
>> 'iFileHandle = FreeFile
>> 'sl = "C:\OLE-field-CONTENTS" & ".dat"
>> 'sFileExist = Dir(sl)
>> 'If Len(sFileExist & vbNullString) > 0 Then
>> ' Kill sl
>> 'End If
>>
>> Open sl For Binary Access Write As iFileHandle
>> Put iFileHandle, , a
>> Close iFileHandle
>>
>> Dim sTmp As String
>> 'sTmp = StrConv(a, vbNarrow)
>> For x = 0 To UBound(b)
>> sTmp = sTmp & Chr(b(x))
>> Next x
>>
>> Me.Text12.Value = sTmp
>>
>>
>> Exit_cmdSave_Click:
>> ' Release structured storage library
>> UnLoadLib
>> Exit Sub
>>
>> Err_cmdSave_Click:
>> MsgBox Err.Description
>> Resume Exit_cmdSave_Click
>>
>> End Sub
>
>
|
|
Stephen,
[Quoted Text] > The existing fields on the form will of course show "??????" because I
> had you change the Record Source of the form to point to your table.
> What else would you expect?
I probably didn't make clear the Access part of this isn't my area of
expertise - my apologies. But, OK, I see your point <g>.
> The error generated is because I added a TextBox control named
> "Text12" to the form and forget to mention it.
>
> I was not trying to produce a production ready solution for you. I
> simply showed you exactly how to extract a single record. Just loop
> through the entire recordset to convert each row.
All understood. Ignorance of Access on my part - I don't wish to sound
ungrateful for your and John's help. It'll be a day or so before I can
get to work on this again, but the mists are clearing.
Thanks again.
Regards
Mark
|
|
Post back if you run into any more problems implementing the code/logic.
--
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"Mark Anderson" <mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote in message
news:%23UdCAig0GHA.1288[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > Stephen,
>
>> The existing fields on the form will of course show "??????" because I
>> had you change the Record Source of the form to point to your table. What
>> else would you expect?
>
> I probably didn't make clear the Access part of this isn't my area of
> expertise - my apologies. But, OK, I see your point <g>.
>
>> The error generated is because I added a TextBox control named "Text12"
>> to the form and forget to mention it.
>>
>> I was not trying to produce a production ready solution for you. I
>> simply showed you exactly how to extract a single record. Just loop
>> through the entire recordset to convert each row.
>
>
> All understood. Ignorance of Access on my part - I don't wish to sound
> ungrateful for your and John's help. It'll be a day or so before I can get
> to work on this again, but the mists are clearing.
>
> Thanks again.
>
> Regards
>
> Mark
>
|
|
Thanks for jumping in, Stephen!
On Wed, 6 Sep 2006 12:44:25 -0300, "Stephen Lebans"
<ForEmailGotoMy.WebSite.-WWWdotlebansdot...[ at ]linvalid.com> wrote:
[Quoted Text] >ASCII Text is being stored in the BLOB(Long Binary Field). I'm sure there
>are many ways to convert it but this worked the first time. I have imported
>the relevant table from your MDB into the ExtractInventoryOLE MDB. I then
>changed the Record Source prop of the ExtractOLEobjectFromTable form to
>point to your IaxText table. I then changed the Control Source of the OLEPIC
>Bound OLE Frame control to point to the DESCTEXT field of the IaxText table.
>
>Finally I modifed the existing Sub behind the ExtractOLEobjectFromTable form
>to this:
>
>Private Sub cmdSave_Click()
>On Error GoTo Err_cmdSave_Click
>
>Dim a() As Byte
>Dim b() As Byte
>Dim x As Long
>Dim lTemp As Long
>Dim sl As String
>Dim blRet As Boolean
>Dim sExt As String
>Dim sFileExist As String
>
>' This is an optional param we pass to fGetContentsStream.
>' It will contain the original file name of the
>' object when embedded as a Package.
>Dim PackageFileName As String
>
>Dim iFileHandle As Integer
>
>' Load our Structured Storage Library
>' Let's see if the StrStorage.DLL is available.
>blRet = LoadLib()
>If blRet = False Then
> ' Cannot find StrStorage.dll file
> Exit Sub
>End If
>
>lTemp = LenB(Me.OLEPic.Value)
>ReDim a(0 To lTemp - 1)
>ReDim b(0 To lTemp - 1)
>
>' Copy the contents of the OLE field to our byte array
>a = Me.OLEPic.Value
>
>' Make a copy of the original data
>b = a
>
>'blRet = fGetContentsStream(a(), sExt, PackageFileName)
>'If blRet = True Then
>'
>' If sExt = "pak" Then
>' ' If a file was dragged from the Explorer window
>' ' it will have a Package object Filename of NULL
>' ' inserted by Shell.DLL
>' ' Catch and give a temp file name
>' If Len(PackageFileName & vbNullString) < 3 Then
>' PackageFileName = "OLE-ExtractDraggedFromExplorer" &
>"." & "bmp"
>' End If
>'
>' iFileHandle = FreeFile
>' sl = "C:\" & PackageFileName
>' sFileExist = Dir(sl)
>' If Len(sFileExist & vbNullString) > 0 Then
>' Kill sl
>' End If
>'
>' Open sl For Binary Access Write As iFileHandle
>' Put iFileHandle, , a
>' Close iFileHandle
>' Else
>'
>' iFileHandle = FreeFile
>' sl = "C:\" & sExt & UBound(a) & "." & sExt
>' sFileExist = Dir(sl)
>' If Len(sFileExist & vbNullString) > 0 Then
>' Kill sl
>' End If
>' Open sl For Binary Access Write As iFileHandle
>' Put iFileHandle, , a
>' Close iFileHandle
>' End If
>'
>'
>' Dim StartRegisteredApp As Boolean
>'
>' StartRegisteredApp = True
>' ' Do we open the exported OLE object in the
>' ' Application registered for this file type on this system?
>' If StartRegisteredApp = True Then
>' ' Some apps require vbNullString for the first parameter,
>' ' other apps require "open" for the first parameter
>' ShellExecuteA Application.hWndAccessApp, vbNullString, sl,
>vbNullString, vbNullString, 1
>' End If ' "open"
>'End If
>
>' Below is for debugging.
>' If you have an OLE object that is not currently
>' supported by this project then:
>' 1) Uncomment out the code below.
>' 2) Use this form to try to extract the OLE object.
>' 3) Email me the two "dat" files generated below
>' Stephen[ at ]lebans.com
>' Subject: Unsupported OLE format
>
>
>iFileHandle = FreeFile
>sl = "C:\OLE-field-ALL" & ".dat"
>sFileExist = Dir(sl)
>If Len(sFileExist & vbNullString) > 0 Then
> Kill sl
>End If
>
>Open sl For Binary Access Write As iFileHandle
>Put iFileHandle, , b
>Close iFileHandle
>
>'iFileHandle = FreeFile
>'sl = "C:\OLE-field-CONTENTS" & ".dat"
>'sFileExist = Dir(sl)
>'If Len(sFileExist & vbNullString) > 0 Then
>' Kill sl
>'End If
>
>Open sl For Binary Access Write As iFileHandle
>Put iFileHandle, , a
>Close iFileHandle
>
>Dim sTmp As String
>'sTmp = StrConv(a, vbNarrow)
>For x = 0 To UBound(b)
>sTmp = sTmp & Chr(b(x))
>Next x
>
>Me.Text12.Value = sTmp
>
>
>Exit_cmdSave_Click:
>' Release structured storage library
>UnLoadLib
> Exit Sub
>
>Err_cmdSave_Click:
> MsgBox Err.Description
> Resume Exit_cmdSave_Click
>
>End Sub
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
Stephen,
I got everything working. A few observations/issues (not least so as to
help those reading this thread from archives). Adding a new text box to
the Export Single form did indeed display the text. Next, I tracked down
the Overflow error in the ExportAll form - it is from this line:
blRet = fGetContentsStream(a(), sExt, PackageFileName)
.... rem that out and all works.
I found that, in the sample ImageAXS MDB, all text records save one
[sic] are one character longer than the source. In most cases the last
character is #0 which I take to be a null terminator. In the ExportAll
form's ExportOLE sub, I was able to insert this code to check exported
text:
lngLenArr = UBound(a) - LBound(a) ' + 1
For lngCnt = 0 To lngLenArr
If a(lngCnt) > 0 Then
strValLoc = Chr(a(lngCnt))
strVal = strVal & strValLoc
End If
Next
Debug.Print "|" & strVal & "|" ' bracket strVal in pipes to show
trailing space
Note that I have to rem the ' + 1' from normal array length setting code
as if left in the code failed - silently - which what I take to be some
dimensioning/out of bounds issue.
As not all strings are null terminated (no idea why!) I'm checking for
Chr(0) and not writing it if found.
Now I can see the text, it occurred to me that what I ought to do is
make a Memo field (or Text if I can be sure values are <255 chars) in
Table IaxData - re my reference MDB - and copy extracted values from the
IaxText OLE field to the new IaxData field - both have a common ID field
that could be used as a key. Can I do this in Access VBA?
Like I say I'm an Access / RDBMS newbie so it's the 'simple' database
stuff I'm more likely to trip up on!
TIA
Mark
|
|
Thanks for the feedback, Mark.
I think it's a good idea to store the extracted text in a memo field.
I'm not familiar with Stephen's code, let alone your modifications, but
presumably at some point in processing each record you have a variable
containing the text extracted from the BLOB field.
One approach would be to add a memo field to the same table that
contains the BLOB field, with a bound textbox on the form. Then just
assign the text to the textbox. Later, use a query to export this and
any other fields to the new database.
On Mon, 11 Sep 2006 15:27:12 +0100, "Mark Anderson"
<mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote:
[Quoted Text] >Now I can see the text, it occurred to me that what I ought to do is
>make a Memo field (or Text if I can be sure values are <255 chars) in
>Table IaxData - re my reference MDB - and copy extracted values from the
>IaxText OLE field to the new IaxData field - both have a common ID field
>that could be used as a key. Can I do this in Access VBA?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
John,
"John Nurick" <j.mapSoN.nurick[ at ]dial.pipex.com> wrote in message
news:krfbg2tl8v3v0pdiljl645cabr3pojvk56[ at ]4ax.com...
[Quoted Text] > Thanks for the feedback, Mark.
Though it might help others reading this months down the line.
> I think it's a good idea to store the extracted text in a memo field.
> I'm not familiar with Stephen's code, let alone your modifications,
> but
> presumably at some point in processing each record you have a variable
> containing the text extracted from the BLOB field.
Yes indeed.
> One approach would be to add a memo field to the same table that
> contains the BLOB field, with a bound textbox on the form. Then just
> assign the text to the textbox.
OK. Despite my shakiness on the Access side of things, I do see I've
already got a recordset for the whole of the IaxText table so I can
write to a memo field from within the main loop without needing to worry
about matching IDs.
> Later, use a query to export this and
> any other fields to the new database.
Except the workflow is to export this to text, do a whole lot of other
stuff to it before it sees the next database. As far as I can see, the
basic export-to-text is for tables only. If I want to use data from two
tables, I still need to be using VBA, or am I missing something.
In outline terms, I've 2 tables IaxText and IaxData, both with matching
ID fields, though IaxText only has entries if there is text data (i.e.
not all records in IaxData). Wouldn't it be easier to use to copy the
text data to IaxData, then I can just dump the whole table to text?
Regards
Mark
|
|
Mark, in the code I posted, the call to fGetContentsStream was commented out
so I'm not sure why you put it back in. Perhaps you did not understand when
I stated that the contents of your Long Binary field are NOT formatted as an
OLE object, therefore you cannot use my OLE extractor code as is.
I'm glad you are making headway. Just finish the logic by copying the string
you create for each row to the desired field in the other table. You've
already posted VBA code to walk through your BLOB recordset. Just open up
the other table in its own recordset, position yourself on the correct row
via the ID field of the current row of the BLOB recordset, copy the text to
the desired field, and continue looping until all rows are processed in your
BLOB recordset.
FInally, you can comment out my code that creates a disk based copy of the
BLOB data as this was simply to aid in debugging.
--
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"Mark Anderson" <mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote in message
news:eGDgo6a1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > Stephen,
>
> I got everything working. A few observations/issues (not least so as to
> help those reading this thread from archives). Adding a new text box to
> the Export Single form did indeed display the text. Next, I tracked down
> the Overflow error in the ExportAll form - it is from this line:
> blRet = fGetContentsStream(a(), sExt, PackageFileName)
> ... rem that out and all works.
>
> I found that, in the sample ImageAXS MDB, all text records save one [sic]
> are one character longer than the source. In most cases the last character
> is #0 which I take to be a null terminator. In the ExportAll form's
> ExportOLE sub, I was able to insert this code to check exported text:
>
> lngLenArr = UBound(a) - LBound(a) ' + 1
> For lngCnt = 0 To lngLenArr
> If a(lngCnt) > 0 Then
> strValLoc = Chr(a(lngCnt))
> strVal = strVal & strValLoc
> End If
> Next
> Debug.Print "|" & strVal & "|" ' bracket strVal in pipes to show trailing
> space
>
> Note that I have to rem the ' + 1' from normal array length setting code
> as if left in the code failed - silently - which what I take to be some
> dimensioning/out of bounds issue.
>
> As not all strings are null terminated (no idea why!) I'm checking for
> Chr(0) and not writing it if found.
>
> Now I can see the text, it occurred to me that what I ought to do is make
> a Memo field (or Text if I can be sure values are <255 chars) in Table
> IaxData - re my reference MDB - and copy extracted values from the IaxText
> OLE field to the new IaxData field - both have a common ID field that
> could be used as a key. Can I do this in Access VBA?
>
> Like I say I'm an Access / RDBMS newbie so it's the 'simple' database
> stuff I'm more likely to trip up on!
>
> TIA
>
> Mark
>
>
|
|
Mark: Forget my last post and do what Stephen says<g>
On Mon, 11 Sep 2006 22:21:25 +0100, "Mark Anderson"
<mark[ at ]SPAMMENOTyeardley.demon.co.uk> wrote:
[Quoted Text] >John,
>
>"John Nurick" <j.mapSoN.nurick[ at ]dial.pipex.com> wrote in message
>news:krfbg2tl8v3v0pdiljl645cabr3pojvk56[ at ]4ax.com...
>> Thanks for the feedback, Mark.
>
>Though it might help others reading this months down the line.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
Stephen
"Stephen Lebans"
<ForEmailGotoMy.WebSite.-WWWdotlebansdot...[ at ]linvalid.com> wrote in
message news:unqp07g1GHA.4772[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > Mark, in the code I posted, the call to fGetContentsStream was
> commented out so I'm not sure why you put it back in. Perhaps you did
> not understand when I stated that the contents of your Long Binary
> field are NOT formatted as an OLE object, therefore you cannot use my
> OLE extractor code as is.
Yes your code commented out the fGetContentsStream for the single/demo
code, but when I applied the same concept to the ExportAll form, I
overlooked that aspect (for a frustrating hours or so <g>).
> I'm glad you are making headway. Just finish the logic by copying the
> string you create for each row to the desired field in the other
> table. You've already posted VBA code to walk through your BLOB
> recordset. Just open up the other table in its own recordset, position
> yourself on the correct row via the ID field of the current row of the
> BLOB recordset, copy the text to the desired field, and continue
> looping until all rows are processed in your BLOB recordset.
Yes, all done. I've also been able to flatten out some keyword table
data for export too.
My thanks again you yourself and John for the help (and project files)
its been a real help and a good learning experience.
Regards
Mark
|
|
|