Write file from OLE Object field type.

J

Jim

I’m working on adding the functionality to create/email an access report in
pdf. I found a solution by Stephen Lebans but it requires 2 dlls in the
folder where the application resides. Since the access application is
emailed to several remote users it is easiest if the application is
self-contained. What I want to do is store the dlls in the database and
write them to the folder where the database resides when the reports form is
loaded. What I have done so far is create a table with two columns
Name(string) PK, SystemFile(OLE Object). The Name column contains two rows,
dynapdf.dll and strstorage.dll and the SystemFile contains the dlls that were
copy/paste into the field in datasheet view. Not sure if this is important
but, when I view the table in datasheet view SystemFile says “Packageâ€.
Note: If I copy/paste the files from datasheet view to the file system it
works fine.

I’ve created a test form (frmCreateFiles) that has a control for the two
fields, txt_Name (text box) and SystemFile (Bound Object Frame). The code
for the test for button is below. The files are created but the problem is
that 1k is being added to the file size which causes the pdf conversion
module to fail.

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim a() As Byte
Dim lTemp As Long
Dim sl As String

Dim db As Database
Dim varPath, varFileName As String
Dim varFileFound, varLoopCounter, i As Integer

Set db = CurrentDb

varPath = getpath(db.Name)

varLoopCounter = DCount("[Object]", "tblVersionControl", "[Object] like
'*pdf*'")

For i = 1 To varLoopCounter Step 1

varFileName = Forms!frmCreateFiles!txt_Name.Value

varFileFound = Len(Dir(varPath & varFileName))

'delete the file if it already exists
If varFileFound <> 0 Then
Kill varPath & varFileName
End If

lTemp = LenB([Forms]![frmCreateFiles]![txt_SystemFile])
ReDim a(0 To lTemp) ' should be -1

' Copy the contents of the OLE field to our byte array
a = [Forms]![frmCreateFiles]![txt_SystemFile]

sl = varPath & varFileName

Open sl For Binary Access Write As #1
Put #1, , a
Close #1

DoCmd.RunCommand acCmdRecordsGoToNext

Next i

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
 
T

Tony Toews [MVP]

Jim said:
I’m working on adding the functionality to create/email an access report in
pdf. I found a solution by Stephen Lebans but it requires 2 dlls in the
folder where the application resides. Since the access application is
emailed to several remote users it is easiest if the application is
self-contained. What I want to do is store the dlls in the database and
write them to the folder where the database resides when the reports form is
loaded.

Interesting idea.

Did you take a look at the logic at the following web page? I don't
know if it supports dlls though. http://www.lebans.com/oletodisk.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
J

Jim

Stephen,

Thanks for the direction... It's working...

Thanks,

Jim

Stephen Lebans said:
You are inserting the DLL's as an OLE Object of unknown type so Access wraps
the file within a COM wrapper of unknown type - hence the Package type.
Instead, insert the DLL's, one per record, as plain Binary data into the OLE
object field. WHen you look at the record in Datasheet view, the field
should display "long binary data". Access will now look at the field as
plain binary data and not as an OLE object.

Read the DLL into a Byte array
Copy the Byte array as is into the OLE field

As in your original post, you already have my code to copy the contents of
the field to a disk file. The LoadSaveJpeg solution on my site contains
sample code to copy an external file in and out of an OLE Field as plain
Binary data.


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


Jim said:
I'm working on adding the functionality to create/email an access report
in
pdf. I found a solution by Stephen Lebans but it requires 2 dlls in the
folder where the application resides. Since the access application is
emailed to several remote users it is easiest if the application is
self-contained. What I want to do is store the dlls in the database and
write them to the folder where the database resides when the reports form
is
loaded. What I have done so far is create a table with two columns
Name(string) PK, SystemFile(OLE Object). The Name column contains two
rows,
dynapdf.dll and strstorage.dll and the SystemFile contains the dlls that
were
copy/paste into the field in datasheet view. Not sure if this is
important
but, when I view the table in datasheet view SystemFile says "Package".
Note: If I copy/paste the files from datasheet view to the file system it
works fine.

I've created a test form (frmCreateFiles) that has a control for the two
fields, txt_Name (text box) and SystemFile (Bound Object Frame). The code
for the test for button is below. The files are created but the problem
is
that 1k is being added to the file size which causes the pdf conversion
module to fail.

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim a() As Byte
Dim lTemp As Long
Dim sl As String

Dim db As Database
Dim varPath, varFileName As String
Dim varFileFound, varLoopCounter, i As Integer

Set db = CurrentDb

varPath = getpath(db.Name)

varLoopCounter = DCount("[Object]", "tblVersionControl", "[Object] like
'*pdf*'")

For i = 1 To varLoopCounter Step 1

varFileName = Forms!frmCreateFiles!txt_Name.Value

varFileFound = Len(Dir(varPath & varFileName))

'delete the file if it already exists
If varFileFound <> 0 Then
Kill varPath & varFileName
End If

lTemp = LenB([Forms]![frmCreateFiles]![txt_SystemFile])
ReDim a(0 To lTemp) ' should be -1

' Copy the contents of the OLE field to our byte array
a = [Forms]![frmCreateFiles]![txt_SystemFile]

sl = varPath & varFileName

Open sl For Binary Access Write As #1
Put #1, , a
Close #1

DoCmd.RunCommand acCmdRecordsGoToNext

Next i

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top