How do I correctly write this expression?

[Guest]

I am trying to write a MS Access expression to have a FORM display the correct picture from one of two tables.
This is the expression I wrote (for the control source) which does not work (the detail area of the form is just blank):
=[tblCattlePics]![CattlePic] Or [tblCattlePics100]![CattlePic]

----------
Background Information:
I am using Access 97.
The database displayed this error message: “The Database has reached Maximum sizeâ€
I use pictures of cattle in my database, one picture for each record in the database. When I hit about 90 pictures, I got the above error message.
I tried using the “Add-Ins†Database Splitter. No help since the table containing the pictures alone comes to 1GB. (I continued to receive the Maximum size message).
I could update to a newer version of Access, but I understand these only go to 2GBs, so I will soon encounter the same problem
SO -- I am trying to separate the 'pictures' table into two (or more) linked tables. Therefore (I hope) keeping each database below 1GB.
The help in Access makes the following statement: Maximum size: 1 gigabyte. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity. [Whatever that means, so far it has not worked, and no search of the Microsoft assistance website has provided any usable answer.]


I appreciate your help!
Thanks,
NMex Ron

 

[JohnFol]

That will evaluate as a boolean expression.
What piece of logic decides which table to use? And when does that logic get
updated (ie on the OnCurrent of the form??)

I am trying to write a MS Access expression to have a FORM display the

correct picture from one of two tables.

This is the expression I wrote (for the control source) which does not

work (the detail area of the form is just blank):

=[tblCattlePics]![CattlePic] Or [tblCattlePics100]![CattlePic]

----------
Background Information:
I am using Access 97.
The database displayed this error message: "The Database has reached Maximum size"
I use pictures of cattle in my database, one picture for each record in

the database. When I hit about 90 pictures, I got the above error message.

I tried using the "Add-Ins" Database Splitter. No help since the table

containing the pictures alone comes to 1GB. (I continued to receive the
Maximum size message).

I could update to a newer version of Access, but I understand these only

go to 2GBs, so I will soon encounter the same problem

SO -- I am trying to separate the 'pictures' table into two (or more)

linked tables. Therefore (I hope) keeping each database below 1GB.

The help in Access makes the following statement: Maximum size: 1

gigabyte. However, because your database can include linked tables in other
files, its total size is limited only by available storage capacity.
[Whatever that means, so far it has not worked, and no search of the
Microsoft assistance website has provided any usable answer.]

 

[Exponent]

This is most likely a result of using OLE Embedding to store images, and the overhead that this can cause
(or are you really storing ~10MB images ?).

The problem can occur with OLE Embedding because an uncompressed copy of the image is stored in the field;
for jpeg pictures this can be anything from 5 to 50 (or more) *times* the size of the original image.


By switching to raw-binary storage you can avoid this overhead (and several other associated problems);
you will probably cut your database size to anything from 1/5th to 1/50th of its current size. Alternatively
you can work entirely with external image files.

One easy way to try these approaches is with our image control, DBPix. It requires little or no code,
and adds useful functionality, such as EXIF info support, lossless jpeg rotation, Zoom & Pan, built-in
file browsing, TWAIN/scanner integration, asynchronous decoding and more. For more info, downloads, samples
etc, see the url below.

--
_______________________________________________________
http://www.ammara.com/
Image Handling Components, Samples, Solutions and Info
DBPix 2.0 - lossless jpeg rotation, EXIF, asynchronous

I am trying to write a MS Access expression to have a FORM display the correct picture from one of two tables.
This is the expression I wrote (for the control source) which does not work (the detail area of the form is just blank):
=[tblCattlePics]![CattlePic] Or [tblCattlePics100]![CattlePic]

----------
Background Information:
I am using Access 97.
The database displayed this error message: “The Database has reached Maximum sizeâ€
I use pictures of cattle in my database, one picture for each record in the database. When I hit about

90 pictures, I got the above error message.

I tried using the “Add-Ins†Database Splitter. No help since the table containing the pictures alone

comes to 1GB. (I continued to receive the Maximum size message).

I could update to a newer version of Access, but I understand these only go to 2GBs, so I will soon encounter the same problem
SO -- I am trying to separate the 'pictures' table into two (or more) linked tables. Therefore (I hope)

keeping each database below 1GB.

The help in Access makes the following statement: Maximum size: 1 gigabyte. However, because your database

can include linked tables in other files, its total size is limited only by available storage capacity.
[Whatever that means, so far it has not worked, and no search of the Microsoft assistance website has
provided any usable answer.]

 

[Steve Schapel]

NMex,

=[tblCattlePics]![CattlePic] Or [tblCattlePics100]![CattlePic] is not
a valid expression.
Could you please explain on what basis Access is meant to decide which
table to find the correct picture in?

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Steve

If I understand your question correctly

Access decides which table the picture is in based on an autonumber field termed "CattleID", which is the key field in the database

MORE explanation
I created a Query that contains both picture tables "tblCattlePics" and "tblCattlePics100", plus the database's 'main table' "tblCattleâ€. I established a 'relationship' using an 'autonumber' termed CattleID. The CattleID number is established in the main database "tblCattle". When I enter a photo in “tblCattlePics†OR “tblCattlePics100†, I also enter the CattleID number

Incidentally, the form works fine if I only designate one (of the two) tables as the 'Control Source'

Thanks
NMex Ro
----- Steve Schapel wrote: ----

NMex

=[tblCattlePics]![CattlePic] Or [tblCattlePics100]![CattlePic] is no
a valid expression
Could you please explain on what basis Access is meant to decide whic
table to find the correct picture in

- Steve Schapel, Microsoft Access MV


On Mon, 12 Jan 2004 20:56:12 -0800, "NMex Ron" <(Nme
Ron)[email protected]> wrote

I am trying to write a MS Access expression to have a FORM display the correct picture from one of two tables
This is the expression I wrote (for the control source) which does not work (the detail area of the form is just blank)
=[tblCattlePics]![CattlePic] Or [tblCattlePics100]![CattlePic

---------

Background Information
I am using Access 97
The database displayed this error message: “The Database has reached Maximum sizeâ€
I use pictures of cattle in my database, one picture for each record in the database. When I hit about 90 pictures, I got the above error message
I tried using the “Add-Ins†Database Splitter. No help since the table containing the pictures alone comes to 1GB. (I continued to receive the Maximum size message)
I could update to a newer version of Access, but I understand these only go to 2GBs, so I will soon encounter the same proble
SO -- I am trying to separate the 'pictures' table into two (or more) linked tables. Therefore (I hope) keeping each database below 1GB
The help in Access makes the following statement: Maximum size: 1 gigabyte. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity. [Whatever that means, so far it has not worked, and no search of the Microsoft assistance website has provided any usable answer.Thanks
NMex Ro

 

[Guest]

JohnFol

If I understand your question correctly

Access decides which table the picture is in based on an autonumber field termed "CattleID", which is the key field in the database

I am using NO 'OnCurrent' to update. The photo is only updated manually when needed (i.e. the cow gets older)

MORE explanation
I created a Query that contains both picture tables "tblCattlePics" and "tblCattlePics100", plus the database's 'main table' "tblCattleâ€. I established a 'relationship' using an 'autonumber' termed CattleID. The CattleID number is established in the main database "tblCattle". When I enter a photo in “tblCattlePics†OR “tblCattlePics100†, I also enter the CattleID number

Incidentally, the form works fine if I only designate one (of the two) tables as the 'Control Source'

Thanks
NMex Ro

----- JohnFol wrote: ----

That will evaluate as a boolean expression
What piece of logic decides which table to use? And when does that logic ge
updated (ie on the OnCurrent of the form??

I am trying to write a MS Access expression to have a FORM display th

correct picture from one of two tables

This is the expression I wrote (for the control source) which does no

work (the detail area of the form is just blank)

=[tblCattlePics]![CattlePic] Or [tblCattlePics100]![CattlePic

---------

Background Information
I am using Access 97
The database displayed this error message: "The Database has reache Maximum size
I use pictures of cattle in my database, one picture for each record i

the database. When I hit about 90 pictures, I got the above error message

I tried using the "Add-Ins" Database Splitter. No help since the tabl

containing the pictures alone comes to 1GB. (I continued to receive th
Maximum size message)

I could update to a newer version of Access, but I understand these onl

go to 2GBs, so I will soon encounter the same proble

SO -- I am trying to separate the 'pictures' table into two (or more

linked tables. Therefore (I hope) keeping each database below 1GB

The help in Access makes the following statement: Maximum size:

gigabyte. However, because your database can include linked tables in othe
files, its total size is limited only by available storage capacity
[Whatever that means, so far it has not worked, and no search of th
Microsoft assistance website has provided any usable answer.

 

[Steve Schapel]

NMex,

OK, I think I understand a bit better now.

It seems to me that the Joins between the tblCattle table and each of
the Pics tables will need to be a Left Join. And then, your
expression to find the picture will have to be something like this...
=Nz([tblCattlePics].[CattlePic],[tblCattlePics100].[CattlePic])
.... or, to put it another way...
=IIf(IsNull([tblCattlePics].[CattlePic]),[tblCattlePics100].[CattlePic],[tblCattlePics].[CattlePic])

- Steve Schapel, Microsoft Access MVP

 

[Exponent]

Am I the only one that read this bit ?

"The Database has reached Maximum size"
When I hit about 90 pictures, I got the above error message

Surely, the real problem here is the image storage overhead.

Fix that and the database size could drop from 1GB to maybe as little as 20 MB. No need to split the table,
no need for joins, and the expression issue becomes academic.

--
_______________________________________________________
http://www.ammara.com/
Image Handling Components, Samples, Solutions and Info
DBPix 2.0 - lossless jpeg rotation, EXIF, asynchronous

NMex,

OK, I think I understand a bit better now.

It seems to me that the Joins between the tblCattle table and each of
the Pics tables will need to be a Left Join. And then, your
expression to find the picture will have to be something like this...
=Nz([tblCattlePics].[CattlePic],[tblCattlePics100].[CattlePic])
.... or, to put it another way...
=IIf(IsNull([tblCattlePics].[CattlePic]),[tblCattlePics100].[CattlePic],[tblCattlePics].[CattlePic])

- Steve Schapel, Microsoft Access MVP

Steve,

If I understand your question correctly:

Access decides which table the picture is in based on an autonumber field termed "CattleID", which is the key field in the database.

MORE explanation:
I created a Query that contains both picture tables "tblCattlePics" and "tblCattlePics100", plus the

database's 'main table' "tblCattle”. I established a 'relationship' using an 'autonumber' termed CattleID.
The CattleID number is established in the main database "tblCattle". When I enter a photo in “tblCattlePics”
OR “tblCattlePics100” , I also enter the CattleID number.

 

[Guest]

Steve

I tried your expressions - still no picture

I had already used a left join in the database relationship, and the query relationships

Other suggestions
Thanks
Ro

----- Steve Schapel wrote: ----

NMex

OK, I think I understand a bit better now

It seems to me that the Joins between the tblCattle table and each o
the Pics tables will need to be a Left Join. And then, you
expression to find the picture will have to be something like this..
=Nz([tblCattlePics].[CattlePic],[tblCattlePics100].[CattlePic]
.... or, to put it another way..
=IIf(IsNull([tblCattlePics].[CattlePic]),[tblCattlePics100].[CattlePic],[tblCattlePics].[CattlePic]

- Steve Schapel, Microsoft Access MV


On Tue, 13 Jan 2004 22:36:13 -0800, "NMex Ron

 

[Steve Schapel]

Surely, the real problem here is the image storage overhead.

Agreed.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

What -- So this means YOU are giving up on fixing this Access problem?
The solution you offer is for us to BUY another product for $99.00+ to correct this shortcoming in the program.

Access users (especially larger businesses will love this).

Please offer another expression, to enable the form to read from either table.

I never replied to 'Exponent's' message, because he is just trying to sell something.

Waiting,
Ron

 

[Guest]

JohnFol

Have you read the latest answers to my question

Can you offer an expression that may work

I appreciate your assistance

Thanks
Ron L

 

[Jim Macklin]

What question. try this E=MC^2 or =$A1*$B$3


message
| JohnFol,
|
| Have you read the latest answers to my question?
|
| Can you offer an expression that may work?
|
| I appreciate your assistance.
|
| Thanks,
| Ron L

 

[Steve Schapel]

Ron

On Thu, 15 Jan 2004 18:56:08 -0800, "NMex Ron" <(NMex Ron)

What -- So this means YOU are giving up on fixing this Access problem?

No. I've already told you how to fix it.

The solution you offer is for us to BUY another product for $99.00+ to correct this shortcoming in the program.

No, I did not. I agreed with "Exponent"'s statement that the problem
stems from your data storage methods.

Access users (especially larger businesses will love this).

Larger businesses that use Access would normally store their image
data externally to the database.

Please offer another expression, to enable the form to read from either table.

If you would post the SQL of the query you are using, I am willing to
have another look.

I never replied to 'Exponent's' message, because he is just trying to sell something.

Fair enough. I didn't initially respond because I don't like his
anonymity. Nevertheless, replying is one thing, reading is another.
If you read his earlier post you might learn something.

Waiting,

Be my guest

- Steve Schapel, Microsoft Access MVP

 

[Exponent]

I never replied to 'Exponent's' message, because he is just trying to sell something.

Not at all, Ron; I've given you a detailed explanation of what is causing the storage problem you are experiencing.
I've suggested two *generic* approaches to solve the problem, and finally, mentioned a product that is
designed specifically to address such issues.

Our Links page refers to some FREE alternatives:

Larry Linson's sample illustrates 3 approaches: OLE Embedding, raw-binary (BLOB) and external-file storage.
That's over 1200 lines of code, completely FREE.

Note that the Access image control has a habit of crashing if you scroll through records too quickly, and
that you may need to use a registry hack to suppress the progress dialog it throws up. Note also that
using the Access image control relies on the relevant office graphics filters being installed, so it may
be necessary to go to every desktop with an Office CD to install them (plus, of course, to have a full
Access/Office license for every desktop - in particular this may not be suitable for distributing an application
with the Access runtime).

Stephen Lebans' site is also well worth a visit, and if you accept that OLE Embedding is not the way to
go, you'll almost certainly want to use Stephen's OLEtoDisk.

Perhaps someone more 'independent' can jump in here and explain that using multiple Gigabyte database files
to store a few 10's of Megabytes of images is far from the best solution.

 

[Guest]

Steve,
Thanks for offering to try again.
Here is the SQL of the query I am trying to use (qryCattlePics)

SELECT tblCattle.CattleID, tblCattlePics.CattlePic, tblCattlePics100.CattlePic
FROM (tblCattle LEFT JOIN tblCattlePics ON tblCattle.CattleID = tblCattlePics.CattleID) LEFT JOIN tblCattlePics100 ON tblCattle.CattleID = tblCattlePics100.CattleID;

Thanks,
Ron
----- Steve Schapel wrote: -----

Ron

On Thu, 15 Jan 2004 18:56:08 -0800, "NMex Ron" <(NMex Ron)

What -- So this means YOU are giving up on fixing this Access problem?

No. I've already told you how to fix it.

The solution you offer is for us to BUY another product for $99.00+ to correct this shortcoming in the program.

No, I did not. I agreed with "Exponent"'s statement that the problem
stems from your data storage methods.

Access users (especially larger businesses will love this).

Larger businesses that use Access would normally store their image
data externally to the database.

Please offer another expression, to enable the form to read from either table.

If you would post the SQL of the query you are using, I am willing to
have another look.

I never replied to 'Exponent's' message, because he is just trying to sell something.

Fair enough. I didn't initially respond because I don't like his
anonymity. Nevertheless, replying is one thing, reading is another.
If you read his earlier post you might learn something.

Waiting,

Be my guest

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Mr/Ms. Exponent

I apologize for not seeing that you were trying to help -- I am not a computer wizard or Access expert, I am just a small businessman and rancher trying to use MS Access to track my cattle

Sorry, I do not know what the terms "OLE Embedding, raw-binary (BLOB) and external-file storage" mean. Or at least I do not know how to implement them

I presumed that if I 'linked' the photos they would not actually be in the database. My mistake, and I was totaly surprised when I received the message 'Databse has reached maximum size'

If there is someway that I can get the pictures out of the database and into some external file please let me know

Thanks for your infomation

Ro

----- Exponent wrote: ----

I never replied to 'Exponent's' message, because he is just trying to sell something

Not at all, Ron; I've given you a detailed explanation of what is causing the storage problem you are experiencing
I've suggested two *generic* approaches to solve the problem, and finally, mentioned a product that i
designed specifically to address such issues

Our Links page refers to some FREE alternatives

Larry Linson's sample illustrates 3 approaches: OLE Embedding, raw-binary (BLOB) and external-file storage
That's over 1200 lines of code, completely FREE

Note that the Access image control has a habit of crashing if you scroll through records too quickly, an
that you may need to use a registry hack to suppress the progress dialog it throws up. Note also tha
using the Access image control relies on the relevant office graphics filters being installed, so it ma
be necessary to go to every desktop with an Office CD to install them (plus, of course, to have a ful
Access/Office license for every desktop - in particular this may not be suitable for distributing an applicatio
with the Access runtime).

Stephen Lebans' site is also well worth a visit, and if you accept that OLE Embedding is not the way t
go, you'll almost certainly want to use Stephen's OLEtoDisk

Perhaps someone more 'independent' can jump in here and explain that using multiple Gigabyte database file
to store a few 10's of Megabytes of images is far from the best solution.

-
______________________________________________________
http://www.ammara.com/
Image Handling Components, Samples, Solutions and Inf
DBPix 2.0 - lossless jpeg rotation, EXIF, asynchronou

 

[Exponent]

I presumed that if I 'linked' the photos they would not actually be in the database.

That is the most obvious conclusion, however, as well as 'linking' to the original file, a 'Preview' image
is stored in the database. Since this image is uncompressed it can be up to 100 times the size of the
original image file (or more), hence the problem you are seeing.

If you have the original files then you should use these (and if you did 'Link' then you should still have
them).

If you 'Embedded' the images then it is usually not possible to extract them directly, however they can
be 'regenerated' - the problem with this approach is that, assuming that the original images were in jpg
format and that you want jpg format out, to output jpg requires that they are recompressed. This is 'lossy',
ie there will be a reduction of image quality. Additionally, if the images contained metadata, such as
the useful EXIF info stored in images by digital cameras, this will be lost too.

If you do need to extract them this way then use the tool I mentioned previously: Stephen Lebans' 'OLEtoDisk'
from http://www.lebans.com/

Mr/Ms. Exponent,

I apologize for not seeing that you were trying to help -- I am not a computer wizard or Access expert,

I am just a small businessman and rancher trying to use MS Access to track my cattle.

Sorry, I do not know what the terms "OLE Embedding, raw-binary (BLOB) and external-file storage" mean.

Or at least I do not know how to implement them.

I presumed that if I 'linked' the photos they would not actually be in the database. My mistake, and

I was totaly surprised when I received the message 'Databse has reached maximum size'.

 

[Stephen Lebans]

Exponent this statement is misleading:

If you 'Embedded' the images then it is usually not possible to extract

them directly, however they can

be 'regenerated' - the problem with this approach is that, assuming

that the original images were in jpg

format and that you want jpg format out, to output jpg requires that

they are recompressed. This is 'lossy',

The solution on my site allows you to select the level of Compression.
Just select 0 and the file will be stored uncompressed.

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

in the database.

That is the most obvious conclusion, however, as well as 'linking' to

the original file, a 'Preview' image

is stored in the database. Since this image is uncompressed it can be

up to 100 times the size of the

original image file (or more), hence the problem you are seeing.

If you have the original files then you should use these (and if you

did 'Link' then you should still have

them).

If you 'Embedded' the images then it is usually not possible to

extract them directly, however they can

be 'regenerated' - the problem with this approach is that, assuming

that the original images were in jpg

format and that you want jpg format out, to output jpg requires that

they are recompressed. This is 'lossy',

ie there will be a reduction of image quality. Additionally, if the

images contained metadata, such as

the useful EXIF info stored in images by digital cameras, this will be lost too.

If you do need to extract them this way then use the tool I mentioned

previously: Stephen Lebans' 'OLEtoDisk'

from http://www.lebans.com/

computer wizard or Access expert,

I am just a small businessman and rancher trying to use MS Access to track my cattle.

and external-file storage" mean.

Or at least I do not know how to implement them.

in the database. My mistake, and

I was totaly surprised when I received the message 'Databse has reached maximum size'.

and into some external file please

let me know.

is causing the storage problem you

are experiencing.

and finally, mentioned a product that

is

raw-binary (BLOB) and external-file

storage.

you scroll through records too quickly,

and

progress dialog it throws up. Note also

that

graphics filters being installed, so

it may

them (plus, of course, to have a

full

not be suitable for distributing

an application

accept that OLE Embedding is not the

way to

that using multiple Gigabyte database

files

$99.00+ to correct this shortcoming

 

[Exponent]

Stephen,

Exponent this statement is misleading:

I don't believe it is, though I'll be pleased to be corrected...

The sample (and the jpeg library) takes a Quality parameter, not a Compression one. 100 jpeg quality is
not the same as no compression - regular jpeg compression is lossy, period (not including JPEG2000, Lossless
Jpeg etc).

As a test I stored a jpeg image of 184 KB using OLE Embedding. Extracting it using OLEToDisk with 100
quality gave an image file of 1416 KB. As well as being more than 7 times the size of the original, the
image is different, as you can see with a difference calculation in Photoshop, for example.

Let me stress that I am not suggesting that this is a flaw in the sample; it is simply a consequence of
the fact that any jpeg compression is lossy.
As far as I am aware, some common OLE servers simply do not store the original compressed data in the field,
so there is no way to extracting a jpeg file without an additional (lossy) jpeg compression step.
--
_______________________________________________________
http://www.ammara.com/
Image Handling Components, Samples, Solutions and Info
DBPix 2.0 - lossless jpeg rotation, EXIF, asynchronous