Access 95 to XP Access and Access 95/WinXP problems? (1 Viewer)

I have a data base that has been developed over the years on Access 95. It has about 2000 queries and 3000 macros. The only code it has is on the OnClick on labels and the code in each case opens Word docs that have queries linked to the docs and it inserts Access field data into Bookmarks, prints the Word docs, copies the doc and pastes it back to Access memo field and closes Word docs without saving. The code also runs several macros.

There are several of us using this data base on different computers and we recently updated the computers and they have XP Home.

The problem with the Access 95 on Win XP is that many things, even including openin the data base window, takes forever the first time it is done UNLESS Ctrl Alt Del is used to bring up the Task Manager. When that is dome then a form might open in about 10 seconds as opposed to what seems forever. However, once a form or other task has been done once then it peforms at normal speed fpor any subsequent use. Even clicking on the Tables or Forms tab when the data base is first opened takes forever unless the Task Manager is brought up.

I have tried using the compatability function on Win XP and have selected both Win 95 and Win 98. This solves the speed problem but many of the macro actions etc do not run.

Sooooo, if I can't fix the Access/Win XP problem then I may have to get Access XP. I am worried about conversion problems and also any loss of functions. For example when Access 97 came out there were conversion problems for converting Access 97 and we also noticed that Access 97 was lacking some macro functions that were in Access 95. We were getting messages coming up that Access 97 did not have this fucntion, so we bailed out

Any help, ideas (both good and bad ) would be enormously appreciated.

Mike

Mike375 said:

I have a data base that has been developed over the years on Access 95. It has about 2000 queries and 3000 macros.

Click to expand...

Your kidding right?

I suggest that you bite the bullet and convert your macros to VBA. Then convert the db to your latest version of Access. You will have conversion errors and you will have to take them one at a time and fix the VBA so that you can convert the db to your newest version of Access.

Ensure that you make backup copies of your db's during this process.

Good luck!

I want to avoid VBA like the plague. Now that desire might be due to lack of knowledge but at this stage I can't see why one would use VBA if the same objective can be achieved with a macro. After looking at a few of these Access forums and seeing what people are doing with code it just seems such a long way round to get from A to B and doubly so with coding errors.

Mike

Experienced people use VBA rather than macros because macros do not provide any error handling capabilities and professionals cannot deliver a database that may at some point drop into code due to an unhandled error. To do the occasional simple thing, you can get by with macros but anything complex will actually be easier with code. In fact you probably have 3000 macros because you cannot use parameters in a macro. Ditto for the 2000 queries. You could probably replace all those macros with a few dozen procedures and substantially reduce your query count as well if you learned how to use parameters and that's not even coding.

There are NO functions that A95 has that newer versions do not support. The conversion errors were no doubt the result of missing reference libraries. A95 is the worst Access version ever. I can't believe you stuck with it so long.

I can't believe you stuck with it so long.

Because it all works. I am in the insurance business and the data base is confined to use in my own business and a couple of friends have it as well.

There are various "professional packages" available but they don't do what this data base does. There are many people in my business who see it working and wish to buy it but I am not in the selling software business and the necessary backup.

If there was something which I could not achieve with the combination of macros and queries, then I would increase my limited knowledge on code. But if I have a choice between spending time on learning extra coding for something I can do with macros and queries or spending the time either selling insurance or training a new recruit, then I do the latter.

By the way, the data base also has 728 forms and I am not sure you would get away with just a few dozen procedures.

Mike

The number of forms is also excessive. Again probably due to poor table design and lack of understanding how parameters can be used.

I have created hundreds of applications including multi-million dollar mainframe efforts requiring dozens of people and none has ever required 728 forms or 2000 queries. I can't believe that you haven't yet run into some Access limit on the number of objects that it will support.

If as you say this application is critical to your business, I would suggest finding a professional developer in your area to help you rebuild the app from the ground up. Once the application is streamlined, the amount of maintenance you need to do should be dramatically reduced.

You might find someone willing to work for a reduced fee if you give up ownership of the db so that they can market it.

The reason the data base has so many forms, tables, macros and queries is that it is used as a complete work station. Actually many of them, perhaps a third or more are different versions of the same thing. The data base has a very sophisticated telemarketing setup (which I hire out) and it caters for many different categories of prospects and the associated statistics and goals settings and so there isa complete set of forms etc for each category. One of the advantages of so much being done with macros and queries (at least for myself and some of the users) is that we can easily make "on the run" changes by simply bring the data base window into veiw and then open the macro or query in question. It could be as simple as someone wanting a background to turn "green" isntead of "blue"

The only problem I have ever encounted due to its size is that list of fucntion attached to the expression builder can't be brought up. So sometimes in the past when I could not remember one of find where I had already made one I would make a new .mdb file and then pull up the function.

The difficulty with a "professional" manager is he will lack the knowledge associated with insurance contracts and associated documentation. This is one of the problems experience by other DBs in this area. When we have changes in insurance policies (This DB runs comparisons on different products) then I can do it all on the spot myself.

The number of forms is also excessive. Again probably due to poor table design and lack of understanding how parameters can be used.

I have sold "parts" of the data base on a few occassions and a "professional" working for another group has made changes to the structure of the DB they have bought to suit them but at the same time have utilised the design and the "what and how" the DB achieves.

However, yesterday I have solved most of the problems I spoke of in my posting. I have a start up a macro which opens about 12 forms and sets call back dates etc. To this macro I have a lot of "runmacro" action to run a lot of the macros attached to labels on the various forms. So we hit the start up macro and Ctrl Alt Del and wait up a couple of minutes until it does its thing. Some of the code on labels that open word and also some forms I added a macro to the start up macro to simply open and close those forms. Where there is a will there is a way. As I metnioned in my starting post once various forms have been opened once then speed is normal next time around. For reasons which I don't understand the delay only occurs with some of the forms on first opening.

As a by the way, sometime ago a few of the large macros were converted to code and to run on OnClick off labels but they would not work. These were very large macro that also had quite a few "runmacro" actions and those macros were also large. It produced pages and pages of code and I think if someone had to go through and fix the problems or start from scratch they would have no hair left

Mike