Force proper version of Access to open application

[Guest]

We have a few Access applications in different version 97, 2000, 2003 (Don't
want to even get into why this is LOL). One of the application I work on is
in 2003. I have had a few people open the application with Access 2000 and
has caused problems with the data (thank god I had back ups). Is there a way
for me to force my application to open only in the 2003 version?

Thanks in advance.

 

[Guest]

Hi Kenny,

I have had a few people open the application with Access 2000 and
has caused problems with the data

What kinds of problems are you witnessing? Do all users have the latest
service pack installed for the JET database engine (very important!). Each
user should also have the latest service packs installed for their version of
Office, and for the operating system as well. Use this document as a guide:

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528

Have you split the application into two .mdb files: a front-end (FE) and
back-end (BE)? From what you described, it does not sound like you have done
so. The entire application should never be shared. Each user should have
their own copy of the FE on their local hard drive. Only the BE database,
with tables for shared data, should be shared via linked FE databases.

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


If you really feel that it is necessary to limit users based on which
version of Access they are using, you could change the file format to the
newer Access 2002-2003 file format, which will prevent a user with Access
2000 from opening the file. They would get an "Unrecognized file format"
error upon attempting to open the database. It would probably be better to
simply run a function at startup that checks the user's version of Access. If
it is not the correct version, then throw up a message box telling the user,
followed immediately by a DoCmd.Quit. However, none of this should be
necessary with a properly split application, and with users who have fully
patched systems.

Your Access 97 app. is a different question altogether, since this would be
run using the JET 3.5X database engine. However, you can still link to JET
3.5 tables from JET 4.0 applications.

You might want to look into this product:

Total Access Startup
http://www.fmsinc.com/products/startup/index.asp


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom,
Thanks for your reply. You are correct in saying the database is not
split. I am considering, but right now want to deal with issue at hand. I
want to make sure that the application is only opened with Access 2003. The
issue I had seen, altered records somehow someway with only a select
statement happening. On a form I list records and have a combo box that let's
the user select a filter for which records to view that's it. Somehow the
filter does not work correctly and some records end up getting altered. I
want to make sure that the application only uses Access 2003 to open with.

Thanks
Kenny A.

 

[Guest]

Hi Kenny,

I strongly recommend splitting the application. Microsoft personnel from the
Access Development Team are on record as stating that sharing an entire
(unsplit) Access application is the number one cause of JET database
corruption. So, do so at your own risk.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom,

I will look into splitting the database, but until then is there a way
to force the application to open in Access 2003? Even if I split the
database, and a user opens the application with Access 2000, wouldn't I get
the same results as if it was not split?
If I do split the database, what happens as updates need to be made to
the application? I update just the FE, but do I have to re-establish the
links to the BE if I replace a form with an updated form, or change a query,
macro, etc...?
If my users are still coming into application via FE and it is linked to
BE what benefits does this provide? The application sits on a server with a
short-cut to it from users pc, so changes only have to happen in one place,
not on each person's FE copy on their pc.

 

[Guest]

Hi Kenny,

I will look into splitting the database, but until then is there a way
to force the application to open in Access 2003?

Yes, there is. But it really shouldn't be necessary as long as the BE is in
the Access 2000 file format, and your users have Access 2000 or later
installed. So, I hesitate to lead you down that road.

Even if I split the database, and a user opens the application with
Access 2000, wouldn't I get the same results as if it was not split?

No. I suspect what is happening when the entire application is shared is
that the first user in determines the version of the required library
"Microsoft Access X.X Object Library" that is set in the VBA Project. For
Access 2003, the version is 11.0, for Access 2002 it is 10.0, and for Access
2000 it is 9.0. There can only be one version active at any given time.
However, this does not apply to linked tables. Thus, no version conflicts for
mismatched Microsoft Access Object Library.

If I do split the database, what happens as updates need to be made to
the application?

You need to distribute an updated copy to each user. This is standard
practice. You can either do this manually, by walking around to each user's
PC and copying an updated FE, or you can automate the process. A very good
tool for doing this is free and is provided by Access MVP Tony Toews. It is
known as the Auto FE Updater:

http://www.granite.ab.ca/access/autofe.htm

...but do I have to re-establish the links to the BE if I replace a form with an
updated form, or change a query, macro, etc...?

As long as you do not make any design changes to the BE database, you should
not need to refresh any linked tables. On the other hand, if you make any
design changes to the BE database (ie. add or remove a field, index, etc.),
then you should delete the linked table from the FE application, compact the
database, and re-establish the link from scratch. You do all of this to the
FE before distributing it. It is best to use UNC path, instead of trying to
rely on a mapped drive letter, when specifying the path to the BE database.

If my users are still coming into application via FE and it is linked to
BE what benefits does this provide?

Massive benefits. The first and foremost is that you have just eliminated
the number one cause of JET database corruption, as identified by Microsoft
personnel! You can make changes to your FE application without having to
take your users off-line. You are not clogging a skinny network wire with
transferring all the bytes of information to define each object that if found
in the FE application (queries, forms, reports, modules). You avoid conflicts
as discussed here:

http://www.access.qbuilt.com/html/gem_tips1.html#SplitDB

Use the information in this paper as a guide to implementing a successful
split application:

http://www.access.qbuilt.com/html/multiuser_applications.html



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Tom,
Isn't it better to just have one copy of the FE that everyone has a link
to so you only have to make one change in one place with no distrubution?
Also, wouldn't that reduce network traffic if the application is on server
that they just have a link to? Wouldn't all processing happen on server
itself with no back and forth traffic between FE and BE?
Back to original question, about forcing application to open only with
Access 2003, the entire application is 2003, so your earlier statement saying
"shouldn't be necessary as long as the BE is in the Access 2000 file format,
and your users have Access 2000 or later installed." would not work in this
situation. Since entire app is in 2003, how could I make sure that it only
opens with Access 2003 for those that have multiple Access versions on their
pc's?

Thanks
Kenny

 

[Guest]

Kenny,

Isn't it better to just have one copy of the FE that everyone has a link
to so you only have to make one change in one place with no distrubution?

No, nada, nein! No way Jose.
It may be easier from a developer's perspective, but did you read the tips I
provided earlier, here?
http://www.access.qbuilt.com/html/gem_tips1.html#SplitDB

Like I have stated earlier, do so at your own risk, which includes increased
chances of database corruption. But *DON'T* then later talk negatively about
Access if your application becomes corrupted, because you choose to play
Russian Roulette with your database. Read this paper by Access MVP Albert
Kallal, and then come back to me and answer your own question:

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

Also, wouldn't that reduce network traffic if the application is on server
that they just have a link to? Wouldn't all processing happen on server
itself with no back and forth traffic between FE and BE?

Nope. None of the processing occurs on the server, even in a properly split
application. Access is a file server database. It simply treats whatever file
is on the file server as another hard drive. In order to get processing to
occur on the server, you need to use a product such as SQL Server or Oracle.

...and your users have Access 2000 or later installed." would not work in this
situation.

Why would it not work "in this situation"?
The only difficulty that you should have is someone attempting to use Access
97 to open a JET 4 application. The code that I might suggest would not
overcome this problem, because it would never run. The user would be
presented with an "Unrecognized Database Format" error instead.

I think at this point, if you are not willing to split the application, and
distribute a separate copy of the FE to each user to install on their local
hard drive, then you need to look into the software I recommended in my first
reply:

Total Access Startup
http://www.fmsinc.com/products/startup/index.asp


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[strive4peace]

Hi Kenny,

I have tagged on to one of Tom's comments .. posted in-line...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Kenny,


No, nada, nein! No way Jose.
It may be easier from a developer's perspective, but did you read the tips I
provided earlier, here?
http://www.access.qbuilt.com/html/gem_tips1.html#SplitDB

Access 97 was very stable and multiple users in the FE seemed to be
okay. With later releases of Access, however, it has become much better
to give each user their own FE.

This also has advantages... for instance, you can have tables resident
in the FE with user preferances -- such as last used criteria on a
report menu, path to import/export files, default form, ...

to manually split a database into front-end (FE) and back-end (BE):

1. Make a blank database -- this will be your Back-End, BE

Name it "YourDatabaseName_BE.mdb"

2. import all the tables -- this will bring in the relationships too
File, Get External Data, Import...

This will be the common Back-end (BE) that everybody shares if you have
multiple users.

3. Then, open your working database... which is now your Front-End, FE

4. DELETE all the tables

5. Compact/Repair
Tools, Database utilities, Compact/Repair

6. link to the user tables in the BE
File, Get External Data, Link Tables...

7. this will be your Front-end (FE)

8. Close the FE database

9. make a copy of the FE for each of your users.
(if there are multiple users) and make a backup

I like to name database files like this:
databasename_yymmdd.mdb

ie: AddressBook_061027.mdb

maybe even add a time indicator onto the end...

ie: AddressBook_061027_5p.mdb

that way, you can ctrl-drag the database to another directory, then
rename it, then continue development -- and the backups will be in
chronological order when sorted.
Unlike the other Microsoft applications, where you specifically save,
when you open a database, Access changes the time/date stamp, so the
file Date Modified is not a good indicator of the latest version of a
database.

so now, you have a FE for each of your users -- but everybody links to
the same BE

once you get used to working this way, you will appreciate the
convenience of having shared data and forms/reports/programs seperated.

 

[strive4peace]

Hi Kenny,

another thing you can do for the users that you wish to be using a
particular version Access when they use your program is this:

create a shortcut on each desktop to open the database using the version
of Access you desire

1. open Windows Explorer
right-click on Start button
choose Explore from the shortcut menu

2. navigate to where the Access program is stored
ie: C:\Program Files\Microsoft Office\OFFICE11

3. right-click on the program file
ie: MSACCESS.EXE

4. choose
Send To, Desktop (create shortcut)

5. minimize Explorer and other open applications
right-click on unused gray part of taskbar
choose Show the Desktop from the shortcut menu

6. right-click on the icon just made and choose Rename
change name from
Shortcut to MSACCESS.EXE
to something specific like
Address Book
and press ENTER

7. right-click on the icon and choose
Properties

8. modify the Target to include the path and name of the database to open
ie:
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
"c:\data\mydatabase.mdb"

(there is a space between the program path and the database name/path)

9. if you want to change the icon, choose
Change Icon...
from the properties window

10. click OK
to Apply changes and dismiss the property dialog box


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Tom and Crystal,

I want to thank you both for your great insight. I will be looking into
splitting the database and then have to talk the managers involved into this
idea. They are not really Access savvy, so I will use your comments and the
artilces to help explain to them this different approach to database
development and its benefits. Thank you once again for your help and wisdom.

Kenny A.

 

[strive4peace]

you're welcome, Kenny good luck with your proposal!

more fuel for splitting...and a caution if you need to secure your data...

I find it best not to use Access security ... Instead use the network
security that you have set up.

Create a front-end and a back-end for your database

The front-end has all the queries, forms, reports, macros, modules, and
links to all the tables. Each person who has access to the database
will have a copy of the front-end

you may have more than one back-end

For instance,

there may be tables that everybody can read and write to. The database
with these tables would go on an area of your server with public
read/write privileges

You may have financial data that only the accounting group is allowed to
read and write. The database with these tables would reside in the
accounting groups shared area.

There may be tables, such as employees, that only the personnel
department is allowed to change, but everybody can view them. Put that
database in a directory where the personnel group has write priviledges
and the public has read access.

I have had trouble with Access security ... if you can use it, network
security works great!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*