Blob in Access

#1

Hi,

I have MS Access 2002. I want to make create a table with a blob field
but I don't see that option when creating a table. I want to store
text in the blob field.

How do I do this?

Thanks,
Lee G.

#2

On Thu, 6 Mar 2008 18:51:25 -0800 (PST), leegold58 <goldtech (AT) worldpost (DOT) com>
wrote:

Quote:

Hi,

I have MS Access 2002. I want to make create a table with a blob field
but I don't see that option when creating a table. I want to store
text in the blob field.

How do I do this?

Thanks,
Lee G.

Try the "memo" data type.

Wayne Gillespie
Gosford NSW Australia

#3

"leegold58" <goldtech (AT) worldpost (DOT) com> wrote

Quote:

I have MS Access 2002. I want to make create a
table with a blob field but I don't see that option
when creating a table. I want to store
text in the blob field.

As Wayne said, a Memo Field will work; so will an OLE field. There's an
example of using a BLOB in Access in the Imaging Examples you'll find at
http://accdevel.tripod.com -- note those examples and article have not yet
been updated for Access 2007.

Larry Linson
Microsoft Office Access MVP

#4

Per Larry Linson:

Quote:

As Wayne said, a Memo Field will work; so will an OLE field. There's an
example of using a BLOB in Access in the Imaging Examples you'll find at
http://accdevel.tripod.com -- note those examples and article have not yet
been updated for Access 2007.

For reasons I can't recall, the last few times I had to track
various kinds of documents via a JET back end, I elected to store
only the DOS path addresses of the documents and keep them in a
dedicated folder.

Obvious downside of that is data integrity - we'll never know if
a doc goes missing until we try to open it.

But it seems to me like there were some fairly serious downsides
to storing, for instance, a mix of MS WORD, Excel, and PDF docs
within the DB. Performance is what comes to mind first, but I
can't really recall.

Anybody have an opinion on the merits of the two approaches?

Same two approaches, but in an SQL Server back end?
--
PeteCresswell

#5

"(PeteCresswell)" <x@y.Invalid> wrote

Quote:

For reasons I can't recall, the last few times I had to track
various kinds of documents via a JET back end, I elected to store
only the DOS path addresses of the documents and keep them in a
dedicated folder.

Obvious downside of that is data integrity - we'll never know if
a doc goes missing until we try to open it.

I store Word docs in a separate folder as you cite. I have a procedure that

reads the table of DOS path addresses and runs a DIR() on each and print the
missing docs. I run the procedure periodically. If a doc gets moved or
deleted, at least I find out in short order. This makes tracking it down a
little easier.
Fred Zuckerman

#6

"(PeteCresswell)" <x@y.Invalid> wrote:

Quote:

For reasons I can't recall, the last few times I had to track
various kinds of documents via a JET back end, I elected to store
only the DOS path addresses of the documents and keep them in a
dedicated folder.

Obvious downside of that is data integrity - we'll never know if
a doc goes missing until we try to open it.

I've been meaning to write a simple routine that
1) clears a flag on the document table
2) runs down each file mentioned in the document table verifying that it exists on
the hard drive.
2a) It might be nice to store date/time and size of file to see if there were any
changes since the time the file was initially saved
3) display any that are missed.

Then do the same thing the other way. Go down the list of files looking for the file
in the table.

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/

#7

Per Tony Toews [MVP]:

Quote:

I've been meaning to write a simple routine that
1) clears a flag on the document table
2) runs down each file mentioned in the document table verifying that it exists on
the hard drive.
2a) It might be nice to store date/time and size of file to see if there were any
changes since the time the file was initially saved
3) display any that are missed.

Then do the same thing the other way. Go down the list of files looking for the file
in the table.

That seems to address the original question to some degree:
sounds like even the stars store their docs in the file system
rather than JET....

How about SQL Server? Same deal? Or is it workable?

I've got an agenda here.... got my guys talked into moving to SQL
Server if testing shows a performance increase and I'm wondering
if I should change my document storage strategy.
--
PeteCresswell

#8

(PeteCresswell) wrote:

Quote:

Per Tony Toews [MVP]:
I've been meaning to write a simple routine that
1) clears a flag on the document table
2) runs down each file mentioned in the document table verifying
that it exists on the hard drive.
2a) It might be nice to store date/time and size of file to see if
there were any changes since the time the file was initially saved
3) display any that are missed.

Then do the same thing the other way. Go down the list of files
looking for the file in the table.

That seems to address the original question to some degree:
sounds like even the stars store their docs in the file system
rather than JET....

How about SQL Server? Same deal? Or is it workable?

Related story: at a client site I interface some Access systems with a doc
mgmt system from Hummingbird - now owned by OpenText I believe. It uses a
document metadata library hosted in SQL Server (Oracle option), but the
documents themselves are renamed/obfuscated - Certificate of Occupancy.doc
becomes A451O3 - and stored on the server file system.

Quote:

I've got an agenda here.... got my guys talked into moving to SQL
Server if testing shows a performance increase and I'm wondering
if I should change my document storage strategy.

#9

"(PeteCresswell)" <x@y.Invalid> wrote in
news:guv3t396hs6jpsdbdpck334qd6humdofvv (AT) 4ax (DOT) com:

Quote:

Per Tony Toews [MVP]:
I've been meaning to write a simple routine that
1) clears a flag on the document table
2) runs down each file mentioned in the document table verifying
that it exists on the hard drive.
2a) It might be nice to store date/time and size of file to see if
there were any changes since the time the file was initially saved
3) display any that are missed.

Then do the same thing the other way. Go down the list of files
looking for the file in the table.

That seems to address the original question to some degree:
sounds like even the stars store their docs in the file system
rather than JET....

How about SQL Server? Same deal? Or is it workable?

I've got an agenda here.... got my guys talked into moving to SQL
Server if testing shows a performance increase and I'm wondering
if I should change my document storage strategy.

I actually copy the file to an attachments subdirectory on the
server, renaming it to the primary key of the attachment's record,
then set it (the copy) to read-only.

I did one application where the attachments table did originally
include the ole field, and was not impressed.

The DBA admin at work won't allow blobs in SQL Server.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

#10

Bob Quintal <rquintal (AT) sPAmpatico (DOT) ca> wrote in
news:Xns9A5B4AAECB745BQuintal (AT) 66 (DOT) 150.105.47:

Quote:

"(PeteCresswell)" <x@y.Invalid> wrote in
news:guv3t396hs6jpsdbdpck334qd6humdofvv (AT) 4ax (DOT) com:

Per Tony Toews [MVP]:
I've been meaning to write a simple routine that
1) clears a flag on the document table
2) runs down each file mentioned in the document table verifying
that it exists on the hard drive.
2a) It might be nice to store date/time and size of file to see if
there were any changes since the time the file was initially saved
3) display any that are missed.

Then do the same thing the other way. Go down the list of files
looking for the file in the table.

That seems to address the original question to some degree:
sounds like even the stars store their docs in the file system
rather than JET....

How about SQL Server? Same deal? Or is it workable?

I've got an agenda here.... got my guys talked into moving to SQL
Server if testing shows a performance increase and I'm wondering
if I should change my document storage strategy.

I actually copy the file to an attachments subdirectory on the
server, renaming it to the primary key of the attachment's record,
then set it (the copy) to read-only.

I did one application where the attachments table did originally
include the ole field, and was not impressed.

The DBA admin at work won't allow blobs in SQL Server.

Years ago I helped with a project that used Indexing Service to manage
and make available all kinds of documents. With it a home care nurse
could pull out her trusty notebook, type in "Transfusion and
Haemophiliac" and find all the documents in his/her [large] handbook that
dealt with those topics (in title, body, ... anywhere) in a few seconds.
Wild Cards and various operators were available to him/her.
Indexing Service seems to me to be powerful, effective and efficient at
managing documents. Indexing Service is not rocket science. Its object
model is akin to a Database model, but only a small fraction of the size.
ADO has an Indexing Service provider and the whole thing could be learned
in an afternoon.
But almost no one seems to use Indexing Service; it appears everyone
would rather chop down trees with their trusty, rusty JET pocket-knife,
or their bigger SQL machete, instead of using the power chain saw
available.
BTW, Indexing Service is a free part of Windows (I haven't checked VISTA
for it yet), and it can be used in web applications (with an MS Server,
of course.)