Linking OLE Objects to a Separate Database 1

[TheTomCat]

I am very new at Access 2007. I have a Photo field on a form that a user can click on to add a photo of a person. I would like to actually store the photos in a separate database. The one I have now is extremely slow due to all the photos stored in it.

How do I leave the form field as is but link to a separate database to store the photos?



Thomas Bailey
a/k/a TomCat

 

[nim180]

Hi TheTomCat How are ya.....

Why not try it this way. Save all you photos in a folder and place it whereever your database is (desktop, server etc) and then do the following.

Create a new column called ImagePath in your table. This will contain the path for your image's. Now get the path for each image and save it in the appropriate field in the table.

Then go to your form where the photos are displayed and go to design view. Create a textbox and name it ImagePath and now put the control source as ImagePath (The new column you added to your table). Put the control visible = NO. Now drag the the textbox to the top right hand corner of your form and shrink it as small as you can.

Now create an image Frame and place any image in it.Now go to the control source of the image and name it PictureImage, then go to the control "Picture" and delete the link to the existing image. You should now have an empty image frame on your form.

On the "On Current" event of your form add the following code.

Me![PictureImage].Properties("Picture") = Me![ImagePath]

That should do it, now when you open the form the image path will be pulled from the table and displayed on your form. This way you dont have to save the image in the database.

Hope that helps,
Nim

 

[TheAceMan1]

How are ya TheTomCat . . .

Is this a [blue]Single Form[/blue] or [blue]Continuous Form[/blue]?

Yor [blue]stuck with OLE[/blue] If its a continuous form! [blue]nim180's[/blue] post will handle single form . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheTomCat]

Thanks for the responses.
It is a single form. I will try the response by nim180 and let you know.

Thanks

Thomas Bailey
a/k/a TomCat

 

[TheTomCat]

Nim180,

I want to make sure I have everything right. It's mostly self explnatory but I want to make sure.

I created the new column in my table and named it ImagePath. You said to then get the path for each image and save it to the appropriate field.
Would you please explain that part further?

I created the text box in form design view. I named it ImagePath, I chose ImagePath as the Control Source. I shrank it and made it invisible also. I think I got that one with no problem.

I created the Image Frame. You said to go to the Control Source of the image and name it PictureImage. Do you just type that in the Control Source line or do you name it PictueImage in the Name line?

Anyway, I deleted the link. Then I put the code in the form's On Current event.

Now, assuming I have everything right, how do I, or what's the easiest way to add a photo to that Image Frame in Form View?

What I want to do is add each photo as I go to each record and save it to each individual record.

I must have something wrong with what you told me because right now, I cannot see how to add a photo either by double clicking or right clicking.

Thanks for your help.



Thomas Bailey
a/k/a TomCat

 

[TheTomCat]

Nim180

I finally got it working. I typed in the path to my server and also typed in the picture number. Now I have the photos showing in my form, for each record, and they are saved elsewhere. Great.

Now, as I mentioned in my earlier response to you, do you know of any easy way to add the photos to each record?
I used to have it where the user double clicked on the Image Frame and chose Insert Object and they went from there. But that was saving them in the current database.

If nothing else, I am definitely a lot further than I was.
Thanks for your help.

Thomas Bailey
a/k/a TomCat

 

[nim180]

Hi TheTomCat,

Im glad you got it working. Since the photos are saved in a different location you can bring up a command box to allow you to browse your computer for a photo you want to add then link the photo to you table. Something like this.

Create a command button. You will be using the same textbox that you have shrunk so make it visible and enlarge it a bit (Will contain new path to photo). Keep in mind once you change the path and save it, the photo in your frame will change. On the onclick event of the command button place the following code.

*****************************************************

Dim strFile As String

strFile = GetOpenFile_CLT("C:\", "Select a Photo To Upload")

Me!ImagePath = strFile

*****************************************************

On the Afterupdate event of the textbox place this code

*******************************************************

Me![PictureImage].Properties("Picture") = Me![ImagePath]

*******************************************************

Now create a new Module and call it basOpenFile and add the following code to it.

*******************************************************

' Declarations for Windows Common Dialogs procedures
Private Type CLTAPI_OPENFILE
strFilter As String ' Filter string
intFilterIndex As Long ' Initial Filter to display.
strInitialDir As String ' Initial directory for the dialog to open in.
strInitialFile As String ' Initial file name to populate the dialog with.
strDialogTitle As String ' Dialog title
strDefaultExtension As String ' Default extension to append to file if user didn't specify one.
lngFlags As Long ' Flags (see constant list) to be used.
strFullPathReturned As String ' Full path of file picked.
strFileNameReturned As String ' File name of file picked.
intFileOffset As Integer ' Offset in full path (strFullPathReturned) where the file name (strFileNameReturned) begins.
intFileExtension As Integer ' Offset in full path (strFullPathReturned) where the file extension begins.
End Type

Const ALLFILES = "All Files"

Private Type CLTAPI_WINOPENFILENAME
lStructSize As Long
hWndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustrFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustrData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Const OFN_ALLOWMULTISELECT = &H200
Const OFN_CREATEPROMPT = &H2000
Const OFN_EXPLORER = &H80000
Const OFN_FILEMUSTEXIST = &H1000
Const OFN_HIDEREADONLY = &H4
Const OFN_NOCHANGEDIR = &H8
Const OFN_NODEREFERENCELINKS = &H100000
Const OFN_NONETWORKBUTTON = &H20000
Const OFN_NOREADONLYRETURN = &H8000
Const OFN_NOVALIDATE = &H100
Const OFN_OVERWRITEPROMPT = &H2
Const OFN_PATHMUSTEXIST = &H800
Const OFN_READONLY = &H1
Const OFN_SHOWHELP = &H10

Declare Function CLTAPI_GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
(pOpenfilename As CLTAPI_WINOPENFILENAME) _
As Boolean

Declare Function CLTAPI_GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" _
(pOpenfilename As CLTAPI_WINOPENFILENAME) _
As Boolean

Declare Sub CLTAPI_ChooseColor Lib "msaccess.exe" Alias "#53" _
(ByVal hwnd As Long, rgb As Long)

Function GetOpenFile_CLT(strInitialDir As String, strTitle As String) As String


Dim fOK As Boolean
Dim typWinOpen As CLTAPI_WINOPENFILENAME
Dim typOpenFile As CLTAPI_OPENFILE
Dim strFilter As String

On Error GoTo PROC_ERR

' Set defaults for the structure
strFilter = CreateFilterString_CLT("All Files (*.*)", "*.*", "Database Files (*.MDB)", "*.MDB")

If strInitialDir <> "" Then
typOpenFile.strInitialDir = strInitialDir
Else
typOpenFile.strInitialDir = CurDir()
End If

If strTitle <> "" Then
typOpenFile.strDialogTitle = strTitle
End If

typOpenFile.strFilter = strFilter
typOpenFile.lngFlags = OFN_HIDEREADONLY Or OFN_SHOWHELP

' Convert the CLT structure to a Win structure
ConvertCLT2Win typOpenFile, typWinOpen

' Call the Common dialog
fOK = CLTAPI_GetOpenFileName(typWinOpen)

' Convert the Win structure back to a CLT structure
ConvertWin2CLT typWinOpen, typOpenFile

GetOpenFile_CLT = typOpenFile.strFullPathReturned

PROC_EXIT:
Exit Function

PROC_ERR:
GetOpenFile_CLT = ""
Resume PROC_EXIT

End Function

Sub ConvertCLT2Win(CLT_Struct As CLTAPI_OPENFILE, Win_Struct As CLTAPI_WINOPENFILENAME)
' Comments : Converts the passed CLTAPI structure to a Windows structure
' Parameters: CLT_Struct - record of type CLTAPI_OPENFILE
' Win_Struct - record of type CLTAPI_WINOPENFILENAME
' Returns : Nothing
'
Dim strFile As String * 512

On Error GoTo PROC_ERR

Win_Struct.hWndOwner = Application.hWndAccessApp
Win_Struct.hInstance = 0

If CLT_Struct.strFilter = "" Then
Win_Struct.lpstrFilter = ALLFILES & Chr$(0) & "*.*" & Chr$(0)
Else
Win_Struct.lpstrFilter = CLT_Struct.strFilter
End If
Win_Struct.nFilterIndex = CLT_Struct.intFilterIndex

Win_Struct.lpstrFile = String(512, 0)
Win_Struct.nMaxFile = 511

Win_Struct.lpstrFileTitle = String$(512, 0)
Win_Struct.nMaxFileTitle = 511

Win_Struct.lpstrTitle = CLT_Struct.strDialogTitle
Win_Struct.lpstrInitialDir = CLT_Struct.strInitialDir
Win_Struct.lpstrDefExt = CLT_Struct.strDefaultExtension

Win_Struct.Flags = CLT_Struct.lngFlags

Win_Struct.lStructSize = Len(Win_Struct)

PROC_EXIT:
Exit Sub

PROC_ERR:
Resume PROC_EXIT

End Sub

Sub ConvertWin2CLT(Win_Struct As CLTAPI_WINOPENFILENAME, CLT_Struct As CLTAPI_OPENFILE)
' Comments : Converts the passed CLTAPI structure to a Windows structure
' Parameters: Win_Struct - record of type CLTAPI_WINOPENFILENAME
' CLT_Struct - record of type CLTAPI_OPENFILE
' Returns : Nothing
'
On Error GoTo PROC_ERR

CLT_Struct.strFullPathReturned = Left(Win_Struct.lpstrFile, InStr(Win_Struct.lpstrFile, vbNullChar) - 1)
CLT_Struct.strFileNameReturned = RemoveNulls_CLT(Win_Struct.lpstrFileTitle)
CLT_Struct.intFileOffset = Win_Struct.nFileOffset
CLT_Struct.intFileExtension = Win_Struct.nFileExtension

PROC_EXIT:
Exit Sub

PROC_ERR:
Resume PROC_EXIT

End Sub

Function CreateFilterString_CLT(ParamArray varFilt() As Variant) As String
' Comments : Builds a Windows formatted filter string for "file type"
' Parameters: varFilter - parameter array in the format:
' Text, Filter, Text, Filter ...
' Such as:
' "All Files (*.*)", "*.*", "Text Files (*.TXT)", "*.TXT"
' Returns : windows formatted filter string
'
Dim strFilter As String
Dim intCounter As Integer
Dim intParamCount As Integer

On Error GoTo PROC_ERR

' Get the count of paramaters passed to the function
intParamCount = UBound(varFilt)

If (intParamCount <> -1) Then

' Count through each parameter
For intCounter = 0 To intParamCount
strFilter = strFilter & varFilt(intCounter) & Chr$(0)
Next

' Check for an even number of parameters
If (intParamCount Mod 2) = 0 Then
strFilter = strFilter & "*.*" & Chr$(0)
End If

End If

CreateFilterString_CLT = strFilter

PROC_EXIT:
Exit Function

PROC_ERR:
CreateFilterString_CLT = ""
Resume PROC_EXIT

End Function

Function RemoveNulls_CLT(strIn As String) As String
' Comments : Removes terminator from a string
' Parameters: strIn - string to modify
' Return : modified string
'
Dim intChr As Integer

intChr = InStr(strIn, Chr$(0))

If intChr > 0 Then
RemoveNulls_CLT = Left$(strIn, intChr - 1)
Else
RemoveNulls_CLT = strIn
End If

End Function

*********************************************************

That should do the trick but i havent tested this out so if there are any errors just give me a shout.

Hope that helps,
nim180

 

[TheTomCat]

Nim180

You are THE MAN.

I have it working perfectly. It is even better than I was hoping for.
I went back and amde the text box invisible again and it seems to still work fine. I also edited the Command Button code so that it will open straight to the folder on my Server that stores the photos.

When I distribute my applications, I will just have to do some editing.

Thank you very much for your help.

P.S. As soon as I can figure out how to give you a star, you will get one from me. It's been a while since I did that.

TheTomCat



Thomas Bailey
a/k/a TomCat

 

[PHV]

As soon as I can figure out how to give you a star
Simply click the
Thank nim180
for this valuable post!
link.