How to link a front-end application to a server in runtime version?

[neelsfer]

My application is split in a front-end and with a separate database on a server. Does anybody have a code to open a "search box" type textbox where one can drill down to the server, and link to tables in the database. Its easy when you have Access 2007 installed, but i would like to use this in a "runtime version" please.
thx

[neelsfer]

I got this code from a website athttp://www.aislebyaisl e.com/access/vba_backend_cod e.htm#linktable s
Can i adapt it to run from a button? How do i go about it. pls help

Function LinkTables(DbPath As String) As Boolean
'This links to all the tables that reside in DbPath,
'  whether or not they already reside in this database.
'This works when linking to an Access .mdb file, not to ODBC.
'This keeps the same table name on the front end as on the back end.
Dim rs As Recordset

    On Error Resume Next

'get tables in back end database
    Set rs = CurrentDb.OpenRecordset("SELECT Name " & _
                                    "FROM MSysObjects IN '" & DbPath & "' " & _
                                    "WHERE Type=1 AND Flags=0")
    If Err <> 0 Then Exit Function

'link the tables
    While Not rs.EOF
        If DbPath <> Nz(DLookup("Database", "MSysObjects", "Name='" & rs!Name & "' And Type=6")) Then
            'delete old link, assuming front and back end table have the same name
            DoCmd.DeleteObject acTable, rs!Name
            'make new link
            DoCmd.TransferDatabase acLink, "Microsoft Access", DbPath, acTable, rs!Name, rs!Name
        End If
        rs.MoveNext
    Wend
    rs.Close

    LinkTables = True
End Function


Sub CallLinkTables()
Dim Result As Boolean

'sample call:
Result = LinkTables("C:\fis\fis_datav3.accdb")
Debug.Print Result
End Sub

[TheSmileyCoder]

You can code a button, to open a filedialog, then after selecting the file pass the path of the file to the function you have posted.

Depending on your setup a more smooth method could be to search a specific folder/subfolder for all .mdb (or .accdb) files and then display these in a form/combobox allowing the user to select from there.


Now this is just a general direction im pointing you in, because writing code for something like this is not a 5 minute thing, it would take considerably longer time, and I think you need to work on it a bit more yourself.

[ADezii]

1. The following Code will:
   1. Open the Microsoft Office File Dialog filtered for Access Databases only (*.mdb).
   2. Allow you to select a 'single' Access Database.
   3. Loop through all the Tables in the External Database.
   4. Dynamically Link only those Tables which are valid (Non-System, Non-Temporary).
   5. Display in a Text Box exactly which Tables were Linked.
2. Any other questions, feel free to ask.
3. Code posted for your review.
4. Download the Demo (Attachment) that I have created for you to get a good picture of exactly what is going on.
5. Relevant Code:
   

   'First, set a Reference to the Microsoft Office XX.X Object Library
   Dim strButtonCaption As String
   Dim strDialogTitle As String
   Dim strDBName As String
   
   'Define your own Captions if necessary
   strButtonCaption = "Open DB"
   strDialogTitle = "Select Database to Link"""
   
   With Application.FileDialog(msoFileDialogFilePicker)
     With .Filters
       .Clear
       .Add "Access Databases", "*.mdb"     'Allow Access Databases only
     End With
     'The Show Method returns True if 1 or more files are selected
       .AllowMultiSelect = False       'Critical Line
       .ButtonName = strButtonCaption
       .InitialFileName = vbNullString
       .InitialView = msoFileDialogViewDetails     'Detailed View
       .Title = strDialogTitle
     If .Show Then
         strDBName = .SelectedItems(1)
     End If
   End With
   
   If strDBName = "" Then Exit Sub         'No DB selected
   
   'The External Database name is now stored in the variable strDBName
   Dim wrkJet As DAO.Workspace
   Dim dbLink As DAO.Database
   Dim tdf As DAO.TableDef
   Dim strBuild As String
   
   Me![txtResults] = ""        'Clear Results Box
   
   'Create Microsoft Jet Workspace object.
   Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
   
   'Open Database Object for Shared Use.
   Set dbLink = wrkJet.OpenDatabase(strDBName, False)
   
   'Loop thru all Tables in the External Database, but ignore System
   'Tables and Temporary Tables (MSys*, USys*, ~*)
   For Each tdf In dbLink.TableDefs
     If Mid$(tdf.Name, 2, 3) <> "Sys" And Left$(tdf.Name, 1) <> "~" Then
       'If you get here, Link to the Table
       DoCmd.TransferDatabase acLink, "Microsoft Access", strDBName, acTable, tdf.Name, tdf.Name, False
         strBuild = strBuild & "Table " & tdf.Name & " in " & strDBName & " successfully Linked!" & vbCrLf
     End If
   Next
   
   If strBuild = "" Then Exit Sub       'No Tables other than System and/or Temporary
   
   Me![txtResults] = Left$(strBuild, Len(strBuild) - 2)    'Strip vbCrLf & post Results
   
   dbLink.Close
   Set dbLink = Nothing

[neelsfer]

Thx mr Adezii for the trouble
I seem to have an Access 2007 issue here. I will attach the screen pics.

[neelsfer]

I got the above code to work eventually and its great (had reference library issues).
My next question:
how do i change the code to work when using "AccessRuntime" , with the front-end application? Any ideas?