Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-06-2016, 04:11 AM   #1
letsaccess
Newly Registered User
 
Join Date: Oct 2016
Posts: 14
Thanks: 25
Thanked 0 Times in 0 Posts
letsaccess is on a distinguished road
Post Need some advice

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
.

letsaccess is offline   Reply With Quote
Old 10-06-2016, 04:51 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,322
Thanks: 10
Thanked 2,263 Times in 2,215 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Need some advice

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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
letsaccess (10-06-2016)
Old 10-06-2016, 05:07 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,115
Thanks: 40
Thanked 3,606 Times in 3,482 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Need some advice

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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
letsaccess (10-06-2016)
Old 10-06-2016, 05:16 AM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,060
Thanks: 79
Thanked 2,003 Times in 1,951 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Need some advice

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.

Good luck with your project.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
letsaccess (10-06-2016)
Old 10-06-2016, 05:35 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,220
Thanks: 86
Thanked 1,620 Times in 1,503 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Need some advice

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
letsaccess (10-06-2016)
Old 10-06-2016, 11:24 AM   #6
letsaccess
Newly Registered User
 
Join Date: Oct 2016
Posts: 14
Thanks: 25
Thanked 0 Times in 0 Posts
letsaccess is on a distinguished road
Re: Need some advice

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 .
letsaccess is offline   Reply With Quote
Old 10-06-2016, 11:35 AM   #7
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,060
Thanks: 79
Thanked 2,003 Times in 1,951 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Need some advice

Each user should have their own copy of the FE on their own PC.

See this re MultiUser MsAccess Performance

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 10-06-2016, 03:32 PM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,115
Thanks: 40
Thanked 3,606 Times in 3,482 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Need some advice

Quote:
good experience with Networking and domain level because I worked as IT coordinator for 10 years
so look at the networking side of things, perhaps you have a slow network.

I also concur with JDraw, each user must have their own copy of the front end on their own machine - this will also reduce network traffic
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
letsaccess (10-06-2016)
Old 10-07-2016, 11:51 AM   #9
letsaccess
Newly Registered User
 
Join Date: Oct 2016
Posts: 14
Thanks: 25
Thanked 0 Times in 0 Posts
letsaccess is on a distinguished road
Re: Need some advice

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 ?
letsaccess is offline   Reply With Quote
Old 10-07-2016, 02:37 PM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,115
Thanks: 40
Thanked 3,606 Times in 3,482 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Need some advice

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
letsaccess (10-08-2016)
Old 10-07-2016, 03:02 PM   #11
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,060
Thanks: 79
Thanked 2,003 Times in 1,951 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Need some advice

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
letsaccess (10-08-2016)
Old 10-08-2016, 10:01 AM   #12
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,220
Thanks: 86
Thanked 1,620 Times in 1,503 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Need some advice

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

Code:
MSecLOG "Code marker 101 Run SQL"
MyDAODB.Execute "sql-statement", dbFailOnError
DBRecs = MyDAODB.RecordsAffected
MSecLOG "Code marker 101 SQL affected " & CStr(DBRecs) & " records"
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
letsaccess (10-08-2016)
Old 10-08-2016, 10:06 AM   #13
letsaccess
Newly Registered User
 
Join Date: Oct 2016
Posts: 14
Thanks: 25
Thanked 0 Times in 0 Posts
letsaccess is on a distinguished road
Re: Need some advice

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)"
letsaccess is offline   Reply With Quote
Old 10-08-2016, 03:35 PM   #14
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,060
Thanks: 79
Thanked 2,003 Times in 1,951 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Need some advice

What exactly is the DB size issue?
Have you run compact and repair on FE(s)?

You do NOT need multiple BEs.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
letsaccess (10-09-2016)
Old 10-09-2016, 05:00 AM   #15
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,115
Thanks: 40
Thanked 3,606 Times in 3,482 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Need some advice

Quote:
@CJ_London I didn't understand this "users using same front end (an easy win to fix)"
you said

Quote:
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
Docman suggested

Quote:
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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
letsaccess (10-09-2016)
Reply

Tags
access , link , odbc , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Advice and integer and long advice required smiler44 Excel 4 10-30-2013 07:50 AM
Need Advice Starnheavn General 4 05-04-2012 11:43 AM
Advice? sueviolet Tables 7 07-11-2003 12:55 PM
Bit of Advice Chimp8471 General 6 06-06-2003 01:48 AM
Looking for some advice please.... Monti2 General 3 05-07-2003 09:53 AM




All times are GMT -8. The time now is 02:42 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World