How to merge backend and frontend again ?

[SpookiePower]

Long time ago, I created my database in access 2000. I did a split into a frontend and backend.

Now I only have access 2007, and would like to merge my front- and backend into one file again. But how do I do this in 2007 ?

 

[DJkarl]

Why would you want to recombine them? You are much better having a seperate front end and back end, the version of Access you're using shouldn't change that model at all.

 

[boblarson]

I agree with DJK on this. You really should have it split - even if you are the only one using it. Many times that will be the difference between losing everything you have and only losing a frontend. Losing data is usuallly much more painful than losing the frontend and with it as a single database, your risk for corruption and losing it all is much greater.

 

[SpookiePower]

Thanks to both of your for the advice.

I know about, the benefit of having the database split in two parts, but the reason for me to merge the front and backend, is because a friend of mine, would like to see my database. For him it would be more difficult to get a database that is split in two, than to get just one file.

But I just can't seem to figure out, how to do this in 2007 - can you help me ?

 

[boblarson]

Thanks to both of your for the advice.

I know about, the benefit of having the database split in two parts, but the reason for me to merge the front and backend, is because a friend of mine, would like to see my database. For him it would be more difficult to get a database that is split in two, than to get just one file.

But I just can't seem to figure out, how to do this in 2007 - can you help me ?

All you need to do is, in the front end delete all of the linked tables. Then go to IMPORT > Access > and then select your backend and select IMPORT instead of LINK and select all the tables.

 

[gemma-the-husky]

as stated already, its easier to keep them separate

but, do this manually

a) take the front end, and delete all the linked tables
b) do file/import (get external data) to collect the real data from the backend

job done

you cant just do b, because it will import the tables with variant names - so you still have to delete and rename


... and I see Bob's already posted this

 

[boblarson]

you cant just do b, because it will import the tables with variant names - so you still have to delete and rename

If you delete the tables first, importing won't change the names. Unless you are importing from SQL Server, the names will still be the same names as in the backend database, so there should be no issue there.

 

[RuralGuy]

You may wish to incorporate code that relinks the BackEnd automatically regardless of where it is located. Then you could just send both the FE and BE in a zipped file and it would work. Lots of free code out there on this one.

 

[SpookiePower]

thanks to all of you

 

[wiklendt]

You may wish to incorporate code that relinks the BackEnd automatically regardless of where it is located. Then you could just send both the FE and BE in a zipped file and it would work. Lots of free code out there on this one.

yup. i use this code in the on open even of my startup form.

i have it set that if it can't find the local backend, it will then try the server. this is because i develop on my laptop where i don't have access to the server. but you can change/delete that and have just the one location (which, in this code is "look for the backend file in the same place as this front end file is). it also has a 'progress bar' (just make a couple of rectangle controls and name one "Fill" and the other "FillTo") - nifty, eh?

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

    ' ------------------------------------------------------------------
    ' linking code adapted from namliam's code on AWF
    ' http://www.access-programmers.co.uk/forums/showthread.php?t=180962
    ' ------------------------------------------------------------------
    Dim tbl As TableDef
    Dim x As Long, MaxX As Long
    Dim tblDB As String

    Me.Visible = False
[COLOR=Red]         
    tblDB = myFolder & "\ORDERS_ReferenceLabs_be.mdb"
    
    If Dir(tblDB) = "" Then ' local BE not found, use server BE.
        tblDB = "\\wm-icpmr\Data2\SHARED\Cidmls\Molecular Biology\Orders\ORDERS_ReferenceLabs_be.mdb"
    End If
[/COLOR]    
    MaxX = 1 ' first count all attached tables
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then MaxX = MaxX + 1
    Next tbl
    x = 1 ' Now update them
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then
'            tblDB = myFolder & Mid(tbl.Connect, InStr(1, tbl.Connect, "\"))
            If tbl.Connect <> ";Database=" & tblDB Then
                Me.Visible = True
                Me.Repaint
                tbl.Connect = ";Database=" & tblDB
                tbl.RefreshLink
            End If
            x = x + 1
        End If
        Me.Fill.Width = x / MaxX * Me.FillTo.Width
    Next tbl
    If Me.Visible Then
        Me.lblWait.Caption = "Done... Opening login form."
        Me.Repaint
        MaxX = Timer + 2
        Do While Timer <= MaxX
        Loop
    End If
    
    DoCmd.OpenForm "frmLogin"
    DoCmd.Close acForm, Me.Name

Exit_Form_Open:
    Exit Sub

Err_Form_Open:

    Msg = "Error # " & Str(Err.Number) & Chr(13) & " (" & Err.Description & ")"
    Msg = Msg & Chr(13) & "in Form_frmWAIT | Form_Open"
    MsgBox Msg, vbMsgBoxHelpButton, "RLS Order Records", Err.HelpFile, Err.HelpContext
    Resume Exit_Form_Open
    
End Sub

Function myFolder()
On Error GoTo Err_myFolder

    myFolder = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))

Exit_myFolder:
    Exit Function

Err_myFolder:

    Msg = "Error # " & Str(Err.Number) & Chr(13) & " (" & Err.Description & ")"
    Msg = Msg & Chr(13) & "in Form_frmWAIT | myFolder"
    MsgBox Msg, vbMsgBoxHelpButton, "RLS Order Records", Err.HelpFile, Err.HelpContext
    Resume Exit_myFolder
End Function

 

[DCrake]

Hi Wik,

Your link code works ok but can I suggest you add a further check to see if the server path exists. It may be that the user has been working off line at home, then gone to work, and forgot to plug in their network cable.

Test a) Does the local copy exist

if a = False
Test b) Does the server copy exist

if b = False
Warn user

also CurrentProject.Path also returns the same as CurrentDb.Name less the name of the mdb. No need for InStrRev()

David

 

[wiklendt]

Test a) Does the local copy exist

if a = False
Test b) Does the server copy exist

if b = False
Warn user

already there i'll go back and highlight it in red...

(edit: my bad, it doesn't exist! i see you added a warning if neither locations exist - i did try something like that earlier but couldn't get it to work for some reason, and didn't have the time to fiddle. may do it now though.)

also CurrentProject.Path also returns the same as CurrentDb.Name less the name of the mdb. No need for InStrRev()

that particular bit i think i've remmed out? i think it had something to do with a situation where there was more than one backend.

...and it's namliam's code i've just adapted it for my needs the link to the original post where namliam posted it is given in the comments at the beginning of the code

 

[wiklendt]

sorry to bring up an old post, but i need to add some technical detail to the code i posted.

i just tried using this code in another database at work that i'm splitting (copy paste this and a module straight from one to the other with minor adjustment (i.e., the form to open when finished)) and when the form with this code was run, it spat an error at me "user-defined type not defined" and highlighted this part in the code:

Dim tbl As TableDef

after a bit of fiddling i realised that this database file did not have the reference to

Microsoft DAO 3.6 Object Library

which all the other databases i've used this form/code on had a reference to (and worked).

once i added that reference, it worked without error.

 

[RuralGuy]

To save some of these mysterious actions it is often suggested that these variables be disambiguated. Dim tbl As DAO.TableDef would have thrown an error if the library was not present.