Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003

    Talking Unanswered: Make a "selectable back end" for development


    I have a database that is in use, but there is still on-going development. I would like to split the database, placing the backend on the network drive, but also keeping a "test" copy on my laptop. I will be making executable files for the users using Visual Studio Tools for Office (so the user's don't all have to have Access licenses) - I don't know if that makes a diffference to the question, but I thought I would point it out.

    Ideally, if the code is run from the executable version, the users would be forced to use the database on the network drive, but if I ran from the "original" mdb file, I would be able to select the data source (either immediately at logon or later). We don't have SQL server, so I need to stick with standard MS Access. Alternatively, if the user logs into the database and has "admin" as his authority level, I would like to setup a control to allow the user (me) to select the datasource - AND to display the currently selected data source.

    It looks like this might be accomplished using tdf.connect. Has anyone done this, and is there a reasonably simple solution?

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    I was going to post my code for linking tables but it is very specific to my situation and may be more confusing than helpful. The basics of what I did are:

    Create a table of tables to link. That way you can loop through the names and given a database location you can link to the tables with DoCmd.TransferDatabase. This method does the actual linking. You will have to delete any existing links with DoCmd.DeleteObject before you link otherwise your new link will have a 1, 2, 3 etc. on the end.

    Also, I would suggest a table with your two database selections or an enumeration variable as the aprameter for the procedure (this will give you the cool choices when you are programming).

  3. #3
    Join Date
    Oct 2003

    Interesting Idea, but gives an error code

    I found code similar to this in "Microsoft Access 2002 Visual Basic." The only changes I made were naming of the checklinks function - I named it setlinks, and instead of checking if the existing links were good, I jumped right into asking the user for the correct location (I got rid of an if/endif combination).

    I don't know why this isn't working, but perhaps one of you might.

    The last poster suggested an interesting option, but i think I want a shortcut. Otherwise I'll probably just use the linked table manager when I want to implement new changes.

    Public Function CurrentDBFolder() As String
    ' Returns the folder of the currently open database.

    Dim strPath As String

    strPath = CurrentDb.Name

    ' Keep removing the rightmost character until it is a backslash.
    Do While Right$(strPath, 1) <> "\"
    strPath = Left$(strPath, Len(strPath) - 1)

    CurrentDBFolder = strPath

    End Function

    Public Function SetLinks() As Boolean
    ' Set new links Returns true if
    ' links are okay (or links are successfully refreshed).

    On Error GoTo setLinksErr

    Dim tdf As TableDef
    Dim strNewMDB As String
    Dim fd As FileDialog
    Dim strsql As String

    ' Loop through each table in the current database.
    ' Loop through each table in the current database.
    For Each tdf In CurrentDb.TableDefs

    ' If we don't have an MDB name yet, display a message and
    ' then ask the user to pick a new file.
    If Len(strNewMDB) = 0 Then
    MsgBox "Please select a data file to continue.", vbCritical

    ' Create a FileDialog object.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
    ' Set dialog box properties.
    .AllowMultiSelect = False
    .InitialFileName = CurrentDBFolder()
    .Filters.Add "Access Database File (*.mdb)", "*.mdb", 1
    .Title = "Select Back-End Data File"
    .ButtonName = "Link Tables"

    ' Show the dialog box.
    If .Show = False Then ' User clicked Cancel.
    Exit Function
    ' Selected file is in the SelectedItems collection.
    strNewMDB = .SelectedItems(1)
    End If
    End With
    End If

    ' Refresh the link using the selected back-end database.
    tdf.Connect = ";DATABASE=" & strNewMDB
    Next tdf
    SetLinks = True ' Relinking was a success.

    Exit Function

    MsgBox "Error #" & Err.Number & ": " & Err.Description, vbCritical
    Resume setLinksDone

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts