Prevent conversion of an Access 97 DB to 2K, XP, ect. 2

[ut1205]

I have a Access 97 DB running on a server at work. I have not made it "read only" because I don't like the window that pops up telling the user that the file is "Read Only". Some users have Office 2k or XP. Is their anyway for me to keep these users from converting my DB to the newer format other than making it read only?

 

[LarryDeLaruelle]

Is the db split front/back ends?

If so, you can convert the front end to 2K/Later but still use the back end tables in 97 format.

I have that situation with a few databases where some users are on 2K and others still on 97.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[ut1205]

Thanks for the replies. Please explain front/back ends. I've never done that before.

Thanks
Jim

 

[lewie]

there is a microsoft article on this Q275164

 

[LarryDeLaruelle]

Jim:

Simply put, a split database is what Scot describes. All Forms, Queries, Reports, Modules, etc reside in the Front End and all Tables reside in the Back End. The Front End is the User Interface and the Back End is data storage.

If you want to use the split database technique you can use the Database Splitter feature available in Access. It is under Tools/Add-ins. What the splitter does is to 'split' the tables from the balance of the database and put them into a separate database. The tables are then linked back to the Front End so that they can be accessed, written to, etc.

The use of split database has many advantages: modifications to the user interface (Front end) can be distributed to user without fear of overwriting existing data; in a multi-user environment, the data tables (back end) can reside on a server and the user interface can be on the individual user systems; in a mixed application environment (some using '97, some using 2K) the front end can be created in both Access 97 and Access 2K and both can access the same '97 format data tables.

Even if I am designing a database for a single person to use on a single machine, I will split the database because it allows for much easier updates.

In your case, if you wish to pursue this option, I would suggest these steps:

1. Make a back up copy of the entire database as it now exists.

2. Run the Splitter Add-in to split the database.

3. Convert the '97 Front End to Access 2K.

4. Test extensiveley in both flavors to make sure that all forms, reports, queries function as they should.

As I said in my earlier post, I have encountered the problem of having to support users in both '97 and 2K. Fortunately, we have now upgraded most users to Office 2K; supporting two flavors of Access can be quite a pain in the posterior.

If you have any additional questions or concerns, feel free to post or e-mail me directly.

Good luck and Happy New Year.


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[ut1205]

Thanks for all the responses. I give it a try.

Jim

 

[BSman]

Here's a simple way to split a database (use a copy, obviously):

First, I set up two subdirectories within the directory containing the database on the server, calling one "Data" and the other "FrontEnd".

Copy the database into both subdirectories.

In "Data" delete everything from the database except the tables (see note below), eliminate the startup form that automatically starts, change the displayed name so you know it's the data database, not the application database), and compact the database.

In "FrontEnd" delete all of the tables (see note below). Go to File, Get External Data, Link Tables, and link to all of the tables in the database in the Data subdirectory.

Now everything should work like it did with two major advantage: You can continue developing and improving the application (using anther copy of the database) and, when you've got a wonderful new version, just copy it over the one on the server (when everyone is out of the application, of course). No worries about the data because it's in the back end database in the "Data" subdirectory.

Another advantage is that if your data gets large enough to merit moving it to SQL Server (or Oracle), you can do that easily and just link to the SQL Server database instead of the "Data" Access back end database.

Note about tables: If you have any tables which are used for application control (examples: lists of reports for a report menu form), leave them in the front end so you can continue development and make additions and changes to the data in those tables. Otherwise you will be changing application control data in the working system that the currently used application front end database won't have any idea how to use.

Regarding 97 and 2K, I am supporting users with both versions of Access. So I do the following:

1. The back end ("Data&quot is always in Access 97 so it can be read (through the link) by both versions.

2. When a new version of the application ("FrontEnd&quot is ready, I create an MDE version (which strips out the VBA source code and restricts users from making changes) and distribute that for the Access 97 users.

3. I then open the MDB file with Access 2K and have it convert the application database. I use the same name with "2K" added to the name (ex: MyApp.mdb for 97, MyApp2K.mdb for the 2K version).

4. Then create the MDE version of the 2K application database and put it on the server.

5. Your 2K users should use the 2K version, thus avoiding any of the questions about converting, etc. Also seems to run better than when you have one version that's used by both 97 and 2K.

I've got a lot of apps in this situation, including some that use a SQL Server (7) back end database, and the process has worked well for many years.

 

[lewie]

here is the microsoft article.


If you do not want to see the Convert/Open Database dialog box when you open an Access 97 database in Access 2000, you can suppress the dialog box by adding a registry entry called NoConvertDialog to the Access Settings key.

This setting does not prevent conversion; it only prevents the initial Convert/Open Database dialog box from being displayed when the database is opened. You can still convert the database by any other available means.
MORE INFORMATION
The following steps describe how to add the NoConvertDialog subkey, which controls whether the Convert/Open Database dialog box is displayed.

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

Click Start, and then click Run.
In the Open box, type regedit, and then click OK. Registry Editor opens.
Locate the following subkey, and select the Settings subkey:
HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Access\Settings\

On the Edit menu, point to New, and then click DWORD Value.
Name the new DWORD NoConvertDialog.
Double-click the new NoConvertDialog DWORD.
Set Value Data to either 0 or 1, and the Base to Decimal. There are two possible settings for Value Data:
1 - Opens the database without prompting you and leaves the database in the original format.
0 - Displays the Convert/Open Database dialog box.
Set the value to 1 so that the dialog box does not appear. Click OK, and then close Registry Editor.
Open Access 2000, and click Open on the File menu.
Browse to a copy of an Access 97 database and open it. Note that the database opens without prompting you to open it or convert it. The database remains in Access 97 format.
NOTE: If you later want to convert the database, point to Database Utilities on the Tools menu, point to Convert Database, and then click To Current Access Database Version
Lewie

 

[ut1205]

I did the front end / back end with two separate links and it works great. I install a custom icon on the users desktop so I just wrote two different batch files to do this. One installs the 97 link and the other the 2k link. Thanks for all the responses.

Jim

 

[evalesthy]

ut1205

If you don't mind my asking what do your batch files look like?

Thanks.

 

[ut1205]

It is a simple DOS file. I'm on my way out of town right now but if you'll send me an E-Mail at ut1205@yahoo.com I'll give you the details when I return.

Jim

 

[BSman]

Actually, you don't even need a batch file. Just create a shortcut that calls the proper version of Access and includes the Access database you want it to open.

The easiest way to get the shortcut to first call the right version of Access (assuming you have two versions of Access on the workstation, since if you have only one you just put the shortcut on that workstation that starts up the 97 or the 2K version of the database as appropriate for that workstation). Send a shortcut to the desktop (right click Access in the programs list) for the version of Access you want. Then modify that to include the name of the database (with the path) after the startup text for the version of Access you need to use. (Put the path and name outside of the quotes that enclose the startup string for Access.)

 

[ut1205]

Thanks for the response BSman and you are right. The reason I use batch files is to save time.
Where I work we have 300+ work stations and even if one-tenth of them use my programs it makes sense to carry it on a floppy or a cd.
I do somewhat more than a simple generic access Icon on their desktop. I try to:
1. Create a custom icon that makes the program easy to find.
(Paint and Clipart)
2. Create a file in their "Program File" folder that contains the custom icon and the link to the mde on the server, and batch files.
3. Puts the custom icon in one or more of the following places:
A: Desktop
B: Start Menu
C: Microsoft Office Tool Bar
D: Quick Launch Tool Bar.
Since we are running a combination of Win 98, Win2k, and Win xp it takes a different line of DOS to cover all the options. (2k and xp go thru Documents and Settings, 98 does not.
4. Since all the "install data" is in the folder I created I also create a second batch file in the folder called "reinstall". If they happen to delete the icon off their desktop a simple click on "reinstall" will get it back. Or, if I go back to reinstall it I don't have to carry the disk around.
5. In some (not many) cases I create a third batch file which uninstalls all icons and deletes the folder that I created in "My Documents".

This may be overkill but I don't work with a lot of computer savy people. I tried one time to put the install program on the server and that was a disaster.
Lastly, I think this makes my applications look a little more professional.

Thanks for the reply and the "front end / back end" you suggested is working GREAT!

Jim

 

[CWestII]

I had that situation and I used the separate frontends (97 and XP). I named one OriginalFileName97 and the other OriginalFileNameXP. I, then, created a router database in Access97 named OriginalFileName (this way the users original shortcuts continue to work with no other intervention). The router database reads the users version of Access and then opens the proper frontend based on this.

If you are interested in the router code let me know.

 

[ut1205]

Thanks for the response and yes I am very interested in the code. You can reach me at ut1205@yahoo.com

Thanks
Jim

 

[BSman]

Actually, the startup location for a specific version of Access is usually at the same location on all machines (excluding machines that have more than one Access version installed...like a developer's machine would). So if you create a shortcut (including the icon you want from the server) for the Access 97 version, another for the Access 2000 version (or XP version) on "standard" machines for each version of Access, all you have to do is drag each icon to the server where the front ends are located so there is a copy on the server of each icon. Then, when you want each user to have a proper shortcut, just copy the appropriate icon from the server to the person's desktop. (You could even accomplish that over the phone with a semi-intelligent user.)

 

[sname]

Hi. Just read this topic.
I have same task: let users read/write data in a 97 mdb, but don't allow them convert the database or make modifications in any MS ACCESS. They can reach the database through applications.
I simply use a password to protect the database. When the application opens the database, use shared mode, not read only, and with a password. Except for myself, no one can open the database in any ACCESS version. This simple way works fine for long time so far.

 

[CWestII]

I think this will be enough to get you there. If you need any addtional help, let me know.

To create a router database that can be used by 97 users and above:
1. Create a new 97 access database.
2. You should name this original file name and keep it in the original location. (See below about where to but your frontends.)
3. Create a new form (make this form your startup form)
4. Add a button to the form (cmdOpen).
5. Add the code below to the click event of the button and the open event of the form respectively.
6. In the code, “FilePath” refers to the location of your file. Replace “FilePath with that location (i.e. C:\MyDb\). I generally place mine in a folder called FrontEnd within the upper level folder.
7. Also, “FileName” refers to the original name of the database with 97, XP or 00 placed on the end depending on the version of that frontend.
8. Also, “YourNetworkUserID” refers to your user id. This allows you to edit the router in case you change file locations or some other feature.
9. SysCmd(acSysCmdAccessVer) = 10 for XP I have forgotten what it is exactly for 97 and 2000. All you need to do is create s simple button that displays a message box. Have the message box display this piece of information and run it in both platforms (97 and 2000). (For example Msgbox “Version No. = “ & SysCmd(acSysCmdAccessVer)) Once you have the two numbers, put them in the code below in the proper order.
10. Once you have finished compile and save all modules.
11. Compact and repair the database
12. You should then open it with a 2000 or XP machine to ENABLE it. (Do not perform a conversion!!!)
13. When you enter the database it should display the form. When you are ready to enter the database just hit the open button.
14. When a user enters the database it should read their version number and route them immediately.


************************************************************************
Private Sub cmdOpen_Click()
Dim strVerNo As Double
Dim stAppName As String

strVerNo = SysCmd(acSysCmdAccessVer)

If strVerNo < 9 Then
stAppName = &quot;MSAccess.Exe FilePath\FileName97.mdb&quot;
ElseIf strVerNo = 10 Then
stAppName = &quot;MSAccess.Exe FilePath\FileNameXP.mdb&quot;
End If

Call Shell(stAppName, 1)
DoCmd.Quit
End Sub

***********************************************************************

Private Sub Form_Open(Cancel As Integer)
Dim strVerNo As Double
Dim stAppName As String

strVerNo = SysCmd(acSysCmdAccessVer)

If Environ$(&quot;UserName&quot = &quot;YourNetworkUserID&quot; Then
If strVerNo < 9 Then
stAppName = &quot;MSAccess.Exe FilePath\FileName97.mdb&quot;
ElseIf strVerNo = 10 Then
stAppName = &quot;MSAccess.Exe FilePath\FileNameXP.mdb&quot;
End If

Call Shell(stAppName, 1)
DoCmd.Quit
End If
End Sub

 

[ut1205]

Thanks! I give it a try.

Jim

 

[BSman]

One other comment about splitting the database. You can link to the back end database two ways. One is to use UNC (universal naming convention), where you put the entire path starting with the server name. This method should work for all users (as long as they have rights to see the back end database folder). But it tends to run slower than the second menthod of using a drive letter.

While using a drive letter is preferable in terms of speed of operation, you must be sure that all users will have the same drive letter mapped to the exact same server and folder on that server.

Which method you use will depend primarily upon the amount of standardization you can enforce among your users for drive letters.