AccessRuntime

[olorin]

Hi there folks,
I have developed a Database using Access 2007 on my home PC which runs WinXP Pro and Office Pro 2007. I have then split the database with the normal frontend backend, tables BackEnd. forms etc. FrontEnd
I now wish to install the front end onto a number of machines in the office and the back end onto the server.
My question is twofold really;
1...When I package the database for distribution using the pakaging wizard in Access 2007 at home, and then take the packaged app to the office, will I be able to change the linked-tables path when the runtime version is on machines that do not have Access installed on them?
2...If any further design is necessary will I have to do a complete new package and then re-install to the office machines?
I've never done a Runtime App before.
Maybe there is an article or tutorial on the web somewhere for this?
Your time is, as always appreciated.

 

[KenHigg]

1. You will have to have some kind of custom routine or code/forms that allow you to link to the backend.
2. Yes you will have to put a new copy of the fe on each machine. There are some pretty good ways to do this with out having to walk around to each machine but it requires an intermediate level of coding, etc to set it up.

 

[olorin]

Thanx for your quick response Ken,
As regards "custom routine or code/forms" could you elaborate a little?
I would consider myself a little above beginner level at VBA, but a tad below intermediate".
I am quite proficient at building forms though, indeed all of the relevant data entry and report generation are carried out for the front end user by entering selections in either combo boxes or clicking buttons.

 

[KenHigg]

Here is some sample code I wrote to re-link tables.

Public Function fncRelink() As Integer
'====================================================================================================
' Name: fncRelink_Click
' Use: Removes and relinks BE tables
' Created By: Ken Higginbotham
' Modified By:
' Date: 10/17/2007
' Called By: fncStartup, frmLoginTableError!cmdLink_Click
' Notes: This relink routine is called from two different places.
'  1. It is called every time the database is opened by the autoexec macro calling
'     basStartup.fncStartup. All linked tables are removed and relinked.
'  2. It is (can be) called the second time from the login error form that allows the user to try to
'     modify the file/location of the BE .mdb and relink with the new file information.
' Either way it will return a 0 if the relink was successfull or the error number if the relink failed.
' Calls:
' Update:
'====================================================================================================
    On Error GoTo err_fncRelink
    
    Dim strTableNames(5) As String      'Table names to relink
    Dim intCounter1 As Integer          'Loop counter var
    Dim dbsCurrent As Database          'Connection var
    Dim tbfCurrent As TableDef          'Connection var
    Dim strDBLocation As String         'Location of BE file
    Dim strDBLocationHeader As String   'BE .mdb password
                 
    '--Set table names array
    strTableNames(0) = "tblMain"
    strTableNames(1) = "tblPartStatus"
    strTableNames(2) = "tblPartTypes"
    strTableNames(3) = "tblSystemVars"
    strTableNames(4) = "tblUsers"
    
    '--Connection var
    Set dbsCurrent = CurrentDb()
    
    '--Set BE file name and location
    strDBLocation = DLookup("[value_text]", "tblSystemVarsLocal", "[value_name]= 'db_tables_database'")
       
    '--Set BE .mdb password
    strDBLocationHeader = "password"
       
    '--Delete tables
    For intCounter1 = 0 To 4
        For Each tbfCurrent In CurrentDb.TableDefs
            If tbfCurrent.Name = strTableNames(intCounter1) Then
                dbsCurrent.TableDefs.Delete strTableNames(intCounter1)
            End If
        Next tbfCurrent
    Next intCounter1
    
    '--Connect back to tables
    For intCounter1 = 0 To 4
        Set tbfCurrent = dbsCurrent.CreateTableDef(strTableNames(intCounter1))
        tbfCurrent.Connect = ";PWD=" & strDBLocationHeader & ";database=" & strDBLocation
        tbfCurrent.SourceTableName = strTableNames(intCounter1)
        '-- Add this TableDef to the current database.
        dbsCurrent.TableDefs.Append tbfCurrent
    Next intCounter1
    
    '--Send back a 0 if all tables were succesfully relinked
    fncRelink = 0

exit_fncRelink:
    Exit Function

err_fncRelink:
    
    '--Send back the error number if a relink error occured
    fncRelink = Err.Number
    '--In case of an error on just one table delete all the linked tables
    For intCounter1 = 0 To 4
        For Each tbfCurrent In CurrentDb.TableDefs
            If tbfCurrent.Name = strTableNames(intCounter1) Then
                dbsCurrent.TableDefs.Delete strTableNames(intCounter1)
            End If
        Next tbfCurrent
    Next intCounter1
    
    Resume exit_fncRelink

End Function

The main section is the "Connect back to tables" part...



ken

 

[olorin]

Thanx so much for your time and excellent advice.
I will try out the code using my table names etc.
My database is a Shipping and Stocks database and has 22 tables (including Look-up tables and Junction tables) so I presume the "Dim strTableNames(5) As String" would then be changed to Dim strTableNames(22) As String, and name them from 0 through 21.
Sorry if this seems an obvious question to ask

 

[KenHigg]

Not at all - There are a couple other pcs used to make this work. I'll send you the entire sample db if you'd like. It would be good to have your feedback...

ken

edit: In the code above the var strDBLocationHeader is a poor attempt to name the password var something that wasn't so obvious (something a hacker could cipher). I think it was originally something like strDBPassword. (Hope that made sense...)

 

[olorin]

Thanx again Ken,
The security of the database is not a great concern, most (if not all) at work are computer illiterate, they are not even aware of holding the "shift" button down while the Dbase loads to reveal the Dbase window or stop the main form from loading etc.
My boss and others just want to be able to view and use the database "as is" but the boss is too mean (or cannot justify the extra expense) to install Access on all the 28 workstations. My workstation is the ONLY workstation with Access on it (Access2003). I edit and modify the database using Access 2007 at home. Though I have not had to add or edit tables to the backend since I installed it at work, (which makes me proud to think that the basic design is OK).
Your offer to send a sample db to illustrate the code is very generous and appreciated.
Thank You