In over my head. (1 Viewer)

[Ziggy_McG]

Good Afternoon (Or perhaps morning?) my friends in the U.K and the world,

I'm an extreme novice to Access (No VBA or SQL experience) working at a call centre in Melbourne, Australia and have created a database as a replacement for our current personnel files. I have done so through a lot of trial and error, many tutorials and hours of reading posts on this very forum.

I have a few specific questions but mostly I just would like general advice if you'd be so kind. Firstly I'll outline what the db is:

Currently each staff member here has an 'Agent Development file' which is an individual Excel spreadsheet containing the following sheets:

Personal Details (Name, DOB, EmployeeID, Emergency contacts etc.)
Discussion Record (A running table of relevant conversations or notes written by their manager)
Absence Record (A table of all their absences, reason for absence, type of absence etc)
Performance Management (Basically just some info HR likes us to keep track of for more serious conversations.)

We have several hundred staff and each of them has an individual spreadsheet containing this information which I'm sure many of you are as horrified about as I was. On top of that because of the (Unnecessary) Macro's and splicers used each file takes up to 30 seconds to load. They are incredibly slow and inconvenient but we've grown so fast that nobody's stopped to fix it until now.

I have spent weeks powering through immense frustration to teach myself how to use access and feel that I've done okay so far - I am still a long way off rolling this db out for use but have reached a point where I'm worried it won't cut it.

What I have so far:
6 tables containing the same information as the above sheets as well as tables for Team Numbers, Operations managers and other such information which I have made in order to Normalise my tables which I understand from you all is quite important

I have 15 Queries which are really the heart of what I'm making. Effectively they all just search for different information based on selections made in combo boxes (I'll explains specifics further down.)

Finally I have 11 Forms - 3 of which are sub-forms and 5 of which are pop-up 'input' forms with Data Entry mode enabled. The primary navigation form is called EmployeeSelector and I have it set up with two combo boxes:
Team - Select from the list of teams (Straight from a table)
Employee - Select from a list of agents with the TeamID selected in the above.

From here the user presses a button which opens the EmployeeDetails form which shows info using a query to select the info of what's selected in Employee combo box. This form also contains all the subforms I mentioned above which also use queries to search for information based on the combo box selections.

So far so good right, it all works despite my many mistakes and it's already much better than the excel versions as far as usability, aesthetics and performance.

Which brings me to my primary concern - Performance.

Given the information provided above I will outline below how much data I expect to have in this db (It just has example data at the moment)

Approx. 500-700 Current Staff each with an EmployeeDetails record (14 Fields, All Short Text, Numbers and Dates effectively)
Anywhere between 10 and 100 discussion records per employee (7 Fields. One long text and some numbers,dates,short text.)
Anywhere between 0 and 100 Absence Records per employee (8 fields, Short text, Numbers, and dates again plus a yes/no field.)
Anywhere between 0 and 50 Performance Management Records per employee. (15 Fields, Dates, numbers, one long and a few short text,)

I have already split the database (Another hot tip I learned from here) and my understanding is that the most efficient way to provide access is to send each user a FE which is fine. I basically want to know, will it function and perform reasonably with this much data? Like I said I haven't used access before and I don't know the limitations.

Additionally I worry about concurrent users - I've read that access supports 255 (I think?) concurrent users but I'm unsure how performance is affected by number of users. I would expect a maximum of 40 concurrent users and a realistic average of 15-20.

I can provided a stripped db if required but I'm moreso wanting some assurance that this will get the job done before I 'Over-commit' as it were. Particularly as I intend to engage professional assistance before going live I don't want to waste any money. I'm also happy to provide any further information if required.

I really appreciate the space to air my concerns and look forward to reading your responses.

Thanks,
Ziggy. :banghead:

 

[isladogs]

Access should cope with the amount of data you describe providing the data is well structured for which normalising plays an important part. You should also index fields that will be used to search or filter your data. There are other factors that affect performance but that's a good starting point.

Do you really need separate forms of data entry? A form can be opened in data entry mode to New record.

Theoretically, Access can have up to 255 concurrent users in a split database but performance will slow to a crawl long before that and some crashes will occur. You should be OK with the number of users you describe but it would be worth preparing to upsize your BE to SQL Server for improved stability and security. THE Express version is free. It also allows a maximum of 10GB of data compared to 2GB in Access

You haven't mentioned any reports which you will need if data is to be printed.

In addition, you haven't mentioned whether you are using VBA or macros. I would strongly recommend using VBA as it far more powerful.

More specific advice can be provided to any of the above but this answer is already too long. Just ask when you have a specific question.

 

[CJ_London]

I agree with Colin's comments but would add that another impact on performance is the network performance. Slow network, slow performance. And users should have a wired connection to the network, not wireless.

There is nothing worse that doing all your development locally, then move the BE to the server to find performance drops of considerably. It needs to be a consideration during development so have a development BE on your server. This link may prove beneficial for ways to maintain performance. https://www.access-programmers.co.uk/forums/showthread.php?t=291269

 

[Ziggy_McG]

Thank you both for your quick responses,

Firstly Isladogs:

In regards to having separate Data Entry forms;
Some of my forms can have data entered directly however I'm trying to keep everything as streamlined and simple as possible and some of the forms look a bit too busy with new record input on the form. Many of my users aren't technically advanced and may struggle if presented with 'Too Much' to look at. I will keep this in mind though and if/when I start to experience issues I may remove these.

In regards to Concurrent users;
Glad to hear my amount of users should work. the SQL server option is something I had heard of however I'm a bit wary of it as I have no experience hosting a server of any kind.

Reports;
I only have one report which draws information about individual employee's absences.

VBA vs Macro's:
As I'm sure is the case with many Access newbies I have primarily used Macro's although I have managed to write some extremely basic VBA code for certain things with significant help from various articles and threads and I wouldn't really describe myself as understanding VBA. I know that Access has a function to convert Macro's to VBA. I've been cautious to do so as I'm unsure how well it works? Would using this function to convert to VBA help with performance? If so I might try that as well (Likely on a copy first before my main FE)

CJ_London:

This is a factor I considered already - I sent a FE to one of the users to test with the BE hosted on our network and her PC loaded the database in 4.3 seconds (Keep in mind this is just with the example data) and I opened all the forms and saw no issues. Most of the PC's are wired in although some staff do have laptops. Having said that the current files are stored on the network so any slowness they would experience they already are I suppose.

Also thank you for that link, it looks very useful for my purposes.

Again thank you both for your replies.

 

[CJ_London]

I'll leave Colin to respond to his part!

This is a factor I considered already - I sent a FE to one of the users to test with the BE hosted on our network and her PC loaded the database in 4.3 seconds (Keep in mind this is just with the example data) and I opened all the forms and saw no issues.

Just be aware access is not excel - excel loads the whole file to memory when opened, access loads what you tell it - which almost certainly won't be the whole file. So check what is actually done in those 4.3 seconds. Normally this would be loading a login or navigation form of some sort, both of which will have minimal interaction with the BE on loading.

Most of the PC's are wired in although some staff do have laptops

Sorry, I wasn't very clear. The point is not so much about performance (although wireless is often slower) but corruption. Wireless is not a stable environment, you can get small interruptions which can break the link between FE and BE and leave the BE in an uncertain state. If wireless is going to be a factor, consider user Sql Server express for the BE which is more robust in a wireless environment.

I'm sure you won't want to get into the detail but copying a whole file to another location or into memory (like Word or Excel) uses a completely different process (which is wireless robust) to do the transaction. With databases there is a whole lot more going on around access rights, file locking, recordsets etc. An interruption halfway through this process will leave the BE in an uncertain state which you may or may not be able to recover from.

So the other tip is to make sure you do regular backups of the BE in case you cannot recover.

Sounds scary? not really - do it right and you won't have a problem. You've made a good start by splitting the db and ensuring users have their own local copy of the FE.

 

[isladogs]

Agree with CJL about network/wireless etc

In regards to having separate Data Entry forms;
Some of my forms can have data entered directly however I'm trying to keep everything as streamlined and simple as possible and some of the forms look a bit too busy with new record input on the form. Many of my users aren't technically advanced and may struggle if presented with 'Too Much' to look at. I will keep this in mind though and if/when I start to experience issues I may remove these.

I accept your point but creating multiple forms for the same data is additional work. The fewer objects you need to create/maintain, the less work you will have

In regards to Concurrent users;
Glad to hear my amount of users should work. the SQL server option is something I had heard of however I'm a bit wary of it as I have no experience hosting a server of any kind.

You may find this utility useful: Available Connections

Also look at this link Optimising Queries

The main reasons for upsizing to SQL Server are stability and additional security.
However, converting the BE to SQL Server is a significant learning curve. Initially you may see no performance benefits. However when its features are properly utilised you should see significant advantages in performance as well

VBA vs Macro's:
As I'm sure is the case with many Access newbies I have primarily used Macro's although I have managed to write some extremely basic VBA code for certain things with significant help from various articles and threads and I wouldn't really describe myself as understanding VBA. I know that Access has a function to convert Macro's to VBA. I've been cautious to do so as I'm unsure how well it works? Would using this function to convert to VBA help with performance? If so I might try that as well (Likely on a copy first before my main FE)

You can convert all macros to VBA - see button on Design ribbon ...Tools section. The code is always more verbose than needed but it gives a good start in understanding VBA including the use of error handling and comments
As you suggest, you should ALWAYS do development work on a copy of the database

Let me know if I've forgotten anything you asked about

 

[Mark_]

One note regarding concurrent users; while you may have 40 odd users who CAN access the system, odds are you won't have them all using it at once.

This does bring up a different issue you may encounter though, are you sure all users have the same version of access? If not, you may run into issues where one computer runs nicely but another has ... issues ...

While it is not a requirement, as a new developer it will help you avoid unusual issues.

 

[Ziggy_McG]

Thanks all for your replies,

CJ;
Thanks for the info, only 3 users are on wireless and they likely won't be making many updates, moreso viewing information. I will keep this in mind however and it seems to be another reason to use a server.

Colin;
Thanks for the links, I now have a lot of reading material for the day. In regards to converting to an SQL server, it's something I'll do some research into but as you said I figured it would be a bit a challenge to learn. I will convert my Macro's to VBA today I think and see how that goes. It should hopefully help me get a better understanding of VBA as well which is a nice bonus.

Mark;
Thankfully the whole office just undertook a total hardware upgrade earlier this year and all run the exact same version of Windows and the whole MS suite which means I am very confident that for the foreseeable future my users will indeed have the same version.

Thank you all for the help, I'm going to spend the day reading the links you've provided as well as converting my Macro's. I'll attempt to institute some of the performance improvement tips mentioned in the linked threads/articles.

Thanks,
Ziggy.

 

[Pat Hartman]

Thanks for the info, only 3 users are on wireless and they likely won't be making many updates, moreso viewing information.

Unless you can always use wired connections, it is probably best to plan on using SQL Server sooner rather than later. Corruption could happen today or never. It's a matter of exactly what Access is doing when the connection drops. SQL Server is better structured to handle the network disruptions. The FE might crash but the data is unlikely to be corrupted as a result.

 

[The_Doc_Man]

Something that I will amplify as a practical matter of being the "chief cook and bottle washer" of a DB shared among a lot of users:

You should ALWAYS have a development environment that you use to test out new code. It needs to have its own separate back-end (which could just be a copy of the production back-end). Then on this forum, look up "front-end auto update" for discussions on how to make everyone automagically refresh the front end files with each launch. THEN if you have to update the FE (only), all you do is replace the master FE copy from which the auto-download occurs and everyone stays up to date.

If you are REALLY trying to do this right, you also have a TEST environment that is different from the development or production environments. Then you stage things from DEV to TEST to PROD. That way, if you bollix up the works during testing, you just make a new copy of everything, fix the problem, and nobody knows about your problems except you.

The ONLY time this gets tricky is if you are ever forced to change the back-end schema, because that requires down time. However, regarding down time... one last thought.

In a big environment with lots of users banging away, there will be moments when stuff happens. If you set aside a time when you can do periodic maintenance, make backup copies, do compact & repairs, etc. then your users start to expect it and will not gripe if they know that "Every Friday at 6 PM the DB goes down for one hour for maintenance." And if you need to diddle with the BE file, you suddenly have a window of opportunity if you just plan your operations carefully.

Just plan ahead. Take the attitude that "stuff happens" (and has to be fixed). Plan to isolate the destructive testing and plan to have moments set aside for production schema updates. PLAN to do maintenance. (Get a buy-off from the boss on this idea.)

It sounds like a lot of work. Heck, YEAH it is a lot of work. But there exists a phrase or two in the language that directly relates: "Pay me now or pay me later. But pay me a LOT more later." And... "If you don't have time to do it properly now, how will you EVER find time to fix it properly later?"