Split a database

[Jac Tremblay]

Hi,
I have an Access application that I distributed to 3 users. I split the
database using the Wizard and it went well.
I first started by creating a backup so I ended up with 2 similar
applications with different names, let's say App V01.mdb and App V02.mdb.
I used App V02.mdb for the split. So I ended up with App V02 Main DB.mdb and
App V02.mdb, the front end part. With the front end, I built an mde that I
distributed to my three users. It all works fine and they can work
simultaneously with a central set of data.
Now, if I want to make changes to the front end part and redistribute the
new version to the users without changing the database part (the backend),
what should I do? Use App V01.mdb or the split App V02.mdb for my changes?
If I use the App V01.mdb (that is not split) and make the changes it needs,
will I be able to split it and have its front end point to the old DB on the
server?
Is there an option one can set to determine where the main DB is and its name?
I found lots of information on the split DB subject, but not that detail.
Thanks.

 

[Allen Browne]

Make further changes in your front end, which I think is "App V02.mdb".

The name of the current database file is:
Currentdb.Name
In Access 2000 and later, you can also use:
CurrentProject.FullName
or get just the path from:
CurrentProject.Path

If Table1 is a linked table, you can parse the name of the back end data
file from:
Currentdb.TableDefs("Table1").Connect

 

[Albert D.Kallal]

I would just work with the ONE new front end.

You don't need the originals anymore (except for a backup).

You made the mde...and that goes to each user...

You now make a backup of your front end MDB..and then continue to work on
it...

You might for testing purposes make a copy of the back end, so you don't
accident damage real data before you deploy you next "great" version of the
mde......

I talk in length about the development process after you split here

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

For all said and done...you will NOT be using the un-split original
application anymore, and have no need to do so...

 

[Jac Tremblay]

Hi Allen,
I tried the code:
' **********************************************************
Sub TestDeJac()
MsgBox "CurrentDb = " & CurrentDb.Name & vbCrLf & vbCrLf & _
"CurrentProject.Fullname = " & CurrentProject.FullName & _
vbCrLf & "CurrentProject.Path = " & CurrentProject.Path
End Sub
' **********************************************************
The answer refers to the front end part of the application in all 3 variables.
Then you mention that:
' *****
If Table1 is a linked table, you can parse the name of the back end data
file from:
Currentdb.TableDefs("Table1").Connect
' *****
I do not understand what I can do with that. Anyway, I get an error when I
try that part of the code (of course, I changed Table1 by tblMaladie which is
a valid table name in my system. There are about 20 of them. The error says
that the property Connect is not valid.
Actually, I split the application and stored the BE in a different (second)
folder to make a test. I have yet another copy of the BE in a third folder
where the data have been changed.
I want to know if it is possible (apparently it is) to code something simple
to close the current BE an reconnect on another copy of the BE that is stored
in another folder.
Thanks for your reply. It is always interesting to see what one can discover.

 

[Jac Tremblay]

Hi Albert,
I take good note of your comment. I read all the informations from the links
you posted. That is very interesting.
If I do not find an easy answer to my problem, I will use "Tony's free FE
updater".
You say that on your web site:
' *****
.... And, the solution here is to either roll your own code to check for a
new version and copy it from the server directory where the BE resides. That
is what I do in my startup code, and it was only a few hours of work. (Albert
dixit)
' *****
Correct me if I'm wrong.
I thought that the solution could be coded in the FE part. The code would
close the current database (the BE), disconnect from it and reconnect to the
one mentioned in the code, path and BE name.
AFAICS, your solution does not work like that. It checks if a new version is
present in the folder where the BE resides and if so, copies it to the
current application's directory. I now wander how the application where this
is run from closes itself and reopens the new one.
My last problem is that I do not have access rights to the directory where
my application is installed. I have to do that part of the job on the user's
workstations. They nicely let me work but I can't keep them too long like
that.
So I wish I could just code something like this:
' *****
....
CurrentDB.Close
CurrentDB.Disconnect
CurrentDB.Connect (strPath & strBeName)
....
' *****
Am I dreaming?
I think will read again some parts of the information you provide.
Thank you for your comment, I really appreciate.

 

[Allen Browne]

Using the name of your linked table is correct.

If you are using Access 2000 or 2002, you may need to add a reference to the
library:
Microsoft DAO 3.6
More info about references:
http://allenbrowne.com/ser-38.html

 

[Jac Tremblay]

Hi Allen,
I do have a reference to Microsoft DAO 3.6.
I am using Access 2003 but the database format is 2000.
I tried everything i could think of but nothing seems to work.
I'll keep trying.
Thanks for your help.

 

[Allen Browne]

Use the Immediate Window (Ctrl+G).
Enter the expression after a question mark.

This should also work:
? dbEngine(0)(0).TableDefs("tblMaladie").Connect

 

[Jac Tremblay]

Hi allen,
They both work in the immediate window.
It works as well with
? CurrentDB.TableDefs("tblMaladie").Connect
and numbers from 0 to 23 like
? CurrentDB.TableDefs(12).Connect
So why would that not work in a procedure?
Actually, it works when I use Dev Ashish's code:
'*************** Code Start **************
' Code Courtesy of Dev Ashish
Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String
Set dbs = CurrentDb()
On Error Resume Next
stPath = dbs.TableDefs(strTable).Connect
If stPath = "" Then
fGetLinkPath = CurrentDb.Name
Else
fGetLinkPath = stPath
End If
Set dbs = Nothing
End Function

Sub sListPath()
Dim loTd As TableDef
CurrentDb.TableDefs.Refresh
For Each loTd In CurrentDb.TableDefs
Debug.Print fGetLinkPath(loTd.Name)
Next loTd
Set loTd = Nothing
End Sub
'*************** Code End **************
WHat could be the problem?
Thanks for your concern. I'll keep looking.

 

[Allen Browne]

Good. you have it working in the immediate window.

I'm sure you will be able to track it down in the procedure now as well.

 

[Albert D.Kallal]

I thought that the solution could be coded in the FE part. The code would

close the current database (the BE), disconnect from it and reconnect to
the
one mentioned in the code, path and BE name.

The problem is not connecting. remember, there is a copy of the FE on EACH
MACHINE.

So, it has little to do with connecting. The simply approach I use in my own
code is check for a new version, and then to upgrade, I simply exit
the current database, and launch a upgrade program that copies the FE..and
you are done.

My last problem is that I do not have access rights to the directory where
my application is installed.

We just spent a HUGE amount of time explain that the FE is installed on each
computer. Those FE's link to a back end on the server. So, since users MUST
HAVE full rights to where the BE is on the server, then this is virtually
zero chance that users will not be able to download (copy) your new FE.
Cleary it is obvious that this new FE is going to be placed on the server in
the same location as the BE for users to download. (perhaps you are talking
about a non multi-user application - but, to receive a copy of a file..they
need some way to do this!!!).

In addtion, that FE that was just copied from the server to the
workstation(S) needs to be pre-linked to the back end tables, or you simply
provide some link code at startup that re-linkes to the BE.

I assume right before you deploy the ne FE, you link it to the correct
tables...right?

 

[Jac Tremblay]

Hi Me,

Here is THE simple solution to this problem.
Open the FE and in the Access menu, select Tools | Database Utilities |
Linked Table Manager. A form will appear that lists all the tables in the
Backend database and the path to each of them. Click on Select All (a check
mark will appear besides each table name), click on the "Always prompt for
new location" check box in the bottom and then click OK. Then, navigate to
whatever location you want your application to connect to and that's it. All
the links will be updated. That is so simple. You can repeat that procedure
as often as you need. No need for code.
Hope this helps. It did for me.