Database is TOO slow

[Badnapp]

I'm beginning to think that despite the downfalls of my database design, the major issue is related to network performance. I have some clerks who work with in the city with me who are experiencing little or no lag, but I also have clerks in adjacent smaller cities who say it takes them minutes just to open a combo box.

I am also almost positive that our servers not maintain a persistent connection in some fashion. I have had 2 clerks already report back to me saying that they cannot access the database due to not having access to the shared drive (I have attached a screenshot for fun). And then if they wait long enough and check back on Network Locations, the shared drive is again accessible and they can use the database. I made changes based on your suggestions Jdraw (added primary key to tasks instead of TaskID field, changed the rowsource of the AssignedBy combo box and got rid of the JudgesNames query). I made an attempt to establish relationships but I didn't get far. I made a copy and tried to use the "Table Analyzer" tool instead but that made a mess of everything so I'm still at square one. Right now I am reading about keeping an open connection to the back end of the database, and I'm hoping this is the golden ticket.

Basically the front end runs about 50% better right now. Storing most of the tables locally solved a lot of speed issues for people, instead of having the ones that remain static linked to the back end. It still seems to really bog down is when the front end has to speak with the back end in some fashion. The only two tables that are stored in the back end are "Tasks" and "AssignedBy." Tasks has to be stored there because those are the actual records we're trying to create. I want to keep AssignedBy in the back end so that if a judge is appointed or retires we can quickly update choices in the front ends.

Thanks for everybody's help so far, I haven't given up quite yet.

 

[jdraw]

No screen shot was attached.
Can you tell us more about your network -perhaps someone with more network expertise will see something.

There are many reported speed issues when using WAN as Dave pointed out.

Just curious:

Do the databases at each office have to come together for some reason?
Are the individual offices somewhat independent?

Do you have any specifications regarding the database you were asked to create and have created?

I'm not sure exactly how this would work in a mde/accde, but you could consider copying the
AssignedBy table to the FE when the user starts his/her session into a local table. When ajudge reitres/new hire arises, the "Admin" could update the BE AssignedBy table. The latest values would be downloaded when the next user session starts. That could reduce slowness.

You said some of the slowness has been reduced considerably for some users. Is there anything special/unusual about those that are still slow?

 

[Badnapp]

Hey Jdraw thanks again for all of your continued assistance, here we go...

Screenshot: Woops forgot to attach it, I have added to this post.

WAN Issues: I have been looking into the DAO Opendatabase method of enforcing a persistent connection and I thought I had done everything correctly but it's not working. I created a module exactly as suggested, the code is included at the bottom of this reply.

So as I understand it, I would call this module on open of my first form, and close it when the last form closes. My DB loads the form "frmAccessLevel" at startup, so in the OnOpen event of that form, I placed the following vba code:

Private Sub Form_Open(Cancel As Integer)
OpenAllDatabases True
End Sub

I thought this would call the module I created, but apparently it does not. I can tell this because I tested it as suggested and there is still a temporary (.laccdb) file that is created whenever I open a copy of the front end. I'm sure it's just another n00b error. Any suggestions?

Re: Just Curious Each office functions independently. The clerks in each office are managed by one person who is located in my office. He just wants a way to print reports very quickly of all of the tasks they have worked on for each week/month. So I figured the clerks enter their data, it is stored in the back end, he uses a copy of the front end to view reports that I set up.

Specifications: Create a database in Access that 26 clerks can use simultaneously if need be to enter tasks they have worked on during the week. Headings for the tasks need to be "Name, Date, Assigned by, DivisionofWork, Subcategory of division, Type of Work, and additional notes). Database needs to be able to run reports at any given moment related to productivity by clerk, work load by region, work load by judge, workload by division of law.

Re: Copying table to front end: Interesting suggestion, I'll give it a shot if I can't improve the performance via this DOA method.

Slowness/Increase in Speed: The users who have noted it being exceptionally slow are the users farthest away from me geographically, which is probably a coincidence. I rolled out a new version of the front end to one of the clerks this morning with all tables stored locally except for tasks and judges. I asked her to create new tasks and I did the same simultaneously on my machine. We both noted a major increase in speed in all areas except for --> When the DB first loads up it is really really slow, when you click "New Task" it is slow to load that form, when you click the "AssignedBy" combo box the application more or less freezes for a short period of time before offering selections.

+++

Persistent Connection Module Code:

Sub OpenAllDatabases(pfInit As Boolean)
' Open a handle to all databases and keep it open during the entire time the application runs.
' Params : pfInit TRUE to initialize (call when application starts)
' FALSE to close (call when application ends)
' Source : Total Visual SourceBook

Dim x As Integer
Dim strName As String
Dim strMsg As String

' Maximum number of back end databases to link
Const cintMaxDatabases As Integer = 1

' List of databases kept in a static array so we can close them later
Static dbsOpen() As DAO.Database

If pfInit Then
ReDim dbsOpen(1 To cintMaxDatabases)
For x = 1 To cintMaxDatabases
' Specify your back end databases
Select Case x
Case 1:
strName = "J:\Law Clerks - Weekly Reports\Reports Database\testmejoy\LawClerksReportsDatabase - v1.1 - Copy_be.accdb"
End Select
strMsg = ""

On Error Resume Next
Set dbsOpen(x) = OpenDatabase(strName)
If Err.Number > 0 Then
strMsg = "Trouble opening database: " & strName & vbCrLf & _
"Make sure the drive is available." & vbCrLf & _
"Error: " & Err.Description & " (" & Err.Number & ")"
End If

On Error GoTo 0
If strMsg <> "" Then
MsgBox strMsg
Exit For
End If
Next x
Else
On Error Resume Next
For x = 1 To cintMaxDatabases
dbsOpen(x).Close
Next x
End If
End Sub

+++

 

[Badnapp]

Update:

I made 75% of the changes listed on this site that I stumbled across while following your advice.

http://www.granite.ab.ca/access/performancefaq.htm

I have been testing the database this morning with one of the law clerks and she says it seems to be operating very quickly. I don't want to get excited yet because it is still not under full load, but I am hopeful. Will keep you updated out of curiosity

 

[jdraw]

If your first Form is the Login, then I would say that is where you do the Open. If you have a Logout form then that is where the Close would go.

I think you will get an .laccdb when you open a database. The FMS link deals with that.

I found a link that shows a slightly modified version of the code you have. You could try it and see if it works as you expect. It allows multiple predefined backends.

I found another link (granite) that has related info.

Are all users/clerks mapped to drive J?

You have some sort of "privileged" Access to the backend to adjust things, right?

You do have a backup of your front end accdb also?

Let me just ask a far out question:
Users are somewhat independent. They use a set of common tables and create their own tasks. What if, they had standalone versions of the database; and you had a routine that submitted their task lists to the central database at the close of business each day or week?
They each have their task lists. Central manager can access the Central database for reports (current up to last night) at any time. Just a thought for worse case options.
[Rationale for this idea is that an online real data base can change instantly -- so no report is necessarily up to date after it is printed.]

How are things going?
I guess you haven't tried putting judges table in FE to see effect.

UPDATE: I was sitting here reading and typing and hadn't seen your post @3:30.


Added this link re WAN It deals with JET, but the ideas may still apply to ACCDB. Albert has been around access for a long time. He was part of Granite (and may still be). @10 PM

 

[Badnapp]

Well if nothing else I am learning a lot about sharing an ms-access database over a network. Especially after reading the last link you sent me re: file sharing over a WAN, I feel like I have a better idea of what is going on here. Our drives definitely disconnect intermittently. For example I logged in this morning and all of my drives had "x" beside them, but as I clicked on the drives the "x" disappeared. A couple of clerks had mentioned the DB wasn't working, then they would click on the J:\ drive, and it would start working.

I didn't password protect the back end yet, because I am back in the testing phase. However, none of the clerks have reason to access it. The only person who would make changes to the back end is me. The manager involved would prefer to just have access to the front end because he is not computer savvy and is concerned about accidentally corrupting data.

I did backup the .accdb file of the front end on your advice. I have the whole file before split, plus the back end and font end .accdb files on my personal drive as I move forward.

I haven't tried putting the judges table in the FE, because I was quite bogged down yesterday with all of the other changes and I wasn't sure how to update it on everybody's front end if a new judge is appointed or retires say, tomorrow. I think you had proposed a solution but I can't remember what it was.

Re: Your far out question: How would you implement something like this? At this point I am considering transferring the Tasks table and Judges table into a small SQL database and linking that to the front ends if all of the changes I applied yesterday don't help the speed. If I cannot for some reason create the BE in SQL I might be open to trying this method. In the meantime I was able to enforce a persistent connection and I renamed the files, moved them closer to the shared drive route (BE was deep, deep in folders before) and changed auto to none in BE properties etc etc, so I'm really hoping that helps. I have one user who is going to try to break it with me today, and if we both agree that it is performing faster I will roll it out to the clerks and go from there.

I really didn't expect this entire can of worms getting into the project. It seemed like such a simple request when I took this on a month ago, and it was rather straightforward until multiple users shared it over the WAN, now it's a headache every day. I am learning though, and I definitely haven't given up hope that we can have a smooth ms-access DB for 26 users across a WAN, it's just a difficult thing to sort out

 

[jdraw]

Good stuff. I think you are right in that some of the speed issues are network related. So we can continue to try a few things to speed up what we can with Access with the help of the forum.

When you have multiple users (26 here), I would say you try changes on a copy of your accdb (I'd call it something like LatestDev). And I recommend you back it up regularly (nightly). You don't want to lose the work you've done, and have to repeat it.

I would name your Operational/production frontend and treat it as gold --no one touches it. Back it up in a very safe place (even 2 places) since it is the key source for making changes to the front end.

If you make changes in LatestDev, and after testing you find it "better/acceptable" performance, you plan a migration to Production/Operations.

A general list (not final by any means) of things to do would be :
-backup LatestDev accdb
-split as required
-create and test the accde FE
- have users(include reports) test the accde with BE
- if everyone says Go!, then
- put a copy of BE on shared drive if changed
- put a new copy of the accde on each machine (you could work out logic to automate copying latest version to local PC as part of your application)
- monitor performance/issues/complaints

Do you have regular backups of your shared drive?

 

[gemma-the-husky]

Slowness/Increase in Speed: The users who have noted it being exceptionally slow are the users farthest away from me geographically, which is probably a coincidence. I rolled out a new version of the front end to one of the clerks this morning with all tables stored locally except for tasks and judges. I asked her to create new tasks and I did the same simultaneously on my machine. We both noted a major increase in speed in all areas except for --> When the DB first loads up it is really really slow, when you click "New Task" it is slow to load that form, when you click the "AssignedBy" combo box the application more or less freezes for a short period of time before offering selections.

this observation.

no, this is not a coincidence - In order to run a query (and behind any access form is a query), the data in the query has to be transferred to the users machine over the network, in order for access to execute the query.

The more data, and the further it has to go, the longer it will take. offices with slow broadband will see a real impact.

So, the solutions are

1. redesign the database to require a minimum of data to be transferred. you can help by preselecting columns, and rows via your queries, and trying to avoid functions that can only be evaluated at run time. this needs some trial and error. although access databases are not client-server, nevertheless, the backend is still clever enough not to send all the data.

2. the alternative is to use terminal services, which can carry a considerable expense, and effectively means the distant users run process on a host computer at a central location

3. to get around these problems, (and what jdraw has alluded to) another solution is to try and have separate local databases, with a methodology for combining them centrally at some point. The issue here is that you need to be careful how you manage keys, to avoid duplication of keys, which would cause problems when you try to re-combine the data.

4. and the other approach is to put all the functionality on to a web database (which clearly avoids the need for access on the users machines) but is by no means a trivial solution



It all needs a lot of consideration. This isn't a problem just with access. it is the same with every programme. The data has to move around the system. On a 100Mb LAN its painless. On a slow WAN, it is painful


so, design considerations in the way you build queries

and finally, the best solution for the pc's themselves is for them to be wired, and not wireless.

 

[Badnapp]

Hey Gemma, I work for the government, and as a result our networks tend to be unquestionably slow. I'm not sure that I have the resources (mostly time, but also knowledge) to redesign the database, so I'm hoping some of the duct tape that we apply will hold this project together. In the long run, it really is a small project that I took on the side and it was my first foray into database design, although I doubt it will be my last because in addition to how powerful these programs are they're also very fun to design and tweak.

I've made all of the changes you listed jDraw. I realize that I wasn't backing up my copies enough, and when I was they weren't organized correctly. I was simply renaming the file v1.1 v1.2 etc when I made changes, and so I had a really cluttered folder of .accdb crap files. I went through and organized it to keep the files that I need, and reorganized the folders based on your recommendations.

One thing that I didn't quite understand is: - have users(include reports) test the accde with BE

Do you mean, split the database and send a copy of the front end to each user as I have in the past? OR... Do you mean, send the users a copy of the whole database (be+fe) and THEN after they test the WHOLE file roll out only the FRONT END. Did I get this right?

 

[jdraw]

That's a lot of info since my last post.
Dave's info is great, as always. His comment about query behind form and ensuring only the right data was being transferred is what I was mentioning. Make sure you only ask for what you need.

When I was suggesting the testing, I wanted the testing by some of the remote users to ensure you were testing in "real life" conditions. No - not a fe/be to each. I want to test the latest BE with some users using the new FE.
1 BE many FEs.

Organized backups are critical especially when something goes bad. Just look at posts on forums where people have a corrupted database and can't continue because there is no backup ( or they don't know if there's one or not).

How are things at the moment?

 

[Badnapp]

Well one thing in particular came to my attention today and I'm not sure if it related but I believe it does...

On a whim, I made a copy of the whole DB and ran the table analyzer on it. The table analyzer suggested that I store all of the columns from my "Tasks" table in separate tables, and run a query labelled "Tasks" to pull all of the information together. Now obviously this botched my forms because they are looking for field "Tasks.TaskID" which is not a field in the query etc etc... So there are some minor formatting issues involved, but I'm assuming that is the direction you guys want me to go in the future? I am starting to see how it is taxing on the program to reference the entire tasks table all of the time. For example when they log in and are looking at their task list, all that they can see are their records because it is filtered based on login information, however what is happening behind the scenes is Access is pulling all 1000 records from the Task table and filtering them for the user. By the end of the year, we will easily have 10,000+ records in that one table, and naturally I start to consider the limitations of everything that you guys have mentioned.

So, at this point I am considering a restructuring of sorts and trying to determine best practice. First things first, no clerks have replied to me (they're very busy during the week) in regards to testing. I would like to roll out the new front end linked to the new back end to the clerks farthest away from me, and see what kind of errors they experience. If it's still drastically slow, then I will consider a) redesigning the database using a query for Tasks or b) creating an SQL database to store the back end and linking that to the front or even c) a combination of both.

Perhaps if I do decide to redesign the tables, one of you could have a quick glance at the finished product and let me know if it looks more efficient?

So to Summarize what you've helped me with so far:
Performance
1. Enforced persistent connection with back end
2. Shrunk 12 linked tables down to 2
3. Subdatasheet name property changed from [auto] to [none]
4. Track name autocorrect turned off
5. Placed .accdb BE closer to root of shared drive (it was 5 or 6 folders in)
6. Shortened name of all files involved
7. Added primary key to all tables (somehow it was missing from Tasks)

Maintenance:
1. Created 3 folders - LatestDev (+ extra backup folder), Old Versions, Operational (FE, BE, DB inside)
2. Backed up all information on my personal C:\ drive
3. Created a folder for notes/logs of changes to the database to track what I do

I feel like since I posted this thread, my database has really started moving in the right direction and I'm learning a lot as I go. I'm not positive that I can make this one 100% perfect, but I am certain that I can develop a working copy eventually, and that I won't make as many noobie errors when I'm designing databases in the future. I think in hindsight, this would have been much simpler if all clerks were working in the same building as many issues seem to be related to WAN performance and I would have indicated that to the manager involved, but hindsight is always 20/20.

 

[jdraw]

What are the F14 F15 fields in Task?
Why did my test records not get a TaskID assigned?
Clerks are in Regions, and Tasks are identified with Region --are these the same, or just come from the same list of regions?
How is CaseName determined in Task table?
What is Date of Record? Task creation or the date these hours worked are applied.
Is there a task record for each day the clerk worked on the task?
Do you have a Region Table? You should.
What do each of the fields in Judges table mean RSJ, FamCrt, Super.
Can Judges work in various Court Houses -- why is CourtHOuse in the Judges table (repeated data)?
Judges table has no primary key?
SubCategories are not Distinct (see jpg)
There are 415 distinct subcategories.
There 451 subcategories in your subcategories table which implies duplicates (but you have separated these 451 Subcategories by divisionOfLaw into separate tables with no Link to the DivisionOfLaw table. So there is some redundancy. So instead of having distinct Subcategories and joining these to the DivisionOfLaw; you may have to keep your individual DivisionalSubCategory tables because you have existing records.
I suggest JudgeId autonumber PK for Judges table,
Courthouses and others could be placed in separate tables, but needs more review.

Here's a preliminary model attached - image a lookup to Judges table to satisfy AssignedBy

That's enough for the moment.

 

[Badnapp]

Wow! So much work to do

1 - What do you mean f14 f15 fields?
2 - I'm not positive, probably because I was messing around deleting the private records before I sent a copy to you. I just checked on my version and a taskid IS created.
3 - Yes, these are the same. In tblUser I stored the clerks and their respective regions. When they log in, that form is hidden but stays open. The combo box in that first login form is referencing the clerk - their region - their password. So when they create a new task, 2 of the combo boxes on that page are auto-populated with their name and region. Thus the clerk's name and region are always tied together.
4 - Casename is a textbox. The clerks work on SO many different case files throughout the year, I have to leave it open ended so they can distinguish what they worked on.
5 - Date of record refers to the day that they worked on said task. ie most clerks enter hours on friday, and indicate which day they allocated which hours to during the week.
6 - Yes, the clerks create a task record each day they work on a task. They had asked me if there was a way to store tasks and update them but I couldn't figure it out. They want to be able to "pull up" old tasks and add hours to them. ie If a clerk works on the same memo every day of the week they want to be able to enter "new task" once, create the info, and then copy and paste it 5 times in some fashion but update the hours worked for each day. The idea was suggested to me after I had built everything, so I don't really care to implement it.
7 - No, there is no region table.
8 - The fields in the judges table are ALL irrelevant except for first and last name. The table is copied from an excel spreadsheet and now that I think about it, I should have only imported 2 columns not the whole spreadsheet.
9 - Judges can be assigned to various courthouses, they move as workload demands. Again the courthouse field is irrelevant to the data. All we need to know is which clerk from which region did which task for which judge.
10 - I just reimported the Judges excel table, got rid of all unnecessary columns, and added a primary key.
11 - Some of the subcategories overlap by nature and my ignorance of database design caused me to not plan for this. I can delete the records from January on Feb 1st as soon as I print records and then restructure the tables at that point, so I think I will do that going forward. I see what you mean, I should have put all of the subcategories in one table and linked them out from there. That way I wouldn't have duplicate data in my tables. I see now that I had to think this through more when setting up tables. In my head, it was logical that you can appeal a criminal, family, or civil case so appeal should be in all three tables, but it only confuses Access by doing so. I'll look into a restructuring of subcategories today on projectdev version. Also it would probably make more sense to the clerk and be less legwork for access. That way my subcategory combobox doesnt have to be cascaded based on the division combobox either.
12 - I used JudgeID by chance when I added a PK to the judges table so that's a +1 for me.
13 - I don't think I need to place courthouses etc in other tables because I just yanked all of those useless columns. Judges tables is now simply JudgeID (PK) | First | NAME
14 - I removed the query for JudgesNames and used a lookup in my assignedby combobox to the judges table and sorted alphabetically. It seems to work the exact same as before
15 - I'm going to take a stab at setting up relationships based on the example you just showed me and I'll report back as the day goes on.

Again I can't say enough how thankful I am that you've taken this on as a project. I thought I would get a tip or 2 on this forum but I'm blown away by how invested you are and I feel like I'm getting a free education in Access DB design. So thankyou!

 

[jdraw]

Wow! So much work to do

1 - What do you mean f14 f15 fields?
When I look at Judges table design I see 153 fields - most start with F. see attached

2 - I'm not positive, probably because I was messing around deleting the private records before I sent a copy to you. I just checked on my version and a taskid IS created.
When I tested your database, I created 2 tasks.No taskId was assigned

3 - Yes, these are the same. In tblUser I stored the clerks and their respective regions. When they log in, that form is hidden but stays open. The combo box in that first login form is referencing the clerk - their region - their password. So when they create a new task, 2 of the combo boxes on that page are auto-populated with their name and region. Thus the clerk's name and region are always tied together.

I created a Region table just for Normalization.
What if the regions are renamed, subdivided, etc. You'll have a lot of changes to make. Simpler if normalized. Just a thought (it's attached).

4 - Casename is a textbox. The clerks work on SO many different case files throughout the year, I have to leave it open ended so they can distinguish what they worked on.

How do you find specific case after the fact? Perhaps it isn't important. But if there were note(s) made how would you find them?
5 - Date of record refers to the day that they worked on said task. ie most clerks enter hours on friday, and indicate which day they allocated which hours to during the week.

Ok so precision is not a key attribute. That starts to change a few things. So this isn't a daily time report sort of thing. So when the boss wants a report at any time, and clerks enter data after the fact (say friday), the reports are NOT accurate. They are an indication at best. This is by process, not necessarily by design. The process by which time is recorded is more adhoc than your database design.
6 - Yes, the clerks create a task record each day they work on a task. They had asked me if there was a way to store tasks and update them but I couldn't figure it out. They want to be able to "pull up" old tasks and add hours to them. ie If a clerk works on the same memo every day of the week they want to be able to enter "new task" once, create the info, and then copy and paste it 5 times in some fashion but update the hours worked for each day. The idea was suggested to me after I had built everything, so I don't really care to implement it.

This is a fact of life with systems and databases --requirements can change. The secret is to design structures that permit change to occur without having to recreate each time. If clerks need it, it should be on a specification or change./enhancement list. Clerks are the users, keep 'em happy; it'll pay back tenfold.
This is an example of where Normalization applies.
If the clerk is adding Notes to a Task (my guess at what this means), then your Task table allows for 1 note (255 chars text). Normalized you would have a 1 to many link from Task to TaskNotes. TaskNotes would probably include an HoursWorked and DateWorked. This is a design issue.
This highlights my point on a model that evolves by using various scenarios and ensuring the model supports the business, model isn't done until all "valid" scenarios can be accommodated.
7 - No, there is no region table.

8 - The fields in the judges table are ALL irrelevant except for first and last name. The table is copied from an excel spreadsheet and now that I think about it, I should have only imported 2 columns not the whole spreadsheet.

This is a sign of jumping in to Access with a plan. You are not the first, and won't be the last.
I think that explains all of the F fields. If you work from a model, you can rationalize/justify every attribute in the entity (field in the table).
9 - Judges can be assigned to various courthouses, they move as workload demands. Again the courthouse field is irrelevant to the data. All we need to know is which clerk from which region did which task for which judge.

So why is courthouse in the judges table? What do you need to record about courthouses, if anything?
10 - I just reimported the Judges excel table, got rid of all unnecessary columns, and added a primary key.

Good!
11 - Some of the subcategories overlap by nature and my ignorance of database design caused me to not plan for this. I can delete the records from January on Feb 1st as soon as I print records and then restructure the tables at that point, so I think I will do that going forward. I see what you mean, I should have put all of the subcategories in one table and linked them out from there. That way I wouldn't have duplicate data in my tables. I see now that I had to think this through more when setting up tables. In my head, it was logical that you can appeal a criminal, family, or civil case so appeal should be in all three tables, but it only confuses Access by doing so. I'll look into a restructuring of subcategories today on projectdev version. Also it would probably make more sense to the clerk and be less legwork for access. That way my subcategory combobox doesnt have to be cascaded based on the division combobox either.

True, there are some benefits from normalization.
Reduces redundancy and some customized tables here.
12 - I used JudgeID by chance when I added a PK to the judges table so that's a +1 for me.

Absolutely!
13 - I don't think I need to place courthouses etc in other tables because I just yanked all of those useless columns. Judges tables is now simply JudgeID (PK) | First | NAME

Why do you store last name in UpperCase?
14 - I removed the query for JudgesNames and used a lookup in my assignedby combobox to the judges table and sorted alphabetically. It seems to work the exact same as before

I'm still thinking about your data model, getting the facts identified to make the model match the business need.
15 - I'm going to take a stab at setting up relationships based on the example you just showed me and I'll report back as the day goes on.

Good, but don't change a whole bunch of forms etc that will cause other issues and workload.

Again I can't say enough how thankful I am that you've taken this on as a project. I thought I would get a tip or 2 on this forum but I'm blown away by how invested you are and I feel like I'm getting a free education in Access DB design. So thankyou!
You're welcome. No problem. It's encouraging to see/help someone who is learning and listening and questioning.

 

[spikepl]

You guys are paying attention to indexing, right? When I had a peek at the first db shown here nothing was indexed.

 

[jdraw]

Thanks spike -- yes we're getting there. There are a few structure issues and requirements to review/clarify. I think he/she has done a great job for a first data base.

 

[Badnapp]

Hey guys,

This weekend was pretty busy for me so I didn't get to do as much as I wanted, but I did make the changes mentioned. I'm a little bit confused now, as I'm sure you expected.

I made a copy of the database and tried to link the new table "tbltasks" to the forms that I have set up, but I basically made a mess of everything. By the end of the day, I had it so that when you clicked "New Task" you could create new tasks and they would show up in the "tblTasks" but they would not appear on the task list. I stayed for a couple of hours overtime but eventually I had to leave for the weekend.

This morning I am about to try again, but I might botch a copy or two until I get it right. As always I am open to any suggestions or tips. I have attached another .zip file for reference that is the current version of the db.

 

[Badnapp]

Update:

I changed all of the tables to *I think* normalize the data. The db also seems to be working correctly. I distributed a copy to one of the clerks to test it out. I'll report back when she gives me her results

 

[jdraw]

Please post the latest with the "normalized data". Did you see my previous responses? Did you consider moving the notes to a separate table?
How's the speed?

 

[Badnapp]

I wish I could give you a speed update. The clerk is too busy today so she didn't test it out. She says tomorrow she will give it a go so my fingers are crossed. I must have missed your note re: moving notes to a separate table because I did not do that.

I have attached the latest copy jdraw I'm hopeful that I'm progressing in the right direction. The table analyzer isn't picking up any errors now, for example; and everything seems to be functioning the same as before.