Back end and front end

[kasa74]

Hi friends,

I think I am having some problem with my back end and frond end files. I have a mde file that anytime I enter new data or change some things in the old clients file, it does not update on the mdb file. Anytime I need to change forms or report or any thing at all, I am suppose to copy the BE file and the MDB file to my workstatio, remove the table link, make the changes I want to make on the design mode and then import the files back to the live copy of the mdb file then make another mde file to work with. But When I copy the BE and the MDB files, I don't have the updated client list or new client just added. So as not to affect other users, I can not make changes in the MDE file and even the MDE file will not even let me make any changes in design mode. I don't know If I am making sense but any help will be appreciated

thanks

 

[Guus2005]

What you need is a procedure that automatically reconnects your tables.

'The connected databases must be in the current directory, if not: an error-message is displayed.
Public Function ConnectFromSameDir() As String
    Dim i                   As Long
    Dim ii                  As Long
    Dim dbs                 As Database
    Dim curdir              As String
    Dim myTable             As TableDef
    Dim strDescription      As String
    Dim strLinkedDbName     As String
    Dim strLinkedFullDbName As String

    On Error GoTo Err_ConnectFromSameDir
    
    'Find directory of database that executes this function (current directory)
    Set dbs = CurrentDb
    curdir = Mid(CurrentDb.Name, 1, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
        
    'check if tables are connected
    For i = 0 To dbs.TableDefs.Count - 1
        Set myTable = dbs.TableDefs(i)
        If myTable.Connect <> "" Then
            
            'table is connected, find name connected Database by stripping directories from full path
             strLinkedFullDbName = Mid(myTable.Connect, InStr(myTable.Connect, ";Database=") + 10)
            ii = 0
            While InStr(ii + 1, strLinkedFullDbName, "\") > 0
                ii = InStr(ii + 1, strLinkedFullDbName, "\")
            Wend
            strLinkedDbName = Mid(strLinkedFullDbName, ii + 1)
            
            If Dir(curdir & strLinkedDbName) <> strLinkedDbName Then
                'database from which table is connected not in current directory
                MsgBox "The backend database is not on the same directory as the frontend database"
            Else
                ' is table connected to database in current directory
                If strLinkedFullDbName <> curdir & strLinkedDbName Then
            
                    ' connected to database in another directory or another replica in same directory so reconnect table
                    strDescription = ";Database=" & curdir & strLinkedDbName
                    myTable.Connect = "MS Access;" & strDescription
                    myTable.RefreshLink
                
                    On Error Resume Next
                    myTable.Properties("Description").Value = strDescription 'Not needed.
                    On Error GoTo Err_ConnectFromSameDir
                
                End If
            End If
        End If
    Next

Exit_ConnectFromSameDir:
    Exit Function

Err_ConnectFromSameDir:
    MsgBox Err.Description, vbCritical
    Resume Exit_ConnectFromSameDir
Resume 'for debugging purposes.

End Function

Run it each time the database opens.

This way you never have to manually reattach or reconnect the tables in the FE to the BE.

Enjoy!

 

[kasa74]

thanks for your reply, but where will I put this code in the program. I was not the one that developed this database and I am new at this job.

 

[Guus2005]

What code is executed when you startup your database?
A form a function or an autoexec macro?

That's where you put this function.
In the load event of a form, in the function you call or in the AutoExec macro

 

[boblarson]

Hi friends,

I think I am having some problem with my back end and frond end files. I have a mde file that anytime I enter new data or change some things in the old clients file, it does not update on the mdb file. Anytime I need to change forms or report or any thing at all, I am suppose to copy the BE file and the MDB file to my workstatio, remove the table link, make the changes I want to make on the design mode and then import the files back to the live copy of the mdb file then make another mde file to work with. But When I copy the BE and the MDB files, I don't have the updated client list or new client just added. So as not to affect other users, I can not make changes in the MDE file and even the MDE file will not even let me make any changes in design mode. I don't know If I am making sense but any help will be appreciated

thanks

I'm going to make a quick other guess is that you still are connected to your local tables within the front end and aren't even linked to the backend. I would double-check that because that would definitely cause your problem.

 

[kasa74]

thanks Bob. I am new at this job so I have little understand of their sever or the FE and BE. Can you pls tell me how to check this and how to link to the BE. thanks

 

[boblarson]

Check this out to see if they are linked:

http://www.btabdevelopment.com/main...llifyouhavelinkedtables/tabid/80/Default.aspx

And then here to link to your server - preferably by UNC (universal naming convention).

http://www.btabdevelopment.com/main...linktoyourbackendviaUNC/tabid/69/Default.aspx

 

[kasa74]

thanks Bob. I checked, my tables are not linked and I tried linking it through the UNC but when I get to linked tables, I could only see one table out of about 30 tables that I have in my database. Also I want to know If I am to do this in my MDE or my MDB file.

 

[boblarson]

You can't do it in your MDE file as it is considered a design change and Access won't let you do it. You have to do it in your mdb file and then create an MDE from it.

Are you sure that you had clicked the SELECT ALL button when you relinked? Also, sometimes it will want that info over and over again for each table (not sure why) but if you copy the whole path when doing the first, you just have to paste and hit enter for each other one.

 

[kasa74]

Thanks Bob. I was able to fix the problem. God bless you.

 

[boblarson]

Happy that we could help.