By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Storing MS Office documents, .txt files, and .PDFs within a JET back end?

P: n/a
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?
--
PeteCresswell
Jul 8 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Fri, 07 Jul 2006 20:14:23 -0400, "(PeteCresswell)" <x@y.Invalid>
wrote:

I would try to find out WHY users want to go this route.

Typically users want to work with documents, want a simple quick way
to pull up a document. They could (should?) care less how the docs are
stored.

The few times I have worked with docs stored in an Access DB I was not
very happy. The OLE machinery causes a lot of overhead so docs don't
load/activate immediately. Also showing them in a OLE bound control
limits the size of the doc, and sometimes what features (e.g.
toolbars) are available.

-Tom.

>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?
Jul 8 '06 #2

P: n/a
(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?
--
PeteCresswell
IMO File Sytems are a good place to store files, including the types of
files you named. Windows includes the Windows Explorer which helps us
to see a visual model of file storage and to manage files.

Windows also provides, free of charge, Indexing Service. Indexing
Service is easily programmed and provides search and identification
capabilities far beyond those which could be provided in a Database,
unless many many hours of creative programming were assigned.

Indexing Service can appear puzzling when one first tries to learn
about it. But the learning curve is short and it is extremely powerful.

http://msdn.microsoft.com/library/?u.../indexserv.asp

Jul 8 '06 #3

P: n/a
Per Tom van Stiphout:
>I would try to find out WHY users want to go this route.
Referential integrity. If a document stored in the file system gets
deleted/lost, then the DB has a pointer to nowhere and doesn't know the doc's
gone missing until somebody tries to open it.

Having said that, I've always stored my docs in the file system and just had
pointers in the DB.

Also showing them in a OLE bound control
limits the size of the doc, and sometimes what features (e.g.
toolbars) are available.
I'll have to look into that one. It may be my "out".... not that it should
matter.... Time & Materials and all that... but I don't like wasting user
dollars on futile pursuits... makes me look bad in the long run.
--
PeteCresswell
Jul 8 '06 #4

P: n/a
"(PeteCresswell)" <x@y.Invalidwrote in
news:ap********************************@4ax.com:
Per Tom van Stiphout:
>>I would try to find out WHY users want to go this route.

Referential integrity. If a document stored in the file system
gets deleted/lost, then the DB has a pointer to nowhere and
doesn't know the doc's gone missing until somebody tries to open
it.

Having said that, I've always stored my docs in the file system
and just had pointers in the DB.
The only time I've ever stored documents in OLE fields was a
replicated app where it was the only way to have the same documents
available at two different offices.

I think for the situation you describe, I'd just run regular checks.

Another thing that might work is to move the documents from their
starting location to a location in a hidden share on the server.
That would be harder for users to find. You'd want to not display
the share in your Access app.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 8 '06 #5

P: n/a
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?
Jul 11 '06 #6

P: n/a
Per Jericho Johnson:
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.
That sounds like a *really* good argument against doing it - especially because
the current argument in favor of doing it is based only on data integrity...
seems like the possibility of corrupted OLE fields would outweigh any file
storage risks - especially if, as somebody else suggested, the app runs a check
every so often..
--
PeteCresswell
Jul 11 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.