Automatically Linking to a BE Database on a CD

[jedel]

Hi,
I'm creating a simple FE / BE Database that shows pictures (sort of a photo albulm if you like). Because I don't know the path of everybody's CD Rom, I have created a system so that when they open the database, it will promt the user for there CD drive path and the images will then be able to be viewed.

What I would like to be able to do, is every 6 -12 months provide updates to the data. Now this can be done by having the data on a BE table located on a CD.

When the user inserts the CD into there drive, the FE Database will be automatically unzipped from the CD to wherever the user wants it on there PC. What I would like to do after that is to link the tables to the From the BE database on the CD to Front end on the hard Drive.

THE QUESTION

Seeing as the users will probably not know too much about Access, how can I automatically link the FE on the PC, to the BE on the CD? Bearing in mind that the path the the CD must be determined first.

Thanks in advance

Jedel

 

[jedel]

I hate to answer my own question but I found the answer to my problem. However, it did raise a second issue. The code that I used for linking the dbs together is

Private Sub Form_Open(Cancel As Integer)
Dim path As String

fAccessWindow ("Minimise")
DoCmd.MoveSize , 1

MsgBox "Before you can browse through the pictures, the database needs to identify the" _
& "path to either your cd or if you have copied the entirte CD to your hard drive" _
& ", the folder location on your hard drive. Click ok and then browse to the CD Drive, or the folder location of the images"

path = BrowseForFolder(0, "Some Text here...or use the control's label caption")

DoCmd.TransferDatabase acLink, "Microsoft Access", path & "\Family Album_be.mdb", acTable, "Photos", "Photos", 1
DoCmd.TransferDatabase acLink, "Microsoft Access", path & "\Family Album_be.mdb", acTable, "info_tbl", "info_tbl", 1


Me.txtPath2 = path

Me.Category.Locked = True
End Sub

The Browseforfolder code is below

Option Compare Database
Option Explicit

Private Type BROWSEINFO
    hwndOwner      As Long
    pidlRoot       As Long
    pszDisplayName As Long
    lpszTitle      As Long
    ulFlags        As Long
    lpfnCallback   As Long
    lParam         As Long
    iImage         As Long
End Type

Private Const BIF_RETURNONLYFSDIRS = 1
Private Const MAX_PATH = 260

Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" _
       (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Private Declare Function SHBrowseForFolder Lib "Shell32" _
       (lpbi As BROWSEINFO) As Long
Private Declare Function SHGetPathFromIDList Lib "Shell32" _
       (ByVal pidList As Long, ByVal lpBuffer As String) As Long

Public Function BrowseForFolder(hwndOwner As Long, sPrompt As String) As String

    Dim iNull As Integer
    Dim lpIDList As Long
    Dim lResult As Long
    Dim sPath As String
    Dim udtBI As BROWSEINFO

    With udtBI
        .hwndOwner = hwndOwner
        .lpszTitle = lstrcat(sPrompt, "")
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With

    lpIDList = SHBrowseForFolder(udtBI)
    If lpIDList Then
        sPath = String$(MAX_PATH, 0)
        lResult = SHGetPathFromIDList(lpIDList, sPath)
        Call CoTaskMemFree(lpIDList)
        iNull = InStr(sPath, vbNullChar)
        If iNull Then
            sPath = Left$(sPath, iNull - 1)
        End If
    End If

    BrowseForFolder = sPath

End Function

This code works fine and does the job nicely.

SOME POINTS

the "txtPath2" field is an unbound text box. This text box reverts to being blank each time the form is opened. I did this because it was the only way I knew to have the data across all the records.

THE NEW PROBLEM

Each time I open the form, it creates new tables so I eventually end up with heaps of tables in the db with the same info in them.

THE NEW QUESTION

Once the user has established the links after the initial start up, how can I bypass the code in the form so that it will always use the file path?

Thanks in advance

Jedel

 

[jwigh]

Before you link the tables, you should drop the old links.You can use a routine like this.

Public Sub DropAllLinks()
Dim Lnk As DAO.TableDef
For Each Lnk In CurrentDb.TableDefs
If Lnk.Attributes = dbAttachedTable Then
CurrentDb.TableDefs.Delete Lnk.Name
End If

Next
End Sub

 

[jedel]

jwigh

Thanks for your reply. I like the idea. But instead of dropping them, can we determine if they are there or not? The idea being that I only want to do this once.

Remember, I need to work in the possibility of only finding the drive path once as well.

Cheers

Jedel

 

[jedel]

OK, I think I have it!

If anyone can improve on this please feel free and let me know.

Firstly, I created a new table with two fields: 'path' and 'linked'. The 'linked' fied is boolean. I called the table 'path_tbl' (ok, so i'm not real creative with names!)

Then I created a startup form using the 'path_tbl' as the data source. In the OnOpen code, I placed the following code.

Private Sub Form_Open(Cancel As Integer)

Dim drive As String

fAccessWindow ("Minimise")
DoCmd.MoveSize , 1

DoCmd.GoToRecord , , acFirst
If Me.linked = True Then
GoTo step2
Else
MsgBox "Before you can browse through the pictures, the database needs to identify the" _
& "path to either your cd or if you have copied the entirte CD to your hard drive" _
& ", the folder location on your hard drive. Click ok and then browse to the CD Drive, or the folder location of the images"

drive = BrowseForFolder(0, "Some Text here...or use the control's label caption")

DoCmd.TransferDatabase acLink, "Microsoft Access", drive & "\Family Album_be.mdb", acTable, "Photos", "Photos1", 1
DoCmd.TransferDatabase acLink, "Microsoft Access", drive & "\Family Album_be.mdb", acTable, "info_tbl", "info_tbl1", 1

Me.path = drive
Me.linked = True
End If

step2:
Me.Visible = False
DoCmd.OpenForm "photos_frm"

End Sub

You'll note that this code was taken from the original post with a few minor tweaks.

in the main photos form, I have the OnCurrent code:

Private Sub Form_Current()
On Error GoTo errcurrent

Me.mpic.Picture = [Forms]![path2_frm]![path] & "\" & Me.txtPath1
Me.testpath = [Forms]![path2_frm]![path] & "\" & Me.txtPath1
Me.List6.Requery

errcurrent:
Exit Sub

End Sub
[/code]

This uses two fields to determine the correct path to the images. the first section is the section that is requested by the user to point to his / her CD Drive and the second is the fixed file path that will not change.

Note that the first part of the path refers to the startup form. This form is currently hidden and can be refered to when needed.

Now, the bueaty of it is when I burn this onto a CD, the FE database will have no links to the tables. The query for the subform is in code.

When the user opens that database for a second time, it will automatically go to teh first record and read the path.

As I said earlier, can any one provide me with some feedback, possible areas of concern or errors? Just like to get a second opinion before I go into production for christmas.

Cheers

Jedel

 

[danvlas]

ftp://ftp.artrom.ro/DbLinks97.zip
might prove helpful...

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html