UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Database Design Fe And Be, Access 2016    
 
   
josemontesjr
post Nov 18 2019, 12:40 PM
Post#1



Posts: 8
Joined: 25-August 06



Background: I recently acquired the administration role of a MS Access database; have been upgrading/update/redesigning etc.

Users: approximately 10-12.

Backend: stored on a shared network drive.

Frontend: stored on users desktop.

Current Design: FE finds a BE record and SQL (DoCmd.RunSQL) will move the record from the BE to the FE (temp table) for the user to edit. Once the user is finished with the record, SQL (DoCmd.SQL) will move the record from the FE (temp table) to the BE. This process keeps the BE from crashing and/or becoming unstable.

Result: have not had any issues.

Question: is this the correct way to run a FE / BE multiuser environment? is there an easier / correct way to do this?

Thanks you for your time and responses.

Jose

--------------------
Jose
Go to the top of the page
 
theDBguy
post Nov 18 2019, 12:43 PM
Post#2


UA Moderator
Posts: 76,906
Joined: 19-June 07
From: SunnySandyEggo


Hi Jose. Welcome to UtterAccess! welcome2UA.gif

First impression is "no," editing a record shouldn't be that complicated. How come you said editing the record directly from the BE makes the BE unstable?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Nov 18 2019, 12:44 PM
Post#3


UA Admin
Posts: 36,208
Joined: 20-June 02
From: Newcastle, WA


It depends on circumstances, but that does seem like it might be more complex to do than necessary.

Why do you want to copy the records back and forth to edit them locally? Why do you NOT want to edit the records in place in the source, back end, table?

Although I would expect this to work, I'm just not sure it's worth the extra network traffic required.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Nov 18 2019, 12:55 PM
Post#4


UtterAccess VIP
Posts: 11,300
Joined: 10-February 04
From: South Charleston, WV


What purpose does the temp table serve? I understand that you might want to defeat the immediate update of Access by not using a form bound to the permanent table. But you could just use unbound controls and when the user clicks save, update the permanent table from the values in the controls. Why the temp table?

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Nov 18 2019, 01:01 PM
Post#5


UA Admin
Posts: 36,208
Joined: 20-June 02
From: Newcastle, WA


"... Once the user is finished with the record, SQL (DoCmd.SQL) will move the record from the FE (temp table) to the BE. This process keeps the BE from crashing and/or becoming unstable."

I think, perhaps, the underlying assumption might be that you avoid potential conflicts or locking when you do this, but I see it the opposite, plus it introduces an additional potential problem.

First, you have to LOCK the record TWICE, not ONCE. First while you copy it back locally, and again when you copy the edited record back to the server. That doubles the potential opportunities for conflicts with other users.

Second, if you want to prevent conflicts with two people trying to edit the same record at the same time, yes, this would minimize that. However, it slips in a NEW problem.

User A grabs the original record and moves it back to their computer.

While User A is fixing up that record locally, User B decides to change that same record, so User B gets another copy of the original and moves it back to their computer.

Meanwhile, User A gets interrupted and doesn't finish their changes until 10 minutes later. But User B, being fast and efficient, finishes THEIR local changes to that record and replaces the original record on the server with their "User B" version.

But, then, when User A gets back to work and makes their own changes to the record a few minutes later, they copy their version back over the top of User B's changed version, and now User B is confused because their work is lost.

I think that's the biggest danger I see. But this kind of asynchronize processing probably introduces other, subtle, problems even while it potentially reduces others.
This post has been edited by GroverParkGeorge: Nov 18 2019, 01:02 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
josemontesjr
post Nov 18 2019, 01:44 PM
Post#6



Posts: 8
Joined: 25-August 06



Thank you everyone for your responses. They are greatly appreciated.

When user A "grabs" a record, it moves (delete it from the BE and copy it to the FE) the record for editing; if user B wants the same file, they receive a message that it is in use and to try again later.

Several reasons I went this route of using a local temp table;
1. When users had the old FE opened, it automatically opened/connected to the BE (the little icon that shows the BE is in use) table A.
a. this caused the BE to become unstable and would crash when others tried to edit table A.
b. some users would leave their old FE opened for extended periods of time; sometimes even keeping table A opened for hours!
2. When users would add new records using the old FE, often in the mornings, to table A the BE would become unstable and crash.

This method has eliminated any unstable crashes.

I'm sure there is a better more standard way of getting this accomplished.

Not sure what that is.

Thank you for your time.
This post has been edited by josemontesjr: Nov 18 2019, 01:46 PM

--------------------
Jose
Go to the top of the page
 
BruceM
post Nov 18 2019, 01:53 PM
Post#7


UtterAccess VIP
Posts: 7,993
Joined: 24-May 10
From: Downeast Maine


Are you saying users are working directly with the tables? If so, that is likely to be at least part of the problem. Tables should not be exposed directly to users, who should be using forms to add or edit data, and reports to print or view read-only output.

Go to the top of the page
 
josemontesjr
post Nov 18 2019, 02:05 PM
Post#8



Posts: 8
Joined: 25-August 06



In the old and new FE, users never see actual tables. They see only forms.

In the old FE Users edit/add/etc. BE table A (linked) via the use of FE forms.

The new FE uses the temp tables, that "move" records via append/delete from BE table A to FE temp table A using DoCmd.RunSQL on FE forms.

How should users be updating/accessing the BE table A through the FE forms?


Thank you for your time.

--------------------
Jose
Go to the top of the page
 
theDBguy
post Nov 18 2019, 02:11 PM
Post#9


UA Moderator
Posts: 76,906
Joined: 19-June 07
From: SunnySandyEggo


Hi Jose. Since you asked what is the "correct way" to run a FE/BE in a multi-user environment, I would just say what you're doing is "not the correct way" of doing it. However, if this is the "only" way to avoid the BE crashes you were experiencing, then I guess for your situation, it "could be the correct" way. It's just I think you were only avoiding the real problem. What was really causing the BE to crash? Once you figure that out and fix it, then you can go back to the "real correct way" of running a multi-user database. Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
AlbertKallal
post Nov 18 2019, 02:28 PM
Post#10


UtterAccess VIP
Posts: 2,903
Joined: 12-April 07
From: Edmonton, Alberta Canada


My experience is such a setup is likely to cause more problems.

QUOTE
delete it from the BE and copy it to the FE



Yikes! You mean the user now exits access, or his computer stops, the record is deleted? That does not sound like a very good setup. In other words, if anything goes wrong with the FE, you now have a missing and deleted record in the BE?

I would be rather surprised that this setup is more stable, but deleting a record in the BE and “hoping” that the record makes a trip back to the BE does not sound like a more robust and reliable setup here. I can’t see how this is not going to cause valuable records and data to be lost.

I suppose if this setup works, then so be it, but it certainly something I would not attempt. Worse, is features like RI and cascade deletes etc. becomes VERY difficult to deal with? In other words, what will stop you from adding child records when no parent record exists? And if you adding a new record, then the PK generated in the front end is going to be different then the PK that gets generated in the back end.

For all but the most simple table designs, this approach might be workable, but as an application grows in complexity, the issues that crop up in regards to working with related data is going to be a significant challenge, and likely a source of many problems and issues that will be more work and efforts saved by any possible corruption issue.

I have several clients in the 3-6 user range, and these applications are highly related, tons of forms and code. Such clients have not experienced a corruption once, and we are talking in excess of 15 years of constant daily use.

QUOTE
How should users be updating/accessing the BE table A through the FE forms?


Linked tables, and forms bound directly to the linked tables. When you launch a form, the main form should only load the one record that the user asked to see.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
josemontesjr
post Nov 18 2019, 02:36 PM
Post#11



Posts: 8
Joined: 25-August 06



Thanks for the feedback; I was sure there was an easier/correct way of doing business.

The "live" database is in constant use, figuring out why it was crashing was difficult given that users are busy working on other things than being able to help me analyze what was causing the issue.

I figured it was an issue with losing connection to the BE over the shared network, but I'm not savvy enough to figure it out and could not find anything online.

This was the next best choice, option that worked.

Thanks for the feedback once again; I'm thinking that it has also something to do with not using recordsets properly.

--------------------
Jose
Go to the top of the page
 
josemontesjr
post Nov 18 2019, 02:46 PM
Post#12



Posts: 8
Joined: 25-August 06



"Linked tables, and forms bound directly to the linked tables. When you launch a form, the main form should only load the one record that the user asked to see."

BE table is linked; the issue I see is that the old FE would load the entire BE table A (Record Source) instead of loading just one record.

Question; FE form was loading all records from BE table A (for every user) instead of loading just one record; how is this accomplished?

Sorry for all the questions, but I do greatly appreciate your time.
This post has been edited by josemontesjr: Nov 18 2019, 02:49 PM

--------------------
Jose
Go to the top of the page
 
josemontesjr
post Nov 18 2019, 04:28 PM
Post#13



Posts: 8
Joined: 25-August 06



Question: does opening a BE query/table via the FE form using recordset (with all its features) effect a multiuser environment? hopefully prevent crashes.

I opened a recordset via the FE form, but it shows as open on the BE until I close the form; would other users be affected?

I'm using a test database to experiment and get this done the correct way.
This post has been edited by josemontesjr: Nov 18 2019, 04:30 PM

--------------------
Jose
Go to the top of the page
 
dmhzx
post Nov 18 2019, 04:43 PM
Post#14



Posts: 7,115
Joined: 22-December 10
From: England


How do you get a unique record identfier?

Most people use an autonumber, and the way you say you're doing things, would mess up the autonumbers, and lose any links to other tables.

Go to the top of the page
 
theDBguy
post Nov 18 2019, 04:51 PM
Post#15


UA Moderator
Posts: 76,906
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (josemontesjr)
Question: does opening a BE query/table via the FE form using recordset (with all its features) effect a multiuser environment? hopefully prevent crashes.

I opened a recordset via the FE form, but it shows as open on the BE until I close the form; would other users be affected?

I'm using a test database to experiment and get this done the correct way.

Hi Jose. Keeping an open connection to the BE is actually one recommended technique to "speed up" the application. Access was built for a multiuser environment, so having multiple users with open connection to the BE is normal.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
josemontesjr
post Nov 18 2019, 05:54 PM
Post#16



Posts: 8
Joined: 25-August 06



Goal: Trying to correct my database to the kosher way of doing business without the BE becoming unstable in the live system.

Test Code (testing database):

Private Sub cmdFind_Click()

Dim db as Database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("FY2016-FY2020", dbOpenDynaset, dbSeeChanges)

With rs
.FindFirst "ANumber = '" & Me.txtANumber & "'"
Me.txtTest = .Fields("LastName")
End With

Set rs = Nothing

End Sub


Result: with Set rs = nothing, it closes the connection once it is completed (locked icon on BE is gone), as a matter of fact, I don't even see it come on! over the share drive.

Result 2; by deleting the Set rs = nothing, the locked icon remains connected.

Question: What is the correct way to accomplish this?

Note: the test code works.

--------------------
Jose
Go to the top of the page
 
theDBguy
post Nov 18 2019, 05:57 PM
Post#17


UA Moderator
Posts: 76,906
Joined: 19-June 07
From: SunnySandyEggo


Accomplish what? Like I said earlier, leaving an open connection is a well-accepted approach to make sure the FE is responsive. In other words, creating a recordset object and not setting it to Nothing until just before the user quits Access is a recommended "best practice." However, you can just open a recordset against a small table. It doesn't have to be the main table.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Nov 18 2019, 06:31 PM
Post#18


UtterAccess VIP
Posts: 11,300
Joined: 10-February 04
From: South Charleston, WV


As has been said, the usual way is just to link the FE to the BE. And such a setup does not ordinarily crash.

--------------------
Robert Crouser
Go to the top of the page
 
AlbertKallal
post Nov 18 2019, 06:51 PM
Post#19


UtterAccess VIP
Posts: 2,903
Joined: 12-April 07
From: Edmonton, Alberta Canada


Access does not pull the whole table over the network.

Assume you have an invoicing system. Say we have 500,000 invoices.

And assume we have a nice invoice form (likely a sub form to show details).

When you launch that form, you would not JUST launch the form, and then have the user hit ctrl-f to search 500,000 rows of data for one record. In fact, think of using google, or just about any web system, or just about any desktop software. You don’t load the WHOLE internet into your web browser and THEN hit ctrl-f to search the web page.

What occurs is you always “ask” what the user wants first. After all, you can’t edit one invoice out of 500,000 invoices.

So you simple ask what invoice the user wants to work on.

The code could be as simple as this:

CODE
   Dim strInvoice     As String
  
   strInvoice = InputBox("Enter invoice number to edit")
  
   If strInvoice = "" Then Exit Sub ' user cancled out
  
   DoCmd.OpenForm "frmInvoice", , , "InvoiceNum = " & strInvoice


So the above code “askes” the user what invoice to work on, and then opens the form to the ONE invoice.

Now, despite the form being bound directly to a linked table of 500,000 rows? No query, no SQL. Just a plane Jane linked table (to the back end on the shared server folder).

Access will ONLY PULL the one record down the network pipe. This is the case for an Access accDB back end, and is ALSO the case if your backend is SQL server.

So, access does not pull the whole table. Now on the other hand if someone is silly enough to launch a form bound to a large table without a “where” clause (not to be confused with filter), then Access is really only doing what you asking to do (load a form up with a huge number of records).

So, no, access does not pull the whole table. How access works as 100% standalone on your desktop, or over the network is 100% the same. When you launch Access, it does not out of the blue now start reading all data and tables. You find even in an un-split standalone desktop application, that launching your application that has a table with 1 row, or 1 million rows takes the SAME TIME to load!

The reason why Access is so much better then say word, or excel? Those systems “always” load the whole document.

If access always loaded the whole table, then pulling one record out of a large table would take forever. Not only would this be slow, but such a system would quite much prevent multi-user operations.

So while two people editing a word doc is full of problems, access works different. It does not load all the data from tables (unless you are silly enough to tell access to do that).

Access ONLY pulls the rows of data from the disk that you ask it to. So, how access works as 100% local (split or un-split), or over network is EXACTLY the same. Access by using of high speed indexing thus will ONLY pull the one record. This is true for 100% local, or that of being on a network. How access pulls one record from a local table, and that of a BE on a network is 100% identical in both cases! (It askes the disk drive to pull this bit from the file – that file can be local, or on a network. The ONLY thing the network does is provide a long extension cord to the disk drive.

Access is a row based data system, and not a document based system like word or Excel. (Those systems work on whole documents – access does not).

Now of course if your where clause can’t use an index, then a full table scan WILL occur, and in that case, then records from the back end will flow into the front end until such time a match is made (and in most cases finish the table scan).

Now of course, a user cannot see, use, edit, update ANY information in your application without FIRST finding that row of data to work on.

What this means is that the ability of your application to EASY find an invoice (or whatever) should be the MOST optimized part of your application. And in this context, when I say optimized don’t mean performance, but the user interface.

A user in near any typical access application will spend the WHOLE day FIRST searching for something to edit.

So, one they done the search, then you present the results of that search (like every search you do, be it the web, or any desktop software). You the let the user launch the form to edit data, and when done, they close the form (your data is saved), and they are right back to the search form ready to do battle with the next customer or task at hand. This basic loop or design pattern is the bases of EVERY single bit of software I have EVER used on a computer.

So, in place of some lame input box, you build a search form say like this:



Or say this:



The user has typed in a few charters, and the matches are thus displayed in some type form (a grid). Again, like google, like accounting packages, like invoicing systems or whatever? The user THEN picks one of the results.

In the above screen shot, with 4-8 users, response time is near instant but there are 200,000+ rows of customers. Now the search form in this example does use a SQL query. So, sure, in some cases you use some SQL to stuff into the form in question, but the "where" clause and a bound + linked table is also just fine.

So, we simple stuff in the SQL into the search form.

Eg:
CODE
   Dim strSQL as string
   strSQL = "Select * from tblTours where LastName Like '" & _
     me.txtName & "*'"

   me.RecordSource = strSQL


So, I don’t use a where clause for the search form, because it stays open all day long, and the user works from that from all day long.
(and you cant use a wild card filter on both sides - it must be TRAILING only, or a = and a exact match for indexing to work and be used. (if you don't do this, then full table scans and full pulls of the table data WILL occur).

Now that we have the “pick list”, the user can easy see if the customer is in the system. And then they click on it.

The code beind the glasses icon in above is thus this:
CODE
   DoCmd.OpenFrom "frmTours", , , "TourID = " & Me!ID


Again, since we using a “where” clause, then we only going to pull the one record down the network pipe (well, ok any sub forms will pull their records also, but again, access will ONLY pull down the records to fill the sub form – not the whole table).

There is another VERY significant bonus of presenting users with a VERY nice and easy to use search screen. It simply encourages users to search first. If your system has a bad UI, then users will often just start adding a user that is ALREADY in the system, because you’re UI does not encourage VERY easy and VERY nice searching. So if you build in a “social” engineering designs that promotes easy searching, then users will search first. You will reduce HUGE amounts of duplicate customers or whatever with such a design encouragement.

That search screen should likely fully function with 100% keyboard, and not require the mouse to do MULTIPLE searches. In other words, if I search for an invoice, or say a customer fails, and it not in the system, then I should be able to type away AGAIN to try an additional search. Let the user re-fire, re-try with GREAT ease.

And you note, that if the user does NOT find a match, then near 99% of such search screens should have a “add new” button. Thus every single process and how your application is 100% consistent. So you might be searching for an invoice, but you might also want to search for a customer. So every part of your system will:

Have a nice easy to use search screen.
(Because users likely are to spend their WHOLE day in that screen).
Once the customer, part or whatever is found? They click on a match, edit, and then they close the form. This ensures that the data is safe written back to the table and thus if the workstation stops, then the data is saved. This also tends to give the user a “sense” of having completed a task.

And it also means that if a customer phones back in 10 minutes, and a different service rep using your application picks up the phone, then they can search for that customer, and it will be in the system. (and not locked by you anymore).

If you had launched a form bound to a large table, not only does this pull huge amounts of records over the network, but NEW records don’t appear in that form until such time you re-load the form, or force a re-query. So this design pattern of searching first, and ONLY editing the ONE record in the form also promotes a VERY good mutli-user model. You never have to re-query, or write code to “re-load” the datasets in the form, since you only every loading up the main form to one record.

The above is the “mother” of all design patterns, and I can’t recall having used ANY software in 20 or more years that does not work as above. Yet, for some reason, Access developers often ignore how every other bit of software they EVER used works. Never could grasp why such people single out why so many using Access decide to do things different in this regards.

In closing:
Not sure where this “myth” came from that Access pulls the whole table. It does not. But then again, if users are opening forms attached to the large table, the access is going to start pulling all those records. But that’s not the fault of access, but the developer who’s telling access to pull all that data.

So, be it an accDB back end, or a server based one (SQL server, Oracle, MySQL etc.), access will ONLY pull the record(s) into that form that you told it to pull.

As a result, even a table of 500,000 rows or more should load in a flash and instant, because the time to pull one row out of 500,000 rows is something that Access is VERY good at doing.

You can read my “notes” on this concept of searching, but it really the same process you use in any software system, and is really nothing new to anyone here:

Searching in Access:
http://www.kallal.ca/Search/index.html



Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2019 - 10:47 AM