Hi Sal & Bob

I have had this on a variety of machines - same problem.

I don't think SQL Server is the answer - I don't know much about it and I think it might be a bit expensive.

I have referential integrity, indexes etc.

I'm converting everything back to Macros to see if that helps.

And Bob, you didn't startle me, you scared the sh#^ out of me! :)

Later
Lisa

Lisa, and Roni do you know how to compile your database by using the
msaccess.exe /decompile
command? If the forms that are giving you problems have code, you may need to compile with this command. This will actually reduce the size of your file even further than a plain compile and compact and repair.
To do this,
-o to the Start menu
-select Run
-ype in msaccess.exe /decompile
        This will open MS Access
-From the file menu select the file you wish to compile and open it
-go to a form that contains code and open it in design view
-open the code module
-go to the Debug menu
-Select compile
        if the file does not compile, fix or remove any bad code.
After the compile is done, save all the modules (clicking save once ussially is sufficient)
Save any forms if prompted.
Close Access, re-open the file and compact and repair

This will decrease absolutly all the empty spaces in your database front end.

Check the size before and then after. Lisa, this should increase performance.



Sal

Thanks Sal,

I can definately give that a try.

Lisa: Can you post a sample of a macro or two and how you converted it to code? I ran with some macros before and found that it did speed me up when switching over to VBA.

-Roni

Roni Estein
[email protected]
https://www.e-drugsCanada.com

Roni & Sal
Hi Roni.
It's not that a specific macro/event runs slowly - I do realize that a specific event should run faster than the macro would. But since I converted all of my many,many macros to vba, forms take much, much longer to open in form view or design view - it's not instant and I've seen other databases with code and the forms still open instantly, mine can take a count of 8 or more, and I can here my computer tickin along thinking very hard to open the forms. I'm thinking that maybe I'm missing something in the libraries etc. I have removed the ADO library and am using the DAO library as discussed in the Smith/Sussman book. Forms that don't have any code on them open instantly, forms that have a little code open fairly quickly, and forms that have lots - take long. That's why I'm blaming the code!

Sal, I will try that decompile option you offered.

I guess cause I'm new to code I feel like I must be doing something wrong or there must be some errors somewhere that it's getting stuck on.

Thanks again for all of your help
Lisa

The reason that forms that contain code open slower is also because they are not compiled code.


Sal

Greetings to all,
I just had a small question about using SQL server as a BE DB.
I'm in the process of converting an access 97 database to access 2000! This is also a split db with BE on sql server 7.0 and FE on access 2000.
I was reading that Sal said:
My BE access databases (before conversion) do not have any queries, they consist of just tables of data! Sal, did you mean I should convert my FE queries to SQL views? Can you elaborate on that! thanks!
fortunately, I am not having any performance issues yet, but that's because we have a good network for development.:D

Sam Gharnagh
Jr. Programmer Analyst
Comp Sci at UofW

If you have any queries in yuor MS Access .mdb or .mde frome end, yes, you should create them as view on the SQL Server for improved performance. If you have no queries in the front end, then there are no queries to upgrade to views.

Going further, if you can create Stored procedures on the SQL server and pass parameters to those, use them as recordsources for your repors with ADO. Even Faster.

Let me know if that answers your question.

Sal

Hey Sal,
I have about 6 front-end access databases which each have bout 20 or so queries! would it be wise to try and convert them all into views/SPs in SQL server 7.0?
by the way, i think i was confusing you with my last post sorry about that!


Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW

Only if you will not be making changes to the view. Or you will have to refresh the links often.

By the way, the reason you do not have any performance issues, unlike Lisa and Roni is because your data resides in SQL Server. You have a true Clieent / Server application, they do not.

Also one more thing to consider, man, I need to stop.

Security is a lot stronger in SQl Server, therefore, if you create your views, you can administer security for all items in one central place, inside SQL Server. This will also increase performance because access does not have to be looking into the security file for access and then SQL Server looking into it's security, (to much redundancy)

if you have 20 or so queries in 6 front ends you really have 120 queries to maintain, and 120 queries that are killing your network. You may be able to bring it down to less than 40 if planned correctly.




Sal

Help Sal.

I tried the decompile you said, but I can't get into it.

It loaded Access 97 not 2000.

So I tried c:\Program Files\Microsoft Office 2K\Office\msaccess.exe /decompile

And I got error Cannot find the file or one of its components.

Am I missing the decompile thing in my Access 2000 installation? The Access 97 seemed to start OK.
Any suggestions.

Thanks
Lisa