Question Help a newbie with splitting a database

[jps1320]

How to split a database, create install package and move backend db

I have built a multi-user Access 2007 database application.

I split the DB and then use the package wizard to create an install package for the users to install the front end on their computers.

I have created a "configuration" page that is the first to display when they open the front end...basically displays the location of the back end and allows the user to edit if necessary, although since I know where the UNC path I have precoded it to the correct location, but just in case I throw that edit option in there.

So I store this location and use this to construct my database connection string that I call when populating recordsets.

All of my recordset calls work great with this technique. However, I have some bound forms for editing some basic tables and some reports too. These all are broken. They are broken because I am splitting the database on my home computer and then copying the backend to a server location at work and the bound forms and reports are still looking for the back end at the path of my home computer.

I think one option to resolve this would be to split the database after I have moved it to the work server, but I am really wondering if there is a way that I can tell it where to look for the backend. I am thinking that the recordsource of the forms/reports might be able to handle a full path..so I could preset it with the future back end location. (or better yet build the recordsource path with a variable string for the path that can be set)

I hoping this is a just a dumb rookie issue and that someone can enlighten me.

Thank you in advance for your assistance.

 

[jps1320]

OK...I have found a solution.

I derived my solution from a post on MyMSAccessBlog by user "Stephen".
Good post with links to different variations on my issue (ex.: managing multiple back-end DBs like Live DB and Test DB).

I am a new user to this forum so this URL may get blocked, but I'll try anyway.
http://mymsaccessblog.blogspot.com/2008/01/relinking-back-end-tables.html

I actually took his code and made it even simpler.

In case anyone is interested this is what I did:
The issue I was having is caused because of two things...
1.) I am moving the backend database location AFTER splitting.
2.) I am deploying the frontend as runtime application, therefore I had no way for the user to restablish the links.

Since, I know the full UNC path where the backend will ultimately reside I code that in as a string and that will serve as the default location. I have a "configuration" page that shows that default value in a textbox which the user could edit. 99.9% time the user will just click through this, but this could be used in case the backend was relocated or I want to aim a test backend and not disturb live data.

I am already using this path for making database connections for populating recordsets, so all I had to do was link the tables with the same path.

(I also let the user modify the location of the logo image that I use the forms.)

Private Sub cmdSubmit_Click()
On Error GoTo ErrorHandler
Dim varThis As Variant
 
'make sure the user doesn't erase the fields and try to link to blank
If (Me.txtDatabasePath = "" Or Me.txtImagePath = "") Then
    MsgBox ("Both the image path and the database path are required values."), vbExclamation
    Exit Sub
    End If
 
strImagePath = Me.txtImagePath
strLogoPath = strImagePath + "mylogo.gif"
strDBDataSource = Me.txtDatabasePath
 
'cycle through the tables in the frontend and relink to backend
For Each varThis In CurrentDb.TableDefs
      With varThis
        If Trim(Nz(.Connect)) Like ";DATABASE=*" Then
          .Connect = ";DATABASE=" & strDBDataSource
          .RefreshLink
        End If
      End With
    Next varThis
 
'let the user know the procedure succeeded
MsgBox ("Configuration complete."), vbInformation
 
'move on to the next form
DoCmd.OpenForm "frmLogin"
DoCmd.Close acForm, CONFIG
 
Exit Sub
 
ErrorHandler:
    GeneralErrorHandler Err.Number, Err.Description, CONFIG, "cmdSubmit_Click"
    Exit Sub
 
End Sub

The next thing I plan to do is use the after_update event on each textbox to validate the path/file the user provided both exists and is accessible by that user. In theory if I do this, I can remove the blank check at the beginning of my code.