MS Access vs other tecnologies (1 Viewer)

[CJ_London]

not sure which of his functions you are referring to but sounds like that's a yes

 

[shadow9449]

not sure which of his functions you are referring to but sounds like that's a yes

Here she is:

http://www.allenbrowne.com/ser-32.html

There are other ways to do it but I'll give this a try.

 

[CJ_London]

that's the sort of thing I was think of.

 

[The_Doc_Man]

That would imply that the ACE/JET BE is intelligent enough to pass back just the requested row.

That's a definite YES, for the case of selecting a specific value based on an indexed field.

 

[static]

No. The engine's performance retrieving a specific record is far superior to finding it on a form.

Maybe I'm reading this wrong. 'Finding it on a form' is searching a loaded recordset. A recordset is basically a beefed up array. 'Retrieving' is loading from the disk. I'm pretty sure ram is faster than a disk.

 

[jdraw]

I posted a small timer class with test routine here that could handle various timings as referenced by CJ in post #37

Good luck with your tests. I look forward to your results.

 

[The_Doc_Man]

Static,

A loaded recordset might not be what you think it is. I believe that the recordset, when loaded, is loaded to working space in the FE file. You know how the FE file grows as you use it? That is called bloat. It occurs when temporary space is allocated in the FE to build a list of pointers for the records of a loaded recordset. The pointers to the entire set are is loaded to disk. That list is not resident in RAM.

You would have to use the resource monitor or task manager to prove it, along with the standard Windows Explorer. Basically, when you load a large recordset, user memory associated with your copy of MSACCESS.EXE doesn't go up by nearly as much as the size of the loaded records. But the size of the FE file goes up proportionately to the size of the loaded recordset. Access does it that way because it doesn't know ahead of time whether you are going to develop a 10-record set or a 1000-record set or a 100,000-record set. So it uses the FE file to hold the list of recordset pointers that will be used to retrieve your records. And if you have ever used the term "cursor" (in its database sense), the "index" to the currently selected record pointer in that list is where that cursor is pointing. (Access calls that a bookmark, by the way.)

 

[static]

If you open an ADO recordset in any other type of application there is nowhere to store the data except ram.
I don't see why DAO would be any different.

A recordset is an object/class returned by the database engine.
You can create a recordset in memory. In ADO you can open a recordset and disconnect it from its source.
In both cases pointers to storage wouldn't make any sense.
If it's a large recordset I would imagine it would swap to disk which is done automatically by the OS.

 

[shadow9449]

If I'm understanding the debate here correctly, we are discussing whether a form that is bound to a complete table loads all the records (or at least pointers/indexes) to RAM or to disk on the client computer where the FE is located. RAM, of course, is much faster than disk.

Either way, my contention was that once the entire table or pointers or indexes are loaded to the FE, it should not be faster to load a specific record over the network than it should be from ANYWHERE on the client's computer. This is even more true if there are multiple users accessing the database making requests from and writing to the back end which leads to temporary record locking and sharing.

SHADOW

 

[CJ_London]

RAM, of course, is much faster than disk.

depends what the task is and how big the task- RAM is not indexed, so finds in RAM are sequential, so to find a particular record can be slower.

 

[ducouer]

I think we need a poll here.
I would choose SQL, many advantages mentioned here

 

[Galaxiom]

I think we need a poll here.
I would choose SQL, many advantages mentioned here

SQL is a database language and, on its own, is not a substitute for Access which is a rapid application development platform.

 

[shadow9449]

Here is a demo I made using the different techniques we've been discussing for people to review or reference.

Some notes:

- The main point was to limit the rows returned during a search. I used CJ London's technique of originally basing the form on a query that returns no records, and then the recordsource gets updates when a name is selected from the search combo box. The add button simply resets the recordsource to the null recordsource
- By default, the form opens to a new record. You can have it open to the record of the client accessed most recently. I demonstrate this with a button on the main form. What happens is when you access a client, it adds it to a table. That way you can load up any client.
- I have two subforms on the main form. There is one on the client's main screen that gets its recordset loaded when a client is accessed. There is another on on a tab that gets loaded dynamically when you change to the tab. This is a demonstration of how to use tabs to further reduce how much data gets loaded during navigation
- Assuming tens of thousands of records, I rigged the combo box to only load the list once a character is entered. You can easily change this to only load after 2, 3, etc characters.

I did a bit of a twist on this. I was thinking that if a user clicked the arrow to drop down the list and saw nothing, they would probably (knowing my clients) go into panic mode. So I loaded it up with JUST the records they expect to see - clients with the last name starting with A. Even though odds are low (1/26 or so) that they happen to want a client with last name A, and even if they do they aren't going to scroll, I figured it was worth the small performance compromise to give them what they expect to see and hope they don't scroll past letter A

Anyway, anyone wanting to see if to comment or use some of the ideas in there is welcome.

SHADOW

 

[isladogs]

Hi

Just had a quick look at this without re-reading the entire (& now very lengthy) thread

Are you aware that selecting a name from the combo box gives error 2185?

 

[shadow9449]

Hi

Just had a quick look at this without re-reading the entire (& now very lengthy) thread

Are you aware that selecting a name from the combo box gives error 2185?

Thanks. That error is only when you don't type a name, but select from the initial A-list. I've revised it to handle that situation.

Here's a list of test clients who are searchable. I realize that unless you know the name of the client, you can't test this demo:


Brown Lisa
Smith John
Wilson Peter
Jones Jeff
Johnson Andrea
White Jennifer
Andrews Walter

 

[isladogs]

Well OK it works - if you know which letters will give a hit....

I did a bit of a twist on this. I was thinking that if a user clicked the arrow to drop down the list and saw nothing, they would probably (knowing my clients) go into panic mode. So I loaded it up with JUST the records they expect to see - clients with the last name starting with A. Even though odds are low (1/26 or so) that they happen to want a client with last name A, and even if they do they aren't going to scroll, I figured it was worth the small performance compromise to give them what they expect to see and hope they don't scroll past letter A

In my experience, users will always do what you don't want them to do.
Always plan for things to go wrong!

Personally I think they might find it confusing only seeing letter A.
Not sure how long your list is in reality but I wonder whether this approach is really necessary.

I have a similar search box for a schools database where there are up to 2000 students. Individual students can be searched by last name or first name

Users type in the first few letters of the last name in a combo box (or forename in a separate combo) and it filters the list accordingly.
This means if you type 'Smi' you won't get all students starting with S but just those starting with Smi.... (Smith / Smithers / Smithson etc)

Even if you had 20,000 names, that approach would still work well

 

[CJ_London]

I did a bit of a twist on this. I was thinking that if a user clicked the arrow to drop down the list and saw nothing, they would probably (knowing my clients) go into panic mode.

another alternative is to make the default value for the combo 0 and as your initial rowsource, something like

SELECT 0, "Type Something" FROM myTable

 

[shadow9449]

another alternative is to make the default value for the combo 0 and as your initial rowsource, something like

SELECT 0, "Type Something" FROM myTable

I really like that idea!

So is myTable an actual table..? Does it have any data in it? How is that done?

 

[CJ_London]

mytable needs to be a real table. Unfortunately it won't return anything if mytable is not populated with any records - and you have to delete the words before entering anything. But since no real fields are being selected, it could be any table - such as MSysObjects which is guaranteed to have some records.

I use it as the rowsource of the combo (i.e. rowsource does not reference a query)

then the change event changes the rowsource to

SELECT ID, Name from myTable WHERE Name Like '" & myCombo.text & "*'" ORDER BY Name.

I tried playing around with the combo control format property (which you can use to display a message when the value is null) but with limited success.

 

[shadow9449]

mytable needs to be a real table. Unfortunately it won't return anything if mytable is not populated with any records - and you have to delete the words before entering anything. But since no real fields are being selected, it could be any table - such as MSysObjects which is guaranteed to have some records.

I use it as the rowsource of the combo (i.e. rowsource does not reference a query)

then the change event changes the rowsource to

SELECT ID, Name from myTable WHERE Name Like '" & myCombo.text & "*'" ORDER BY Name.

I tried playing around with the combo control format property (which you can use to display a message when the value is null) but with limited success.

Ok how about this...