Results 1 to 6 of 6
  1. #1
    quicova is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    53

    button to link to specific backend file

    Hello everyone.



    I'm trying to create a button to select which backend file to link to, anyone has any ideias on how to go about this.
    I'm new to scripting and access. and only today I found this split database option.

    what I'm trying to do is to use the same frontend on several projects.

    So I would like to open the frontend always inked to an empty backend and them press a open button which I could select which file to link to.

    hope anyone can point me in the right direction.

    thanks so much

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Usually, the front end will have linked tables to back end DB's. I don't understand the need to dynamically link tables. Just link to the tables/files you need. When the user opens a form that needs a table, it will already be linked.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've done the same thing using Development and Production back ends.

    It can be as simple or complicated as you want.
    I started out by using the "Linked Table Manager" in Access. If no errors, then the BE can be switched using VBA code.

    My code is intermediate...I use one button and the File Dialog Picker to allow me to pick a BE anywhere, not in just one or two folders.

    Here are cut down versions of my code for 2 buttons:
    Code:
    Private Sub btnSelectBE1_Click()
       On Error GoTo Err_SelectBE
    
       Dim d As Database
       Dim T As TableDef, sFile As String
    
       Set d = CurrentDb()
       sFile = "C:\AccMdb\BE1.accdb"
    
       For Each T In d.TableDefs
          'Debug.Print t.Name & ": " & t.Properties("Connect")
          If Len(T.Properties("Connect") & " ") > 1 Then
             T.Properties("Connect") = ";DATABASE=" & sFile
             T.RefreshLink
          End If
       Next
    
    Exit_SelectBE:
       Set d = Nothing
       Exit Sub
    
    Err_SelectBE:
       MsgBox Err.Description
       Resume Exit_SelectBE
    
    End Sub
    
    '-------------------
    
    Private Sub btnSelectBE2_Click()
       On Error GoTo Err_SelectBE
    
       Dim d As Database
       Dim T As TableDef, sFile As String
    
       Set d = CurrentDb()
       sFile = "C:\Project1\BE2.accdb"
    
       For Each T In d.TableDefs
          'Debug.Print t.Name & ": " & t.Properties("Connect")
          If Len(T.Properties("Connect") & " ") > 1 Then
             T.Properties("Connect") = ";DATABASE=" & sFile
             T.RefreshLink
          End If
       Next
    
    Exit_SelectBE:
       Set d = Nothing
       Exit Sub
    
    Err_SelectBE:
       MsgBox Err.Description
       Resume Exit_SelectBE
    
    End Sub

    Links to other Relinking code:
    http://access.mvps.org/access/tables/tbl0009.htm
    http://access.mvps.org/access/tables/tbl0012.htm
    http://allenbrowne.com/ser-01.html
    http://www.jstreettech.com/cartgenie...rDownloads.asp (J Street Access Relinker)

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I am still curious what situation would dictate the need to connect to different projects at different times from a single FE. I keep imagining a huge FE that would have a complex matrix.

  5. #5
    quicova is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    53
    Hello everyone

    Thanks so much for your answers.
    ssaunf got it right on the money. this link http://www.jstreettech.com/cartgenie...rDownloads.asp (J Street Access Relinker) had exactly what I needed.
    Thanks so much.

    Now try to figure out how to always open in a specific backend file (an empty backend file with just the table structures).
    and create a button to save as or make a copy of the empty backend file to start a new project.

    thanks again

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Now try to figure out how to always open in a specific backend file (an empty backend file with just the table structures).
    and create a button to save as or make a copy of the empty backend file to start a new project.
    You could:

    Create a blank BE of your project dB and save it in a known location. Maybe name it "Blank Project BE.accdb"
    Create a form with a button to select the blank project database, a button to select the folder to save the new database to, a text box for a new name for the database and a button to copy & rename the blank database.

    Use the FileSystemObject:
    -to select the blank database (source)
    -to select just a folder (target)
    -to copy the source file to the target folder, renaming the dB using the name from text box.

    I have A2000 and the FileSystemObject wasn't available until A2003 (I think), so I can't provide any code. But there are lots of examples if you Google "FileSystemObject".

Please reply to this thread with any new information or opinions.

Similar Threads

  1. break the link to a backend table
    By markjkubicki in forum Programming
    Replies: 1
    Last Post: 06-28-2013, 10:49 PM
  2. Replies: 2
    Last Post: 05-21-2013, 02:25 PM
  3. Replies: 5
    Last Post: 11-13-2012, 12:16 PM
  4. Replies: 3
    Last Post: 09-11-2011, 01:25 PM
  5. Replies: 2
    Last Post: 09-11-2011, 05:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums