Back end and Front End (1 Viewer)

[ASherbuck]

Alright, I'll try to explain this since I don't have a DB to upload as an example.

I'm trying to implement a new DB that allows the user at the front end to use a drop down control to select which back end Data.mdb file they wish to use. This is brought about as we have two seperate locations that have different data (sales records).

My idea so far is to simply bring each DB from each location and rename them specific to their location, then in a front end create the linked tables, therefore being able to access both data files at the same time, although my capabilities limit me to making two seperate forms, 1 for each table.

Is there anyway for me to have a form that is capable of switching between which table it populates it's text boxes with?

For ex "Combo1 has a list of Table1 and Table2" from the seperate mdbs. The Form contains unbound text boxes that display the fields SaleDate, SaleTotal, SaleID. When I switch from Table1 and Table2 I would like the information to change with respect to which table I have chosen.

If Im making a mess of this I apologize, I just hope this isn't something rediculously complicated.

 

[gemma-the-husky]

assuming you have the same data tables in each backend, then the easist way is to have a table identifying the tables you want to connect to with

a) their names in the front end and
b) the corresponding names in the back end

-----------
the path currently used to connect a table is in .connect property of the tabledef object

so assuming you have a table called "constants"

then currentdb.tabledefs("constants").connect, returns a string showing the current back end path. if this is the one you want then dont do anything

if it isnt, then you need to
a) drop the links to the curreently connected tables and then
b) relink them using the new data path

---------
assuming jet backend (rather then eg sql) then this code will walk through the linked tables, drop them and reconnect them. ive not included the field defs in the links table - you should be able to do this from examining the code. this has some error handling built in

hope this helps

[edited - added field defs now]
fields in mylinkedtables are just
ourtable 'the name we want to see in the code database
remotetable 'the table name in the backend database (generally the same)

Private Sub btnLink_Click()

Dim rst As Recordset
Dim db As Database
Dim tdf As TableDef
Dim tablename As String
Dim linkages As Integer
Dim prelinked As Integer

'this isnt used here - in some versions i count how many tables i will be linking, and show a progress bar
   
    Set db = CurrentDb
    Set rst = db.OpenRecordset("MyLinkedTables")
    
    On Error GoTo fail
    DoCmd.Hourglass True
    linkages = 0
    prelinked = 0
    rst.MoveFirst
    
    Do Until rst.eof
        tablename = Nz(rst!ourtable, vbNullString)
        If tablename <> vbNullString Then
            
            On Error GoTo baddrop
            DoCmd.RunSQL "drop table [" & tablename & "]"
        
nodrop:
            On Error GoTo fail
            Set tdf = db.CreateTableDef(rst!ourtable)

'in this case i am taking the backend path and dbsname from textboxes        
            tdf.Connect = ";DATABASE=" & txtFolder & txtDB
            tdf.SourceTableName = rst!remotetable
        
            db.TableDefs.append tdf
            linkages = linkages + 1
        End If
        rst.MoveNext
    Loop
    DoCmd.Hourglass False
    
    MsgBox (linkages & " tables relinked. ")
    
exithere:
    Exit Sub

fail:
    DoCmd.Hourglass False
    Call MsgBox("Cannot cannot complete the linkage of the tables. Please check table 'Mylinkedtables'. ")
   
    Call MsgBox("Error Message: The actual system error message was as follows: " & vbCrLf & vbCrLf & _
        "Error: " & Err & "  Desc: " & Err.Description)
    Resume exithere
    
baddrop:
    Resume nodrop
End Sub