Front-end / Back-end - Vista

[Rick Allison]

Vista...
If I install the back-end in %USERPROFILE%\Documents which will be unique to
each individual, how do I connect to the location of the back-end upon first
time load of the front-end?

Background...
My application is a MS Access VBA .mde front-end and a MS Access .mdb
back-end. Access 2000, 2002, 2003, and 2007

I sell this application to people all over the US and Canada.

When I deliver the application the front-end is a link to the back-end built
in, so to speak.

Before Vista I put everything into c:\program files\my application and all
was good.

I now know that Vista does not grant write privileges to c:\program files\my
application so the Back-end database and writing to it is a huge problem.

If I install the back-end in %USERPROFILE%\Documents this will be unique to
each individual.
How do I connect to the back-end upon first time load of the front-end given
this unique situation for each user? I cannot do this myself nor do I want
to ask the end user to do this. First time load should work without
problems.

Sorry it took so long to get to my question.

Rick

 

[Jack Leach]

I would make a global property to hold the path, storing the value in a
auxilary table which is set on your autoexec:


Public Function AutoExec()
Let gROOTPATH = ELookup("fldValue", "tblSys", "fldVariable = """ & Root &
"""")
End Function




In the module you want the property to reside in:



Option Compare Database
Option Exlicit

Private m_strgROOTPATH As String


Public Property Get gROOTPATH() As String
gROOTPATH = m_strgROOTPATH
End Property

Public Property Let gROOTPATH(ByVal strgROOTPATH As String)
m_strgROOTPATH = strgROOTPATH
End Property




And then to use it in code:

Private Sub psLogSomething()
Dim sPath As String
sPath = gROOTPATH & "useractivity.log"
...
End Sub



Creating flag file might work for a first time run... install the package
with something like firstrun.inst in the root directory, and you can check
for it's existence in the autoexec and play with whatever first time settings
you need, and delete the file at the end of the procedures. It will check
for it each time the app runs but only run the applicable code if the file is
there.

Take a look at this code from Dev Ashish... a few minor mods to it and it
will work completely unseen by the user, and would be a nice addition to your
install code:

http://www.mvps.org/access/tables/tbl0009.htm


I've never had to do this, but this would be my first approach to the problem.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Tony Toews [MVP]]

Before Vista I put everything into c:\program files\my application and all
was good.

Actually Microsoft's recommendation even before WIndows 2000 was to
put updatable files in the Users Documents and Settings folder.
Vista is simply tightening up the rules which is a good thing in my
opinion.

If I install the back-end in %USERPROFILE%\Documents this will be unique to
each individual.
How do I connect to the back-end upon first time load of the front-end given
this unique situation for each user? I cannot do this myself nor do I want
to ask the end user to do this. First time load should work without
problems.

You want to use the following API calls to get the name of the users
folder.

SHGetFolderPath: Retrieve Windows Shell Folders (Best Practice
http://vbnet.mvps.org/code/browse/csidl.htm

Then you can use the VBA code that Jack mentioned to relink the
tables.

Sorry it took so long to get to my question.

Lengthy postings are just fine.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I would make a global property to hold the path, storing the value in a
auxilary table which is set on your autoexec:


Public Function AutoExec()
Let gROOTPATH = ELookup("fldValue", "tblSys", "fldVariable = """ & Root &
"""")
End Function

I would never keep the location of the backend file in the FE. The
problem is that if the FE ever gets updated then you lose the path and
file name of the BE.

Instead I use an INI file although you could also use the registry.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jack Leach]

Sorry, that was a bad post where I was thinking while I was writing instead
of before I wrote.

I had meant to pull the path from a linked table in the BE on normal
startup, or pull it out of an install file if the file exists for the
installation, and relink the tables using it.

Anyway, an INI file makes much more sense... store that or any other hard
variables in there are read them through vba's file IO at startup.

If you don't mind Tony (or anyone else who knows), would you recommend
storing this in a property as I mentioned? I only starting using properties
like this a few months ago, and while I haven't come across any problems yet,
I haven't used it long enough to be fully confident in them and have had no
outside opinion on them yet.

Thanks,
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Tony Toews [MVP]]

I had meant to pull the path from a linked table in the BE on normal
startup,

Trouble is if the user has a new FE which is linked to tables on the
developers system then how can get that path from a linked table?

or pull it out of an install file if the file exists for the
installation, and relink the tables using it.

How do we know where the user are putting the files? In my opinion
the FE and related files should be inside the users APPData folder
while the data MDB should reside in the users My Documents folder.
And the user can override those if the installer lets them. And those
folders, of course are different for each user as the account name is
part of the path.

Anyway, an INI file makes much more sense... store that or any other hard
variables in there are read them through vba's file IO at startup.

Anywhere except in the FE or the BE.

If you don't mind Tony (or anyone else who knows), would you recommend
storing this in a property as I mentioned? I only starting using properties
like this a few months ago, and while I haven't come across any problems yet,
I haven't used it long enough to be fully confident in them and have had no
outside opinion on them yet.

You mean a database property? No I wouldn't again because if they
download a new FE then they lose the old FE's database property.

I don't use database properties anyhow as I prefer to store variables
in a Global Options table in the BE or user specific variables in a
System Users table in the BE.

With the obvious exception of the path and file name of the BE which
has to be stored somewhere else on the users system.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[rick allison]

Tony,

How would an INI file work in Vista?

For example: I create the FE and BE on my computer. I have an INI file that
says the BE is located in "my computer/my application". When the file is
installed on the customer's computer the INI file will contain "my
computer/my application" where in fact the BE will be installed in "their
computer/my application".

I'm not sure if this is an install issue or a VBA issue.

Anyway, I cannot go to each computer and make the change manually.

It was easier before when I put everything, FE and BE in c:\program files\my
application and the "hard link" to the BE that was in the FE just worked. I
have VBA code that allows the BE to be anywhere on the computer after the
customer initially opens the program. The issue is how to handle FE to BE
connection in Vista the first time the program is opened when I really don't
know where the BE has been installed.

Thanks for any tips you can provide.

Rick

 

[Tony Toews [MVP]]

For example: I create the FE and BE on my computer. I have an INI file that
says the BE is located in "my computer/my application". When the file is
installed on the customer's computer the INI file will contain "my
computer/my application" where in fact the BE will be installed in "their
computer/my application".

I don't distribute an INI file. Well I do but it has no data in it.
The first time the user runs my app and there is no INI file I look
for either of the following situations
- a data MDB in the users My documents\Granite Mgr Folder along with
a license key file.
- a demo MDB in %userapps%Granite Fleet Mgr folder
I then automatically link to the appropriate MDB

If neither of the above worked, and there was no INI file then I
prompted the user. But this should never happen

It was easier before when I put everything, FE and BE in c:\program files\my
application and the "hard link" to the BE that was in the FE just worked. I
have VBA code that allows the BE to be anywhere on the computer after the
customer initially opens the program. The issue is how to handle FE to BE
connection in Vista the first time the program is opened when I really don't
know where the BE has been installed.

Yes, it was easier doing this. But for security reasons this
shouldn't have been happening since at least Windows 2000.
Alternatively you could just create a folder on the root of C.
However I disagree with this as some IT departments won't let this
happen. And I don't like cluttering up the root of users hard drives
when %appdate% and My Documents are available.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jack Leach]

Trouble is if the user has a new FE which is linked to tables on the

developers system then how can get that path from a linked table?

Only use the FE after the install/firstrun flag file has been removed. If
it's a fresh install or new version there will be a flag file located in the
..mdb directory. Startup would look for this file, and perform all the
essentials (relinking to the BE via the path left behind by the install,
setting all the system variables in the BE after it's linked), and then
remove the flag file.

How do we know where the user are putting the files?

The data paths and any other variables would be written to file in the
install procedure, and distributed with the FE. On the FE's first run the
app relinks and sets variables in the backend.

I don't use database properties anyhow as I prefer to store variables
in a Global Options table in the BE or user specific variables in a
System Users table in the BE.

With the obvious exception of the path and file name of the BE which
has to be stored somewhere else on the users system.

Two particular peices of data I store are used more than anything else: the
BE root folder and the FE root folder. Just about every path used in the
project refer back to these two. I used to store them in a table, but wasn't
happy about having to read it from a table over the network every time I
needed to build a path. I had switched to storing these two in a constant
instead (Every computer I distribute to is easily within physical reach on a
normal work day), which freed up a lot of performance. I've recently
switched to properties and include the property settings as part of the
startup code.

So the system/user variables are stored in the back end, but on startup the
two root locations are set as a global property for efficient usage.



This is very much one of those things where there's 10k+ ways to do it
depending on personal preference and circumstance (isn't there always).
Thanks for the feedback on your methods. I'm currently in process of
revamping my core application coding, so I'll take any ideas I can get!

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Tony Toews [MVP]]

Only use the FE after the install/firstrun flag file has been removed. If
it's a fresh install or new version there will be a flag file located in the
.mdb directory. Startup would look for this file, and perform all the
essentials (relinking to the BE via the path left behind by the install,
setting all the system variables in the BE after it's linked), and then
remove the flag file.

So the install procedure then creates this flag file then contains the
path and file name of the BE MDB? If you have an install procedure
that can figure all this out then great.

The data paths and any other variables would be written to file in the
install procedure, and distributed with the FE. On the FE's first run the
app relinks and sets variables in the backend.

See above

Two particular peices of data I store are used more than anything else: the
BE root folder and the FE root folder. Just about every path used in the
project refer back to these two. I used to store them in a table, but wasn't
happy about having to read it from a table over the network every time I
needed to build a path. I had switched to storing these two in a constant
instead (Every computer I distribute to is easily within physical reach on a
normal work day), which freed up a lot of performance. I've recently
switched to properties and include the property settings as part of the
startup code.

Ah, there's the difference. You can easily to get each computer in
physical reach. And the BE server name and folder name are a known
preset value.

I can't depend on that so I've come up with other solutions.

This is very much one of those things where there's 10k+ ways to do it
depending on personal preference and circumstance (isn't there always).
Thanks for the feedback on your methods. I'm currently in process of
revamping my core application coding, so I'll take any ideas I can get!

Yup, so long as it works for you.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/