Storing Images in Database as Filename

[abbyanu]

Hi

I'm developing a small Access App, which we hope to use both in-house and on the Internet (incorporating ASP).

I wish to store images in an access table as filename (e.g. image.jpg) without the pathname. I also wish to use the image filename to display the actual image on a Form, and also when entering data, I wish the user to browse for the image from the harddrive, but only the filename (without the path) should be stored in the table. The images would be located in the same directory as the database.

How can I accomplishing this.

I'm a newbie to VB, and I would very much appreciate any suggestions.

Thanks in advance.

AbbyAnu.

 

[MontyBurns]

abbyanu,

I can help you with the most complicated part of this - getting the image name by browsing for it.

In your access DB, create a new module called BrowseForFile or something similar. Paste the code at the bottom of this post into it (you'll prob have to fix some wrapping issues).

There are a couple of entries you need to change though. I have it set up to default to a particular network folder and return part of the path including the file name. Do do this you need to call it using this code in your form:
'**************************************************
' Opens a file browse window (via a module called
' GetOpenFile, located in the BrowseForFile module)
' and returns the path to the file into the field
' DocumentUNC.
'**************************************************
Private Sub DocumentUNC_DblClick(Cancel As Integer)
Dim strTrimmedPath As String
Dim intChars As Integer

strTrimmedPath = GetOpenFile
intChars = Len(strTrimmedPath)
If intChars > 40 Then
strTrimmedPath = Right(strTrimmedPath, (intChars - 39))
Me.DocumentUNC = strTrimmedPath
End If

End Sub

You'll need to calculate how many characters to trim from the left to only return the filename (it returns the full return path by default). I take off 39 chars as you can see. Me.DocumentUNC = strTrimmedPath puts the resulting string into my form field.

Search this code (below) for \\lon-cifs-2\maximus\transactionbibles\ - this is my starting directory and you will need to replace each occurrance with your own starting path.

***********************************
**** MODULE CODE ***************
***********************************
Option Compare Database
Option Explicit

Public Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
Public Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type



Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000

Function TestIt()
Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Databases (*.*)", "*.*&quot

MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="A:\", _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Select backup file to restore from&quot
' Since you passed in a variable for lngFlags,
' the function places the output flags value in the variable.

Debug.Print Hex(lngFlags)
End Function

Function GetOpenFile(Optional varDirectory As Variant, Optional varTitleForDialog As Variant) As String
' This gets the file name
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName, strRestoreFrom As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
lngFlags = ahtOFN_FILEMUSTEXIST Or ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

' Define the filter string and allocate space in the "c"
strFilter = ahtAddFilterItem(strFilter, "All Files", "*.*&quot
' Now actually call to get the file name.
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
If varFileName <> &quot;&quot; Then
strRestoreFrom = varFileName
GetOpenFile = varFileName
Else:
GetOpenFile = &quot;Incorrect&quot;
End If
End Function

Function ahtCommonFileOpenSave( _
Optional ByRef Flags As Variant, _
Optional ByVal InitialDir As Variant, _
Optional ByVal Filter As Variant, _
Optional ByVal FilterIndex As Variant, _
Optional ByVal DefaultExt As Variant, _
Optional ByVal FileName As Variant, _
Optional ByVal DialogTitle As Variant, _
Optional ByVal hwnd As Variant, _
Optional ByVal OpenFile As Variant) As Variant

Dim OFN As tagOPENFILENAME
Dim strFileTitle As String
Dim fResult As Boolean
Dim strFileName As Variant
' Give the dialog a caption title.
If InitialDir = &quot;&quot; Then InitialDir = &quot;\\lon-cifs-2\maximus\transactionbibles\&quot;
If IsMissing(Filter) Then Filter = &quot;&quot;
If IsMissing(FilterIndex) Then FilterIndex = 1
If IsMissing(Flags) Then Flags = 0&
If IsMissing(DefaultExt) Then DefaultExt = &quot;&quot;
If IsMissing(FileName) Then FileName = &quot;&quot;
If IsMissing(DialogTitle) Then DialogTitle = &quot;&quot;
If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
If IsMissing(OpenFile) Then OpenFile = True
' Allocate string space for the returned strings.
strFileName = Left(FileName & String(256, 0), 256)
strFileTitle = String(256, 0)
' Set up the data structure before you call the function
With OFN
.lStructSize = Len(OFN)
.hwndOwner = hwnd
.strFilter = Filter
.nFilterIndex = FilterIndex
.strFile = strFileName
.nMaxFile = Len(strFileName)
.strFileTitle = strFileTitle
.nMaxFileTitle = Len(strFileTitle)
.strTitle = DialogTitle
.Flags = Flags
.strDefExt = DefaultExt
.strInitialDir = InitialDir
.hInstance = 0
.lpfnHook = 0
'New for NT 4.0
.strCustomFilter = String(255, 0)
.nMaxCustFilter = 255
End With
' This will pass the desired data structure to the
' Windows API, which will in turn uses it to display
' the Open/Save As Dialog.
If OpenFile Then
fResult = aht_apiGetOpenFileName(OFN)
Else
fResult = aht_apiGetSaveFileName(OFN)
End If

If fResult Then
If Not IsMissing(Flags) Then Flags = OFN.Flags
ahtCommonFileOpenSave = TrimNull(OFN.strFile)
Else
ahtCommonFileOpenSave = vbNullString
End If
End Function

Function ahtAddFilterItem(strFilter As String, _
strDescription As String, Optional varItem As Variant) As String
If IsMissing(varItem) Then varItem = &quot;*.*&quot;
ahtAddFilterItem = strFilter & _
strDescription & vbNullChar & _
varItem & vbNullChar
End Function

Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
intPos = InStr(strItem, vbNullChar)
If intPos > 0 Then
TrimNull = Left(strItem, intPos - 1)
Else
TrimNull = strItem
End If
End Function




Good luck, hope this helps,
Burns

 

[abbyanu]

Hi... it's me again:

I'm using Access 2000. I found some suggestions at Microsoft's website. I need some help to refine these:

Set the Imageform form's OnCurrent property to the following event procedure:
[tt] Private Sub Form_Current()
On Error Resume Next
Me![ImageFrame].Picture = Me![ImagePath]
End Sub [/tt]

Set the AfterUpdate property of the ImagePath text box to the following event procedure:
[tt] Private Sub Form_AfterUpdate()
On Error Resume Next
Me![ImageFrame].Picture = Me![ImagePath]
End Sub [/tt]


I did the above. Works, but three problems:

1. After going to the next record, image for the previous record remains on the screen. How can I clear the screen or refresh, so that only the current file [ImagePath] is displayed at [ImageFrame] on the form?

2. The above code only allows me to type the full path and filename into the textbox. I also wish to use a command button to browse the hard drive for image files, display the image on [ImageFrame] and then store the pathname in the relevant field [ImagePath] in the table. What code do I add to the command button's Click to accomplish this?

3. I then wish to take the pathname above, strip it and leave only the filename and extension, and store this in a different field [ImageName]. What code can I use to accomplish this?

Thanks for assistng.

Peace,
AbbyAu.

 

[abbyanu]

Burns

I'm truly humbled! I just accessed your suggetions after posting the revision above. This should be a piece of cake for you, I bet Please do help with 1 and 2.

I will use the trimming function right away.

Thanks so much.

AbbyAnu.

 

[MontyBurns]

AbbyAnu,

Firstly, I can't take credit for the code I posted! All I did was modify it to return the path to the file.

Attach the small piece of code to your command btn. OnClick will now call this code, which in turn will call proc's within the module you created with the large piece of code. What it does is open a file-browse window, and once you've either dbl clicked the file u want or selected it and clicked Open, it will return the full UNC path and put it in a text box.

The small piece of code I sent has string operators in it to trim part of the path away as I know which server, share and root folder all the files will be in. You'll need to store the whole thing so i'd remove the string functions.

I reckon you'd want to actually store the full path while displaying the filename only. Possibly look for the last \ in the path and take everything to the right of it? Have a look at string functions in Access Help.

Unfortunately i've never worked with ImageFrame's in Access so I'm not sure why it isn't updating with the current event. Possibly it's just taking time to display the image?

Good luck with it anyway,
Burns

 

[abbyanu]

Mnay thanks, Burns.

That worked, perfectly!

Kudos! AbbyAnu.