Recommended handling of images Results

Does anyone have a recommended way to deal with images (ie. linked to file, OLE object embedded, others I'm not aware of)???

I have a report which summarizes the history of a project. In this report I have a subreport which has task information. As a user enters task information they have the option of associating pictures with the task (ie. prototypes, design images, test setup, etc).

So my data structure is based on:
- many projects
- each project can have many tasks
- each task can have many pictures

My report for each project lists some typical project information and a subreport shows the task details associated with that project along with as many images that are associated with it.

My current system links to files on a network to avoid database size. This works well for storage and form use, until I try to print or preview a report. Then some of my pictures don't show up and I get a message that says images may be too large. My report could conceivably have up to 100 images, but I'm images stop showing up when I have around 15-20 pictures. Pictures files are typically 300 KB.

Is there a better way to show images on a report?????

As I got to see that this forum is really trying to help access programmers , I decided to use it to post my article in here to benefit its seekers .
Please do note the following :

Methods applied are unconventional and rely on my experimentation of the situation since I got to realize that there were minimal documentations for our subject of discussion J

Also please pardon my English , I've did my best yet it’s a fact that English is my third language.

Quick Introduction
Why wouldn't Microsoft make it easy to compile an MS Access Project into a distributed executable?
1 – Microsoft has managed to create a monster by creating MS Access , and by realizing the risk and the fact that they would have to put many applications (such as Axapta , Great Plains , mind you Visual Studio .net) to sleep , they had to cripple it a little from being a developers platform and tam it into becoming a small business solution.
2 – For a peculiar reason programmers through-out time have always been valued based on complexity and not on achievement (So an Oracle programmer would get paid for developing a phone book application yet an Access Programmer gets a tap on the back which leads elite programmers to refrain from such beast called Microsoft Access.

Now , Now enough chit chat and let's get to work:
Step 1 : Make sure that your application security is not reliant on Microsoft Security file .mdw (for many reasons 1st its unsecured , 2nd it would still give away that this is an MS Access application, 3rd it would conflict with users who have other access applications installed on their own PCs who are about to use yours) you will have to script your own security modal which isn’t so difficult , all you need is to script a login form , an administration form , and every form and report on your application to check if authorized to run or edit (On_Open event) and thus leading the Form/Report to close "DoCmd.Close Form.Name" Or denying data manipulation through "Form.AllowEdits = False" / "Form.AllowDeletions = False" (I am trying to make it look simple so you wouldn’t cry).

Step2: Make sure you have scripted your own menus and tool bars (it's easy and fun so don't you get lazy).

Step3: Make sure your Startup Form is selected , your databse window is set to hide and that you have your own icon installed as project and form icon (Insert icon in your project folder , use string ".youriconname.ico" instead of full path because usually paths vary PC to Another

Step4: Implement Microsoft Script disabling "Startup Form Over-ride by using Shift Key"

Create New Model :

Public Function SetProperties(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
On Error GoTo Err_SetProperties
Dim db As DAO.Database, prp As DAO.Property
Set db = CurrentDb
db.Properties(strPropName) = varPropValue
SetProperties = True
Set db = Nothing
Exit Function
If Err = 3270 Then 'Property not found
Set prp = db.CreateProperty(strPropName, varPropType, varPropValue)
db.Properties.Append prp
Resume Next
SetProperties = False
MsgBox "Runtime Error # " & Err.Number & vbCrLf & vbLf & Err.Description
Resume Exit_SetProperties
End If
End Function

Now Create a label named bDisableBypassKey and add the following code to its Click event :

Private Sub bDisableBypassKey_Click()
On Error GoTo Err_bDisableBypassKey_Click
'Dim strInput As String
'Dim strMsg As String
'strMsg = "Do you want to enable the Bypass Key?" & vbCrLf & vbLf & "Please key the 
'programmer's password to enable the Bypass Key."
'strInput = InputBox(Prompt:=strMsg, Title:="Disable Bypass Key Password")
'If strInput = "TypeYourPasswordHere" Then
' SetProperties "AllowBypassKey", dbBoolean, True
' Beep
' MsgBox "The Bypass Key has been enabled." & vbCrLf & vbLf & "The Shift key will allow the users to bypass the startup
options the next time the database is opened.", vbInformation, "Set Startup Properties"
SetProperties "AllowBypassKey", dbBoolean, False
' MsgBox "Incorrect ''AllowBypassKey'' Password!" & vbCrLf & vbLf & "The Bypass Key was disabled." & vbCrLf & vbLf & "The
Shift key will NOT allow the users to bypass the startup options the next time the database is opened.", vbCritical, "Invalid
'Exit Sub
' End If
Exit Sub
MsgBox "Runtime Error # " & Err.Number & vbCrLf & vbLf & Err.Description
Resume Exit_bDisableBypassKey_Click
End Sub

(above script is a raw and dangerous script , if not handled with care you might end up losing designer's access to your project also do note that I have managed to disable its back door since you shouldn’t be leaving such an advantage in the hands of end users) .
Also do not click your disable label until final stage of operation.

Step5: Cover all your form errors with proper addressing messages or procedures (Unlike MS Access , programs shouldn’t generate run time errors , and usually a program with a runtime error will crash and exit), now for an easy solution for those who are lazy enough use the general Form Error section and add the following (On Error Resume Next) or maybe add a message box or two stating that an error has occurred and user should be more careful (You lazy you!).

Step6: Create a .mde file (Save your VB scripts from being stolen until someone comes up with a proper tool to decode it , you can also skip this stage if you like , or if for a peculiar reason Microsoft Access denied it -Happens with huge projects).

Step7: Create a photo dimensioned 362x290 pixel (Smart developers tried 1x1 pixel image which worked up fine on their platforms but generated errors and crashes on other users' platforms so stick to the standard , oh and darken the area where Microsoft overlays its version details ) in BMP format as your splash screen (This will replace Microsoft Access splash screen on running), give it the same name as your database/project (i.e if your database/project is named myproject.mdb etc. name your photo myproject.bmp ) and place it in your project's folder.

Step8: Rename your database / project file extension (The neat trick) into dll (i.e if your database / project is named myproject.mdb etc. name it myproject.dll ), Also move security workgroup file (system.mdw usualy) used while database creation to project's folder and rename its extension into ocx (i.e if your security workgroup file is named myproject.mdw etc. name it myproject.ocx ).

Step9 : Create a Visual Basic 5 or higher project (Lets get down and dirty for a minute) that all to be done is to run a shell command (Batch file like executable) running the following code in Open event of its Startup Form:

Dim strPathToProgram As String
strPathToProgram = App.Path & "MSACCESS.EXE " & App.Path & "myproject.dll /wrkgrp " & Chr(34) & App.path & "myproject.ocx" &
Shell ("" & strPathToProgram & "")

while making sure Form Parameter "Make Visible in Taskbar" is set to False , Startup Form is invisible and then compile it to the same Project Folder (Adding up an icon file wouldn’t hurt).
(Now attached to this article you will find the source code to such project and a compiled example of its EXE)

Step10 : Keep a copy of file MSACCESS.EXE usually found in Microsoft Office folder in Program Files to your project folder (Saves Junior programmers the hassle of searching registry for Microsoft Office or Microsoft Access Runtime installation folder , also note that this doesn’t work with MS Office 2007 in which case you have to make your executable file created on Step9 lookup MS Office 2007 installation path in registry key "HKEY_LOCAL_MACHINE SOFTWARE Microsoft Office 12.0 Access InstallRoot : Path" value and add it to your shell command).

Et Voila !

Important Notices:
1 - Now do note the following , your project is still reliant on Microsoft Office Access and while I do know how to make it un-reliant , the technique to it is still illegal as it requires certain tampering to Microsoft Access Runtime files installation and registration technique which Microsoft denies.

2 – It is better to include Microsoft Access Runtime environment 2000 / 2002 and not 2003 or 2007 to your installation as you will be faced upon first start by MS Access confirm security level of application which is a confusing dialogue to end user.

Download Link to Access Runtime environment 2000 :
3 – You can always use command line switches to stealthily install Microsoft Access Runtime during installation of your application which I recommend setup factory as an installation packager of choice (A bit techy here).

4 – Finally do note that Command Line switches of Microsoft Access malfunctions if paths include spaces so do try to restrict/deny installation paths that is not compliant with old dos fundamentals of naming (A bit techy here).

Best Regards,

Revised Edition.

I acknowledge the input by members of AWF.
Their comments on the original version are appreciated.
__________________________________________________ _______

Naming Conventions.

This document is aimed at the user who is unfamiliar with any naming convention.

It is based upon personal experience and the interaction I have had with others.

It is not a hard and fast rule.

Why use a Naming Convention?

Using a naming convention when creating your Access Database is vital. A good naming convention will help stop errors that can occur due to badly named objects and will make the initial development of your project quicker and easier. A good naming convention will also make it easier when revisiting a database to add new functionality. This revisit could be after a considerable amount of time. It is then that you will appreciate the structure you employed during the initial development phase of the Database.

Some Basics.

Normalisation is a topic that is widely discussed. If you are not aware or at least have a basic understanding of the subject then you should make yourself familiar. There are many discussions available on the WWW. Normalisation is the foundation stone of any Access database.

Reserved Words are words that are reserved for use by the built in Access functionality or SQL functionality. Examples of the most common reserved words that new developers use when naming database objects are: Date, Day, Month, Year, Now, Print. A developer may have a field in the Sales table called ‘Date’. This reserved word ‘Date’ should be replaced with something like ‘SaleDate’.
A full list of reserved words can be found by searching the WWW.

Spaces and Special Characters. You may be tempted to use spaces or special characters when naming database objects. These special characters can produce errors when referring to them in queries or VBA if not handled correctly. Avoid them when naming objects in your database. The underscore “_” is a widely accepted character by many programmers. It is not recommended here, but simply acknowledged to be acceptable.
Special characters / | @ £ ^ ( ) [ ] { } ; : ! # & = * + - ? " ' $ %

Keep Names Short yet Informative. A table, query, form, report etc. needs to have a name that is informative. Not too long and not too short. For example a query named ‘qryListOfSalesForEachDepotGroupedByMonth’ should be called something like ‘qryMonthlySalesByDepot’ and should definitely not be called ‘MSD’.

Title Case also known as CamelHump. A query named ‘qryMonthlySalesByDepot’ is easier to read than a name written in all UPPER CASE or all lower case i.e. ‘QRYMONTHLYSALESBYDEPOT’ or ‘qrymonthlysalesbydepot’

Name Prefixes. The following is a list of prefixes and example names to use when naming objects. By using a prefix you can more easily distinguish between different object types that have the same name. I.e. if you have a table named ‘Staff’ and a report named ‘Staff’ it is difficult to tell which is which, so name them ‘tblStaff’ and ‘rptStaff’.
Another reason we name our tables and queries with a prefix is that when you are working with these two object types in the query window, Microsoft Access simply lists all tables and queries together without differentiating them. By using the tbl and qry prefixes on these two objects, it ensures that the tables are shown separately from queries.

Other objects that need to be named include the bound and unbound controls found on forms and reports. When you create a form or report using the wizard or AutoForm or AutoReport each of the controls are named the same as the field names. Now whilst this is by MS Access default, it is not really an acceptable way of naming controls. One of the key reasons we do not want to do this is that sometimes when we are working with forms and reports we want to refer to the control rather than the field. By having the field names the same as the object names, you can have a situation where the wrong object is referred.

The way we overcome this, is by naming our controls based on the control type. For example if our field was called Postcode and the control we are using for this field is a text box, then we would name the text box txtPostcode and the label lblPostcode. Note how we refer to text boxes as a control. It is not a field as many think. There are no fields in Reports and Forms. They are controls.

Tables tbl
Queries qry
Forms frm
Reports rpt
Macros mcr
Modules mod, mdl or bas

Tables are usually plural and are preceded with tbl
e.g. tblClients

Queries are preceded with qry
e.g. qryClients

For greater clarity one can describe what a query is used for. This will also sort the queries into like groups and distinguish a query from a table. Notice the use of CamelHump.

E.g. qryFrmClients Main Form
qryFrmSubClients Sub Form
qryRptClients Report
qryRptSubClients Sub Report

Forms follow the same convention and so do Reports.

E.g. frmClients Main Form
frmSubClients Sub Form
rptClients Report
rptSubClients Sub Report

MACROS are preceded with mcr
MODULES are preceded with mdl or mod. Some programmers of old use bas.

Naming of Fields in Tables. Usually singular

Adding prefixes to field names is a practice of old and not often adopted in MS Access.

Primary Keys and Foreign Keys.

Primary Keys should share the same name with its Foreign Keys.
The Primary Key name should be unique within the Database.
The Primary Key should be tagged as the Primary Key and the Foreign Key should be tagged as the Foreign Key.
E.g. ClientPK as Primary Key
ClientFK as Foreign Key.

The commonly used ‘ID’ for both Primary and Secondary Keys does not create a distinguishing identifier.

Naming Fields in Queries.

Queries inherit the table’s field name.
Calculated Fields within queries require their own naming and should observe the basic convention of short, simple and descriptive.

Form and Report Control Objects.

Text Boxes txt
Labels lbl
Command Buttons cmd
Combo Boxes cbo
List Boxes lst
Images img
Sub Forms sbf
Sub Reports sbr


I finish with how I started.

This document is aimed at the user who is unfamiliar with any naming convention. It is based upon my personal experience and the interaction I have had with others.

The naming standard shown above is but one convention. The convention that you adopt is entirely up to you. By using a convention you can be assured that if you do require assistance, your design will be better understood by others.

Choose a naming convention and stick with it, however be prepared to change it if something better comes along.

Not finding an answer? Try a Google search.