Pete,
I don't really recommend storing files inside the .mdb file because
there are disk space limits, and if the .mdb file gets damaged or
corrupted beyond repair, you lose everything. Also, I have seen .mdb
files that get damaged, can be repaired with Compact and Repair, but the
OLE Field data is still damaged and unusable. Beginning with Access
2000, a single .mdb file can only be 2GB. However, you could create a
separate .mdb file for just this one table, so you would be able to
store almost 2GB of files.
Now, if you are going to do this, I don't recommend using the OLE
object control to load or display these files for your users. Access
puts alot of overhead in the OLE field if you do this, and then you are
running a copy of Acrobat (for example) inside the ole object control on
your form, which can be problematic. The best way is to store the file
in straight binary format, no overhead, and then when you need to
display it, extract it to a temp folder on the hard drive and let
Windows run the application that can display that file externally from
your Access application.
I do store some small files inside my .mdb for a few applications.
These are special files that help with the upgrading or deployment of my
applications, so the users don't directly interact with these files, but
the concept is the same.
What you want to do is have the users browse to the file to be
stored. Then, use the following code to insert the file into the OLE
object field in your table:
================================================== =======
'Note: Record must already exist in the Table, this routine
'is merely filling in the data for the OLE Field.
'Also, Error Handling has been removed for brevity
Public Sub ImportFileToOLEField(TableName As String, _
WhereClause As String, _
OLEFieldName As String, _
inFileName As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim mssql As String
Dim byteArray() As Byte
Dim aFile As Long
Set db = CurrentDb()
mssql = "SELECT * FROM [" & TableName & "] WHERE " & WhereClause & ";"
Set rst = db.OpenRecordset(mssql, dbOpenDynaset)
If Not rst.BOF Then
aFile = FreeFile
Open inFileName For Binary Access Read Shared As aFile
ReDim byteArray(LOF(aFile))
byteArray = InputB(LOF(aFile), #aFile)
Close aFile
aFile = 0
rst.Edit
rst(OLEFieldName).AppendChunk byteArray
rst.Update
End If
rst.Close
If aFile <0 Then
Close aFile
End If
Set rst = Nothing
db.Close
Set db = Nothing
End Sub
================================================== =======
This will get the file inserted into the OLE Field as a simple binary
object. That way, there isn't any overhead for the application
information, which you won't need. The other thing you want to do is in
the table that is storing the files, have a field for the original file
name, original path, and original file extension. So, either a single
field for the full path and file name (i.e. C:\Data\MyFile.pdf) or
separate fields that break down this information. The reason I
recommend storing the original path is for reference only if someone
ever questions if it was the right file. The filename and file
extension are important because you will use them later when the user
wants to display this file.
Ok, so now that you have files stored in your table, you need a
mechanism to extract those files and let the user display them in the
appropriate application (Word, Excel, Acrobat, etc, etc). So, use the
following routine to extract your OLE object to a temporary folder:
================================================== =======
Public Sub ExportOLEFieldToFile(TableName As String, _
WhereClause As String, _
OLEFieldName As String, _
outFileName As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim mssql As String
Dim byteArray() As Byte
Dim aFile As Long
Dim fSize As Long
Set db = CurrentDb()
If outFileName <"" Then
If Dir(outFileName) <"" Then
Kill outFileName
End If
mssql = "SELECT * FROM [" & TableName & "] WHERE " & WhereClause & ";"
Set rst = db.OpenRecordset(mssql, dbOpenDynaset)
If Not rst.BOF Then
fSize = rst(OLEFieldName).FieldSize
ReDim byteArray(fSize)
byteArray = rst(OLEFieldName).GetChunk(0, fSize)
aFile = FreeFile
Open outFileName For Binary Access Write Shared As aFile
Put #aFile, , byteArray
Close aFile
aFile = 0
End If
rst.Close
End If
If aFile <0 Then
Close aFile
End If
Set rst = Nothing
db.Close
Set db = Nothing
End Sub
================================================== =======
Now you have the file extracted to the hard drive, in a temp folder.
All you need to do now is use a Windows API call to tell Windows to
open that file in the default application for that file type. This is
the equivalent of double-clicking on the file. Windows will look at the
file extension and open the default application for that file.
Ok... I have the code you need to launch the file in the default
application, I just can't find it at the moment. I wanted to get this
much posted so you could begin to weigh your options on this topic. As
soon as I find the code for launching the file in Windows, I will post a
follow-up.
--
Jericho Johnson
jerichoj at sbcglobal dot net
(PeteCresswell) wrote:
User wants to go this route instead of storing pointers in the DB and the
documents outside.
Only time I tried it was with only MS Word docs - and that was a loooong time
ago - and it seemed to me like there were performance issues at the time.
How about the different types? The MS docs I would expect Access to
differentiate and handle appropriately (i.e. .DOC and .XLS).. but how about
.PDF? and can I stash a .TXT document in the same OLE object as the others?
What's the best way to let the users enter such documents? Copy/Paste? Forcing
them to choose from a Common File Dialog? All of them would be pre-existing
documents that were released to the user by various corporations/fund managers.
None of them would be created by the user... so we're talking about storing an
existing doc, not typing it into the app.
Anybody been here recently?
Am I wasting my time trying to accommodate the user's desire for JET in-DB
storage instead of going to SQL Server?
