Re-Link backend with ini file

[Nick Del Vecchio]

OK I've been playing around with this for a couple of days now and
have found a few suggestions as to how to do it. But I still can't
get it to work.

Basically, I have a Frontend called FEApp.mde and a file called
DataPath.ini in one folder and a Backend called BEdata.mdb on another
shared computer. The DataPath.ini file and has only one line in it.

\\PC1234\SharedDocs\BEdata.mdb

On another forum, someone posted a suggestion to have this code in the
Front End

Dim strFile As String
Dim intFile As Integer

strFile = App.Path & "\DataPath.ini"
intFile = FreeFile()

Open strFile For Input As intFile
Line Input #intFile, gblDataPath
Close intFile

MsgBox gblDataPath

However, there is no mention as to where to put it or how to call
it.

Does anyone know how to get this to work so that when I launch the
front end, it looks as the ini file for the path to the backend and
refreshes the links to the tables in that backend?

 

[ruralguy via AccessMonster.com]

I find the best startup is to load an invisible form with the AutoExec macro
and use the OnLoad event of the invisible form to accomplish this task.

 

[ruralguy via AccessMonster.com]

You will probably want to incorporate code similar to this in your
application.
http://allenbrowne.com/ser-13.html

 

[Jeanette Cunningham]

In addition to ruralguy's excellent answer, my experience with this approach
recently is that changing the path and refreshing the links takes 5 minutes
when the backend is on the server at my clients office.
If I manually delete all the linked tables from the frontend, then do a
link/import manually, the process runs almost instantly.
Then I copy this frontend to all the other users' computers.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[ruralguy via AccessMonster.com]

Wow Jeanette, 5 minutes? I suspect something else is going on there.

In addition to ruralguy's excellent answer, my experience with this approach
recently is that changing the path and refreshing the links takes 5 minutes
when the backend is on the server at my clients office.
If I manually delete all the linked tables from the frontend, then do a
link/import manually, the process runs almost instantly.
Then I copy this frontend to all the other users' computers.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

You will probably want to incorporate code similar to this in your
application.

[quoted text clipped - 31 lines]

 

[Jeanette Cunningham]

Yes, it takes this long every time I try it this way.
Client needs to spend $$ to improve network setup, but is putting it off at
present.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Wow Jeanette, 5 minutes? I suspect something else is going on there.

In addition to ruralguy's excellent answer, my experience with this
approach
recently is that changing the path and refreshing the links takes 5
minutes
when the backend is on the server at my clients office.
If I manually delete all the linked tables from the frontend, then do a
link/import manually, the process runs almost instantly.
Then I copy this frontend to all the other users' computers.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

You will probably want to incorporate code similar to this in your
application.

[quoted text clipped - 31 lines]

front end, it looks as the ini file for the path to the backend and
refreshes the links to the tables in that backend?

 

[Nick Del Vecchio]

Thanks for the input, but none of these suggestions focus on the
solution that I requested in this thread.

My problem is I don't know how to get the front end to get the path
referenced in the .ini file and then refresh the links to the table to
the backend.

 

[Jeanette Cunningham]

See the code in this link
If your backend and frontend are in different folders, see Dev Ashish's
solution: Relink Access tables from code


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Here's the link
http://www.mvps.org/access/tables/tbl0009.htm


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Nick Del Vecchio]

I've used this link many times in the past and it works very well but
it is not what I am asking for in this thread.
Neither this link or Dev Ashish's solution show how to get the front
end to accept the path from an ini file.
Here is a link to a thread with Douglas Steele and a discussion as to
what I am trying to do.

http://www.utteraccess.com/forum/Unc-Path-Access-Un-t1938957.html

In this thread, Mr. Steele writes, "My preference would probably be
use an INI file, located in the same folder as the front-end. Then,
you can simply mail a new INI file to the users and have them replace
the one they have."

This is what I would like to do as well, but have not figured out how
to do it.

 

[Peter Hibbs]

Perhaps the Back-End Re-Linker code at this site will help :-

http://www.rogersaccesslibrary.com/...p?FID=21&SID=5debzz2c87c6957859f13b1d2zcccfb4

Peter Hibbs.

 

[Nick Del Vecchio]

WOW!!! Where have you been?
This is exactly what I have been looking for.
I've copied your two modules into my front end and it works just as
you described.
Actually it's better than I had imagined since I know the UNC location
of the files on my clients servers, I can just type them into the ini
file and distribute the new front end and ini file by email.
Never having to go over to their location to set them up.
This is great since they are not very computer knowledgeable and won't
ever have to deal with the link manager.

The only problem I had was once after the program created the initial
ini file, I moved the backend to see what would happen. The find file
dialog would not pop up.
But after deleting the ini and trying again, it worked fine. Not sure
why it didn't work before.

Thanks again!!!

 

[Peter Hibbs]

Nick,

Glad you found the code useful. If you are still developing the
database or giving users front-end updates perhaps the Back End
Updater and Front-End Updater utilities on the same site may be of
interest.

Anyway, good luck with your project.

Peter Hibbs.

 

[Nick Del Vecchio]

The code is great and works as described but is a little slow when I
put different locations in the ini file since the code loops around
the paths till it finds one that works. And it does this every time.
So to make it a little faster, I added a Function to your code that
was originally written by Allen Brown.
Basically it checks to see if the current links in the front end are
good, then it bypasses the checklinks code. Hope you don't mind...

It goes something like this:

' 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

Then I created a new startup form to launch with this in the open
event

If Not InitOk("tblxxx") Then

If Not CheckLinks("tblxxx") = True Then

' did not work, so continue opening this form
End If
Else
DoCmd.OpenForm "frmSwitchBoard"
' and cancel opening this form
DoCmd.Close acForm, "frmStart"
End If

I think it will speed up the opening a little by not having to go the
ini file every time.
If the startup form opens, there is a message that tells the users to
close and re-open the application since the ini file would have been
used to refresh the links.
Or to delete the ini file and launch again.

I haven't done a whole lot of testing on this concept yet but I plan
on trying it on Monday.
Please let me know if you think there is a downside to doing it this
way.

Thanks

 

[Peter Hibbs]

OK, sounds good. I will have a look at it when I get some time, if you
should find any problems then let us know via this forum.

Peter Hibbs.

 

[Nick Del Vecchio]

Well I found a little problem with the relinker if the location that
is in the ini file does not exist.
I get an Access error 52 Bad file name or number.
I wanted to put all the locations of the backend for all the different
users that use the database.
But the linker gives this error when it comes across a location that
does not exist.
So I guess instead of emailing just one ini file with all the user
backend locations, I'll have to personalize each user's ini file with
their particular backend location.
Not exactly what I had in mind, but at least I can still update their
front end from home while in my PJs
Please let me know if you find a way to bypass this error.

Thanks

Nick

 

[Peter Hibbs]

Nick,

I am not sure I understand the problem. Are you saying that you send
each user a copy of the .ini file whenever you update the front-end
file? Are you actually sending updates to the front-end file?

Peter Hibbs.

 

[Nick Del Vecchio]

Not exactly. Lets say I have 3 users that all have the backend in a
different location.
Initially, I wanted to send them all the same front end update and ini
file that contained all three different locations of their backends.So
the ini file would have 3 lines in it.
I was hoping that whenever I send them a new front end, the ini file
that they already have would cycle through the backend locations and
find the one that is valid for their particular location.
But what happens is that they get that error message as the program
cycles through the ini file and tries to connect to a location that
does not exist.
So since that doesn't work, I'll have to send each user their own ini
that contains their own particular backend location.
This is not a big game changer for me as I only have to do that
once.
Then every time I send them a new front end, their ini file will
locate their backend.

Nick

 

[Peter Hibbs]

Nick,

Yes, I agree. Each different user should really have a .ini file that
holds only their own back-end file location, having extra invalid
pathnames in the .ini file is asking for trouble and is not how the
system was designed to work.

I have an almost identical situation, four different users with the
same database and what I did originally was just emailed them the
front-end and back-end files and got them to save the files in
different folders (I prefer to have the back-end file in a different
folder to the front-end file because there is less chance that it will
get deleted by mistake when they are doing updates and back-ups, etc).
Then I got them to run the front end file and I provided detailed
instructions on how to use the file selector to locate the back-end
file. Personally I think that this method would be safer than sending
them a .ini file with the back-end location already stored in it
because you could easily make a very small mistake in the pathname
string and it would not work (but I guess you have already done this
so it should be no problem). Once they have done that it all works
with no further problems. Each time I send a front-end update they
just overwrite the existing front-end file with the new one and the
files are linked automatically.

Anyway, hope you get it sorted OK.

Peter Hibbs.