Hi ,
I am new with MS Access and I start working with new company which they have a big Access DB file about 1 GB with many tables and relations ,I want to know what is the best solution for this situation to improve the performance and security and keep it easy also,
1- Export all data to MS SQL or MYSQL then link them to MS access as front end
2- Make more than MS access back end and connect them to 1 Access front End
3- Anything Else .
I think the first option is good but I don't have many experience with SQL and I hear some errors appears when do something like that because of ODBC .
I am really confused which choice I must go through it because I must stuck with it
.
Define 'security'. I mean the an Access database on a computer not connected to the outside world would be super secure. One on a local network isn't far off. Of course in every case you still could have a disgruntled employee come in and set the place on fire or delete records. So, what are you concerned about security-wise?
You would never make more than 1 backend. That method you are thinking of is 1 backend and multiple-front ends. That method might speed up your database, but it would actually be less secure (more doors to your house, more ways for criminals in).
My advice would be to pick specific goals and work towards them. What you've asked of us is pretty vague and I am unsure you even know what you want.
moving to ms sql or MySQL will not necessarily improve performance (it may even be worse) although you have better security options for the data - and requires more admin to maintain it.
1gb is not too large so I don't see the need to split the back end if that is the reason for doing so - have you compacted the backend yet?
To take advantage of mssql or MySQL server side processing (which is what will improve performance, everything else being equal) requires a major rewrite of all data handling routines from combo/listbox rowsources to queries/forms/reports and handling of recordsets within vba code.
An alternative to consider which requires virtually no change at all is to put the backend and all user front ends on terminal server or citrix. This provides additional security to gain access to the server and performance will be almost as good as if the backend was on your own machine.
Decide first which is more important, data security or performance. If the latter before looking to invest in additional IT resources look at how the front end works:
forms with just an unfiltered table as a recordsource will be slow compared with one which has criteria applied
tables with lookups and multivalue fields will also be slow (and will not migrate to any other db)
fields regularly used in criteria, grouping, sorting should be indexed
using domain functions in queries will be slow
there are plenty of other suggestions as well, just google 'improve access performance' or similar
Further to plog's advice, I'd like to know some of the details of the current situation re security and performance.
What exactly is the issue with current security? Application?
Please quantify the performance issues that you want to improve.
Your questions seem quite basic and I am a little surprised that you are thinking of changing an operational database from Access to SQL Server. Especially since you say you have no SQL experience.
Perhaps you could tell us more about yourself; your database experience; what your role is in the database/IT area; how you got selected to do this work since you are self-identified as new to MSAccess.
Ever since Access stepped away from Workgroup security, the ability to protect your data has fallen strictly on your shoulders.
If you are worried about security vis-a-vis "separation of duties" then the only way to keep folks from doing what they aren't supposed to do is to use a setup based on a startup form that is your traffic cop and that never goes away. Then the form has to know who is opening it and what role they are permitted to play.
If your security concern is to protect the data from being seen then you need to consider Domain-level security to keep out those employees who are not users of that system. On the machine that will host your BE file, you need to consider setting up a group identifier. Then you can give users the required access rights through the group ID by making the users members of the group. Then set NO ACCESS to anyone not in the group and rely on Windows domain-level security to block interlopers.
For specific roles, you have to remember that to use Access, every legit user will need rights equivalent to Windows "Modify" access. Therefore, finer granularity must be managed by the forms AND nobody can ever be allowed to see the Navigation Panel. There are articles all over this forum about hiding the ribbon and hiding the other visible features of Access itself.
As an answer to the "performance" issues, for ANY multi-user situation I would strongly recommend a front-end/back-end split where the primary data tables are in the BE and the FE holds the queries, forms, reports, macros, and modules. As a matter of pragmatism, if I have staging activity, I have some FE tables that get erased a lot because they are local scratchpads. But here is where you need to again be careful. Everyone should work from their own private copy of the FE file in order to minimize lock contention on the visual and procedural objects in that FE file. You can't stop data locks on the BE, but if you have separate copies of the FE, one per user, every lock on an object in the FE is PRIVATE, not SHARED, and so cannot lead to interference.
To give any more specific advice, I think we would need more specific questions detailing your goal in English, with minimal techie examples of anything unless it is a specific piece of code that is failing and the reason you think it is failing. Beyond that, all we would be able to offer is guidelines.
First of all thanks for all for help I will be more specific , My Access DB is split BE/FE and I have more than 25 users working in this FE I have many department working on the same FE and all time complain why the DB is very slow and the DB growing up and I afraid to be 2 GB, so I start looking to how to improve this, some article advice to work with SQL express , and some article use multiple BEs .
so I come more confused which is best for me .
I have also a little experience with MYSQL and VBA and good experience with Networking and domain level because I worked as IT coordinator for 10 years .
What about the size , is moving the DB from ACCESS to MSSQL OR MYSQL is good choice,
because I read some people complain from this solution maybe because of ODBC stability,
Is there anyone here try this solution to advice me ?
Really think you need to be deciding which nut you want to crack rather than which hammer to use.
Once you have determined why the system is slow, then you can form a strategy to resolve it.
At the moment the potential whys suggested in this thread include
slow network (easy to determine)
lack of indexing
poorly designed queries
poorly designed forms
users using same front end (an easy win to fix)
backend needs compacting (another easy fix)
plus as previously suggested google 'slow access' or similar to find a host of other reasons why the app may not be performing as well as it might be
moving to MySQL/sql server will not fix any of the above except compacting the backend - but instead replaced with index maintenance, table rebuilds etc - they don't just run themselves.
And in answering your question, one of my clients decided to move the backend to sql server - response time on opening forms, executing commands etc moved from a second or so to a couple of minutes. Reason? sql server had too many other users doing completely different things and heavy processing taking up all resources leaving very little for anyone else.
Agree with CJ's list and would add
-have you identified the issues to be addressed, and assigned them priorities
-get agreement on "what hurts most"
-then identify steps to improvement
Jumping to another software/database management system without redesign; improving design; removing /solving bottlenecks..... is just additional work until you know what you're going to address and when.
Good luck with your analysis and the project generally.
I went through one of these analyses. Took me a long time, but the key to the whole thing was to build some "instrumentation" in the FE file because that is where Access is actually running.
I put together a subroutine that had the ability to make an entry in a logging table local to the FE file. (Remember, this was for diagnostic purposes so had to be the fastest possible response, which is writing to a local table.) The data in the logging entry was basically an autonumber, a date/time stamp, a separate copy of the Timer() function that gives you milliseconds since midnight, and a text field. You called the routine with the text value as the only formal argument; all else was determined inside the subroutine. I didn't allow anything fancy to occur in the logging routine.
When I needed this to be running, I set a global variable to TRUE. The subroutine was essentially an If (global variable = TRUE) then... (do the logging) followed by the End If. So if you had not turned on logging, it was essentially only the overhead of the call, an If, and an immediate return. I had it set to automatically process the accumulated log entries when you turned off the logging feature by setting the global variable to FALSE. It reformatted stuff and wrote it to a file, then erased the FE logging table.
The log-dumper was trickier - but because logging was no longer the issue, it had the opportunity to take a little longer. There, what I did was take the entries in Autonumber order, translate the date, and then do a "value MOD 1000" on that timer entry, tack that behind the date/time entry, and follow that with the text field. What that did was allow me to find where the time was going pretty much to the millisecond.
In actual practice, I started by "instrumenting" the highest level subroutines and functions, and adding a log entry any time the form in question was going to go "idle" because it was waiting for user input, so that I would know not to care about the gap that was going to follow the "user is thinking" entry.
Then it was a matter of refining the problem by drilling down to the next level of action (where possible) by logging entry and exit for the next level of subroutines (and functions). Eventually I put a logging entry around each query, bracketing with start and stop times, and was able to exactly identify which query was causing me grief.
I should emphasize that you would not leave this kind of code floating around in a user's production copy of the FE file, but you might have a set-aside copy of the design master that you modified for this purpose. Once you identify which parts of your app are giving you the grief, THEN is when you need to consider how to fix it.
Another thing I did along these lines was that I had converted every query to use the .Execute method that is available for DAO databases. You can assure that you have exactly that kind of database by declaring a DAO.Database object in some module and using "SET MyDAODB = CurrentDB" so that you have pointer to the current DB that IS a DAO database type. Then you can do
Knowing the speed of the query and the records affected helped me decide where my time was going.
Just for the record, in my case I had to rethink a way to do the operation with SQL in a way that rather than using a "... WHERE .... AND field IN (SELECT target FROM tableX) ..." as a way of selecting the targets, I had to make the field indexed, make the update based on a JOIN, and rebuild the WHERE clause to have correspondingly fewer steps since the JOIN was doing some of the filtration for me. So in my case the timer helped me to identify an SQL statement that was sloppily coded.
Really Appreciate your help , Now I deiced to keep moving with Access and forget MYSQL or MSSQL , So can I make multiple BEs open by same FE is that good idea to solve DB size issue ,Is this solution improve the performance or no.
@CJ_London I didn't understand this "users using same front end (an easy win to fix)"
My Access DB is split BE/FE and I have more than 25 users working in this FE I have many department working on the same FE and all time complain why the DB is very slow
Everyone should work from their own private copy of the FE file in order to minimize lock contention on the visual and procedural objects in that FE file.
Basically every user should be running their own copy of the front end located on their own machine. If they don't, not only do you get the issues mentioned by Docman, but you also risk corrupting the front end, let alone making it impossible to maintain unless everyone is off the system.
The FE size is just 20MB but the BE more than 1GB , when I split the DB I sent to every users one FE file to working on it in his Machine should I Compact and repair all of them ?
Basically every user should be running their own copy of the front end located on their own machine. If they don't, not only do you get the issues mentioned by Docman, but you also risk corrupting the front end, let alone making it impossible to maintain unless everyone is off the system.
When I split the DB I sent to all users copy of the FE file to use it on his machine is that OK or no , why I can't use many BEs one for each department and link all of them in one FE is that bad idea ?
you can, but you won't be able to have relationship rules between the two db's and potentially make it slower.
I'm going to drop out of this discussion for now, but you are obsessing about the db size. Slow performance and size are not related. What matters is a well constructed back and front ends and decent network performance.
You have asked for advice and basically ignoring it in favour of your original two thoughts to resolve the problem.
investigate all of these and answer them
slow network (easy to determine) - not answered
lack of indexing - not answered
poorly designed queries - not answered
poorly designed forms - not answered
users using same front end (an easy win to fix) - answered, each user has own front end
backend needs compacting (another easy fix) - not answered and could solve your size issue
