Splitting a database and updating the FE via e-mail

[Sierras]

Hi,

I'm almost finished a database that I've been working on for some
users I've been communicating with over e-mail. I think the tables
are just about at the stage where they could be considered finallized.
(I hope).

Anyway, I haven't done the splitting of any databases yet and just
have a concern over updating the FE via e-mail in the future.

The users will have the databas on their own local hard drives. But
the path to the BE will be different for each user. So when I send
them a new FE, they will have to re-link the BE tables manually. Is
there any way to automate this with a button? They are not very
computer friendly users.

Thanks

 

[Joseph Meehan]

Hi,

I'm almost finished a database that I've been working on for some
users I've been communicating with over e-mail. I think the tables
are just about at the stage where they could be considered finallized.
(I hope).

Anyway, I haven't done the splitting of any databases yet and just
have a concern over updating the FE via e-mail in the future.

The users will have the databas on their own local hard drives. But
the path to the BE will be different for each user. So when I send
them a new FE, they will have to re-link the BE tables manually. Is
there any way to automate this with a button? They are not very
computer friendly users.

Thanks

I have never had that problem as I was able to use a standard path, but
I suspect that someone has set up a system where the path could be
referenced from a file on a standard location on the users computer.

 

[Michael Gramelspacher]

The users will have the databas on their own local hard drives. But
the path to the BE will be different for each user. So when I send
them a new FE, they will have to re-link the BE tables manually. Is
there any way to automate this with a button? They are not very
computer friendly users.

Rather long message but here is one way I handle relinking.
Mostly I tell users to put FE and BE in same folder.
Mike Gramelspacher
Ferdinand, Indiana

First of all download this code:

1. Call the standard File Open/Save dialogbox
http://www.mvps.org/access/api/api0001.htm

2. Relink Access tables from code
http://www.mvps.org/access/tables/tbl0009.htm

3. Reconnect attached tables on Startup
http://allenbrowne.com/ser-13.html

This function goes in general module with the above code downloads:
'-------------------------------------------------------------------
' Procedure : InitOk
' DateTime : 12/6/2004 08:15
' Author : Allen Browne
' Purpose : Set InitOK to True or False depending whether a recordset
' : based on a linked table can be successfully opened.
'--------------------------------------------------------------------
'
Function InitOk(strLinkedTable As String) As Boolean
Dim rs As DAO.Recordset
On Error Resume Next
Set rs = CurrentDb.OpenRecordset(strLinkedTable)
InitOk = (Err.Number = 0)
rs.Close
Set rs = Nothing
End Function


Below is the only code I have on the starting form. If InitOK is True,
then tables do not have to be relinked. It InitOK is False then call
download no. 3. If the backend is in a different folder, open the
starting form and see if the user wants to browse for the file. I have
three possible switchboard forms, so that is reason for the extra code.

'--------------------------------------------------------------------
' Procedure : cmdBrowse_Click
' DateTime : 12/5/2004 21:25
' Author : Mike Gramelspacher
' Purpose : If reconnect does not work for the current directory, then
' : allow user to browse to a different folder and select
file.
'--------------------------------------------------------------------
'
Private Sub cmdBrowse_Click()

Dim FileFound As Boolean

' call function to browse for the new location of data file
FileFound = fRefreshLinks

' new location was found and data file relinked, so open index form
If FileFound Then

' get configuration from config table and assign menu name
' and to global variables
gstrMenuName = Nz(DFirst("MenuName", "tblConfig"), "frmMenu3")
'.. and assign minimum required characters for search forms
' to global variables
gintCharacters = Nz(DFirst("SearchCharNo", "tblConfig"), 1)

DoCmd.OpenForm gstrMenuName
End If

End Sub

'-----------------------------------------------------------------------
--
' Procedure : Form_Open
' DateTime : 12/5/2004 21:45
' Author : Mike Gramelspacher
' Purpose : If back end is already connected, then cancel this open
and
' : just open normal form, else try current folder first, and
if
' : not found there, allow user to browse for file.
'-----------------------------------------------------------------------
---
'
Private Sub Form_Open(Cancel As Integer)

' try to open a recordset with this table
If Not InitOk("tblCommunions") Then

' did not work, so change folder name of data file to current
folder name
' and try to relink data file
If Not Reconnect = True Then
' did not work, so continue opening this form
End If
Else

' get configuration from config table and assign menu name
' and search character minimum to global variables
gstrMenuName = Nz(DFirst("MenuName", "tblConfig"), "frmMenu3")
gintCharacters = Nz(DFirst("SearchCharNo", "tblConfig"), 1)

' it worked, so open index form and cancel opening this form
DoCmd.OpenForm gstrMenuName
Cancel = True
End If

End Sub

 

[Sierras]

WOW - That's a lot of code!!!

Where do I put all these codes and functions?
I'm having difficulty understanding API, functions, and modules. Can I
just copy and paste the codes in 3 different modules?
And the code you have in the starting form. Is that on the On Open event
of the switchboard? The thing is, I may have more than one switchboard
and I wouldn't want the code to run every time the main switchboard opens.

One last thing. I don't think I'll be doing too many updates a year.
Will all this coding slow down the performace of opening the database each
time? If it will, maybe there is a way to send the code in a seperate
file that the user runs only when an update is needed?

Thanks..

 

[Michael Gramelspacher]

WOW - That's a lot of code!!!

Where do I put all these codes and functions?
I'm having difficulty understanding API, functions, and modules. Can I
just copy and paste the codes in 3 different modules?
And the code you have in the starting form. Is that on the On Open event
of the switchboard? The thing is, I may have more than one switchboard
and I wouldn't want the code to run every time the main switchboard opens.

One last thing. I don't think I'll be doing too many updates a year.
Will all this coding slow down the performace of opening the database each
time? If it will, maybe there is a way to send the code in a seperate
file that the user runs only when an update is needed?

Thanks..

The code from the Internet can all go in the same module along with the
InitOK function. That is all you need to do with it. Just paste it into
a new module.
The startup form is a separate form from the switchboard. The startup
form calls the switchboard. The switchboard does not call the startup
form. The startup form is set in the startup options, Tools/Startup. It
is called by the act of opening the database program.
This method has been explained many times in the news groups by experts.
Essentially, a startup form opens, checks if the links are OK, and then
cancels its open event. The user sees nothing. With the code from
Allen Browne's web page, the files are relinked, if necessary, provided
they are in the same folder as the front end. The user sees nothing at
all and the 'real' startup form/switchboard opens in an instant. The
user thinks nothing happened at all, it just worked normally. Only if
the user puts the new back end file in a diffent folder, does the
normally 'hidden' form have to open to display the Browse command
button. At that point the code by Ken Getz and Dev Ashish need to run.
In spite of the length of the code, the user cannot tell whether the
program opens any slower.
The command browse and the form open code are merely to illustrate how
this method is done. You need to make your own 'hidden' form and write
you own form module code. Here is my form which is normally hidden.
http://www.psci.net/gramelsp/temp/ErrorDisplay.png

Mike Gramelspacher
Ferdinand, Indiana

 

[Sierras]

Thank you very much for your clear and detailed explaination.

If this was a forum, I'd ask to make it into a sticky as I'm sure that
many new users of splitting databases all have this concern. And
you've contained all the info here so we don't have to search multiple
newgroups and web sites for the procedure.

Thanks again...

 

[Michael Gramelspacher]

Thank you very much for your clear and detailed explaination.

If this was a forum, I'd ask to make it into a sticky as I'm sure that
many new users of splitting databases all have this concern. And
you've contained all the info here so we don't have to search multiple
newgroups and web sites for the procedure.

Thanks again...

You are welcome. I rarely dare to post a response, but this just
happened to be something I had worked through before.

Mike Gramelspacher
Ferdinand, Indiana

 

[Albert D.Kallal]

The users will have the databas on their own local hard drives. But
the path to the BE will be different for each user.

Why is the path location going to be different? I certainly agree that you
need to have provisions to re-linking the tables to a back end of your
choice, and further you need this ease of use in the finished product.

However, you should STRONGLY encourage, or even setup your own install
routines to FORCE the whole application to be in the SAME directory location
for each user.

If you write a long email post with instructions on how to do something, it
will apply to ALL of your users. It makes no sense to have a custom location
and install for EACH user.. Doing so will mean considerable additional
support efforts on your part.

The best approach here is to have the split database installed to the SAME
location on each computer. That way, you can send the front end part
pre-linked to the KNOWN back end location. Traditionally, in the past that
location has been:

c:\Program Files\MyCoolApplcationName\

I mean, where does most other software you purchase install to? You have to
put on a developers hat here!!

So, I see NO reason as to why the back end location would be different for
each user. The only exception to this is that if you plan to place the data
in the "My Documents" folder.

If you use my documents, each new user actually does have a different path
name.

So, it really depends on "where" you install the software two. You should
consider having the same location for each user. I use the free open source
"Inno" installer to copy my front end to the same location. The result of
this is that installing is very easy. Here is some screen shots that shows
how easy it is to install (actually, "copy" the new front end).

http://www.kallal.ca/ridestutorial/upgrade.html

So, while you still do need and want to add provisions in your code that
checks if the back end is moved, or not where it supposed to be, but by
standardizing all users, at least when you do something, it should apply to
all users..and not a "one by one" affair.

 

[Sierras]

Thanks for your comments. However, most users will want to put the data
in a location that will be backed up on a regular basis just as all their
other data. And normally, users do not have their data in the program
files. In fact, the program files are almost always on the main "C" drive
which you wouldn't want to back-up anyway. You may want to make an image
of the "C" drive once a week or month or so, but not back-up every day.
Most people I know have a seperate drive for their data. But I agree, if
everyone would put the info in the same place it would make my life a lot
easier. But the beauty of this code is that they could all be different
and still not be a major headache for me.

 

[Sierras]

Well looks like I celebrated too early.
When I run the code, I get a compile error that states:
"Only comments may appear after End Sub, End Function, End property"

Obviously I'm doing something wrong since I've searched the newgroups
and could not find anyone else having problems with this code from Ken
Getz.

Maybe if there is an example of a spit database that shows just how
the code is called would help. But I can't find anything to really
illustrate the "How-To" in detail.

This would also help me understand how the start-up form is hidden at
start-up, calls the switchboard and goes hidden again. And how does
it "Call the function" that I've read in so many examples in mvps.org.
There's even another example by Peter Vukovic that I've tried and
can't seem to get to work either. See this link
http://allenbrowne.com/ser-13.html

So even though I have all the info, and all the codes, I guess I'm
still in the dark as to how about making this work. But I'm still
having fun!!

 

[Sierras]

Here is the line of code where Access gives me a compile error:

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Any ideas?

 

[Douglas J. Steele]

Where in the module do you have that line of code? It needs to be at the
beginning, before any other code.

 

[Sierras]

I used the code from here:
http://www.mvps.org/access/api/api0001.htm

And just copied the whole thing into a module.
I get the message and it gets stuck here:

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

I did a search in google and found that someone else had this same
problem with the same code but don't know how they fixed it. Here is
the similar error:

http://groups.google.com/group/micr...+appear+after+end+sub&rnum=1#830ffd6fbd4e8d23

Basically, this is what is going on. I have all the code to do what I
need for it to do, but can't find any step-by-step instruction as to
how to do it. It's like having all the parts of a car with no
instruction as how to put it together.

I downloaded all the code from the net:

1. Call the standard File Open/Save dialogbox
http://www.mvps.org/access/api/api0001.htm

2. Relink Access tables from code
http://www.mvps.org/access/tables/tbl0009.htm

3. Reconnect attached tables on Startup
http://allenbrowne.com/ser-13.html

Put them all in a module called basReconnect

Then created a Startup form which is being called from Tools|Startup
In the load event of the startup form, I put a code;

Private Sub Form_Load()
Call fRefreshLinks
End Sub

And on the Open Event I put:

Private Sub Form_Open(Cancel As Integer)
Cancel = True
End Sub

This is as far as I got since I keep ketting the error and can't
really go further. I still have to figure out where to put the call
frmSwitchBoard as well as where to put the code for the button to
browse for the back end if it's not in the same directory.

So I guess I got a long way to go.
If there were only a simple sample database that does this, I could at
least figure out how it was done and learn by example.

I found one that works well at:
http://www.rogersaccesslibrary.com/

But for this one to work, the user has to put the BE in a pivotdata
folder in the same folder as the FE. So it won't really work in a
network environment.

Any help would be appreciated...

 

[Sierras]

Well after just re-ordering the code a bit, I finally got it working.
But instead of conneccting right away, I always get a message box to
re-connect the tables. Even if they are in the same directory.
This is what I've got now. Please let me know if I'm missing
something.

Private Sub Form_Open(Cancel As Integer)
Cancel = True
Call fRefreshLinks

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSwitchBoard"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[Sierras]

Well after playing around with your code in the Init function, I've
almost got it working. But not quite. For one, I don't have a config
table. Do I need it? Where would I put it and what would it consist
of.

This is what I've got so far, and it's almost working. But the
startup form always pops up even if it finds the tables. Let me know
what you think. Thanks

'--------------------------------------------------------------------

Private Sub Command2_Click()
'--------------------------------------------------------------------
' Procedure : cmdBrowse_Click
' DateTime : 12/5/2004 21:25
' Author : Mike Gramelspacher
' Purpose : If reconnect does not work for the current directory,
then
' : allow user to browse to a different folder and select
file.
'--------------------------------------------------------------------

Dim FileFound As Boolean

' call function to browse for the new location of data file
FileFound = fRefreshLinks

' new location was found and data file relinked, so open index form
If FileFound Then

' get configuration from config table and assign menu name
' and to global variables

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSwitchBoard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End Sub

'-----------------------------------------------------------------------

' Procedure : Form_Open
' DateTime : 12/5/2004 21:45
' Author : Mike Gramelspacher
' Purpose : If back end is already connected, then cancel this open
and
' : just open normal form, else try current folder first,
and if
' : not found there, allow user to browse for file.
'------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)

' try to open a recordset with this table
If Not InitOk("One of the tables in my database") Then

' did not work, so change folder name of data file to current
folder Name
' and try to relink data file
If Not Reconnect = True Then
' did not work, so continue opening this form
End If
Else

' get configuration from config table and assign menu name
' and search character minimum to global variables

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSwitchBoard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Cancel = True
End If

End Sub

 

[Sierras]

Also, when I but the FR in a completely different folder, I get an
error that the reconnect could not find the table. It highlights this
code:

db.TableDefs(i).RefreshLink

Almost not having fun anymore...

 

[Michael Gramelspacher]

The logic and flow seems to be wrong. Whether Reconnect is successful
or not, the form with browse opens. When I can get to it, I will try to
work it out, but you can probably get it yourself.

Mike Gramelspacher

 

[Sierras]

That's because I DON'T KNOW WHAT I'M DOING!!

But I'm learning. And I'm determined to get this to work.

 

[Michael Gramelspacher]

That's because I DON'T KNOW WHAT I'M DOING!!

But I'm learning. And I'm determined to get this to work.

Replace Open_Event code with this and give it a try. Just change to
your table in InitOK and and make it your switchboard file name to open.
Mike Gramelspacher

'-----------------------------------------------------------------------
--
' Procedure : Form_Open
' DateTime : 6/3/2006
' Author : Mike Gramelspacher
' Purpose : If back end is already connected, then cancel this open
and
' : just open normal form, else try current folder first, and
if
' : not found there, allow user to browse for file.
'-----------------------------------------------------------------------
---
'
Private Sub Form_Open(Cancel As Integer)

' try to open a recordset with this table
If Not InitOk("name of your table here") Then

' did not work, so maybe data file is in this folder
If Not (Reconnect = True) Then
' did not work, so continue opening this form
Else
' links OK now, so open switchboard
DoCmd.OpenForm "your switchboard file name here"
' and cancel opening this form
Cancel = True
End If
Else
' links are already OK, so open switchboard
DoCmd.OpenForm "your switchboard file name here"
' and cancel opening this foem
Cancel = True
End If

End Sub

 

[Sierras]

Thanks for helping. It's getting better.
When the BE is in the same folder, it works.
When the BE is in a sub folder, the startup form comes up and the
click to browse works, but then doesn't disappear after the user links
the tables.
But when the BE is in a completely different folder or over a network,
I get an error message that the BE database could not be found and the
VB window highlights this Reconnect Function code by PETER VUKOVIC in
yellow:

db.TableDefs(i).RefreshLink

Does this code need any tweaking or were you able to use it as is?I
didn't do the Macro:AutoExec. But I'm assuming that just calling the
Reconnect function on the Open Startup form event is the same.

Here's the complete code from the internet:


Function Reconnect()
'**************************************************************
'* START YOUR APPLICATION (MACRO: AUTOEXEC) WITH THIS FUNCTION
'* AND THIS PROGRAM WILL CHANGE THE CONNECTIONS AUTOMATICALLY
'* WHEN THE 'DATA.MDB' AND THE 'PRG.MDB'
'* ARE IN THE SAME DIRECTORY!!!
'* PROGRAMMING BY PETER VUKOVIC, Germany
'* (e-mail address removed)
'* ************************************************************
Dim db As Database, source As String, path As String
Dim dbsource As String, i As Integer, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
'*************************************************************
'* RECOGNIZE THE PATH *
'*************************************************************

For i = Len(db.Name) To 1 Step -1
If Mid(db.Name, i, 1) = Chr(92) Then
path = Mid(db.Name, 1, i)
'MsgBox (path)
Exit For
End If
Next
'*************************************************************
'* CHANGE THE PATH AND CONNECT AGAIN *
'*************************************************************

For i = 0 To db.TableDefs.Count - 1
If db.TableDefs(i).Connect <> " " Then
source = Mid(db.TableDefs(i).Connect, 11)
'Debug.Print source
For j = Len(source) To 1 Step -1
If Mid(source, j, 1) = Chr(92) Then
dbsource = Mid(source, j + 1, Len(source))
source = Mid(source, 1, j)
If source <> path Then
db.TableDefs(i).Connect = ";Database=" + path
+ dbsource
db.TableDefs(i).RefreshLink
'Debug.Print ";Database=" + path + dbsource
End If
Exit For
End If
Next
End If
Next
End Function