MS Access vs other tecnologies (1 Viewer)

[shadow9449]

access does not pull all the data across the network then apply the criteria. The ACE/JET/SQL Server engine does that work before returning the reduced results.

However filters are another matter - they work locally on the dataset that has already been pulled across the network.

Ok so just to reiterate:

In a query where the last name criteria is "Like AB*", If the user types "AB", access will then only fetch clients whose name start with AB over the network? If so, that makes sense that there will be a huge speed and network-friendly advantage.

 

[CJ_London]

that is correct

 

[shadow9449]

Awesome! Thanks, CJ!

I made a lot of changes to my application and had users test it and they have been seeing real speed improvements. I'll post details later as to what I did so people who are interested might get a few tips.

 

[shadow9449]

I'll step in with a simple compromise for the question about

DB = CurrentDB

The way I did this was to have a general module where I kept some common utility routines and variables that were used by all of my forms. Then I put the DB variable, which I had declared as a DAO.Database just to avoid any confusion, in the general module as a public variable.
.

I've implemented this technique. It's hard to determine if on its own it helps with performance but it does sound logical.

I've been running into a problem, though.

- When I'm developing the app, I enter using the good old shift key so I can get into the program. The DB variable doesn't initialize as its initialization is done in the opening form which was bypassed. To make things simple I created a form and put into its opening code the initialization so I have to load the form every time I want to work on my project. However, what seems to happen is that every time I get an error message (which happens a lot when I program ) the variable goes out of scope so I have to keep opening the initializer form. Is there a better way?

- In a similar vein but more concerning to me: any good programmer has proper error handling routines in all (or almost all) blocks of code that properly issue an error message and exit the Sub rather than resulting in a runtime error. I am not sure, but I would speculate that if one of these errors is tripped, that the DB declaration would go out of scope and the user won't be able to use much of the program without restarting.

As I said, I'm probably missing something.

Thanks again!

SHADOW

 

[CJ_London]

I do pretty much the same, but I have a public function to run sql - something along the lines of

public db a dao.database

function getRecordset(sqlstr as string) as dao.recordset

if db is nothing then set db=currentdb
set getrecordset=db.openrecordset(sqlstr)

end function

 

[shadow9449]

I do pretty much the same, but I have a public function to run sql - something along the lines of

public db a dao.database

function getRecordset(sqlstr as string) as dao.recordset

if db is nothing then set db=currentdb
set getrecordset=db.openrecordset(sqlstr)

end function

That's actually a really good idea. That way I never have to worry about db going out of scope or forcing the initialization.

Thanks a lot!

SHADOW

 

[CJ_London]

I do most of my initialisations that way, including a login call for form opening which is where it normally gets called - as you say it is a pain if they are all initialised in the opening form and you are working deep down on another

 

[shadow9449]

Does anyone have an opinion if there is an advantage of db=currentdb over db = DBEngine(0)(0) or the other way around?

SHADOW

 

[CJ_London]

so far as I am aware there is no advantage - currentdb is DBEngine(0)(0)

 

[Galaxiom]

However, what seems to happen is that every time I get an error message (which happens a lot when I program ) the variable goes out of scope ...

That is a common misconception among developers. Errors do not cause variables to be reset or go out of scope. This happens when you click the End button in the dialog. In fact, by default, VBA warns that will happen at that point, not before.

Instead, you can correct or bypass the error and press the Go button.

- In a similar vein but more concerning to me: any good programmer has proper error handling routines in all (or almost all) blocks of code that properly issue an error message and exit the Sub rather than resulting in a runtime error.

Another common myth. Error handling is vastly overused by some developers, so much so that it becomes hard to see the code among the error handling.

On encountering an error in a procedure without handling, the error is passed upwards through the chain of calls until it encounters an error handler. Only when no handler is encountered anywhere in the chain will the program Break.

Consequently it is often adequate or even desirable to just put error handling in the top procedures of the major execution pathways. In some cases it will provide less precise detail about exactly where the problem occurred but usually enough to point in the right direction.

However, where a common procedure is called from multiple execution paths, having error handling on the common procedure can be a disadvantage because the error handler message can't indicate where the call to it had originally come from. Letting the error pass back up to the calling procedure will show where the problem actually started.

Traditional error handling in functions called from queries is generally a really bad idea. The error is displayed and the function exits, only to be called again for the next record in what looks to the user like an endless fault loop. Far better to return an out of scope value from the function or simply "Error" to be displayed in the query results.

I am not sure, but I would speculate that if one of these errors is tripped, that the DB declaration would go out of scope and the user won't be able to use much of the program without restarting.

No. See first comment above.

As I said, I'm probably missing something.

Yes.

 

[Galaxiom]

so far as I am aware there is no advantage - currentdb is DBEngine(0)(0)

No they are not quite the same.

CurrentDB automatically refreshes the Collections so includes any new query or table objects created since the start of the session. DBEngine(0)(0) returns the originally initialised collections which must be first refreshed to see anything added since starting.

CurrentDb returns a copy of the structure while DBEngine(0)(0) returns the database internal pointer. Consequently, DBEngine(0)(0) is much faster, but the difference is trivial in well constructed code.

Under rare circumstances, DBEngine(0)(0) can return a pointer to a library database instead of the main database. CurrentDb always returns the main database.

Generally, CurrentDb should be preferred unless the developer comprehends the implications of these differences.

 

[shadow9449]

No they are not quite the same.

CurrentDB automatically refreshes the Collections so includes any new query or table objects created since the start of the session. DBEngine(0)(0) returns the originally initialised collections which must be first refreshed to see anything added since starting.

CurrentDb returns a copy of the structure while DBEngine(0)(0) returns the database internal pointer. Consequently, DBEngine(0)(0) is much faster, but the difference is trivial in well constructed code.

Under rare circumstances, DBEngine(0)(0) can return a pointer to a library database instead of the main database. CurrentDb always returns the main database.

Generally, CurrentDb should be preferred unless the developer comprehends the implications of these differences.

Actually, I comprehend almost nothing of this post I wouldn't even know what background information to start reading in order to make sense of it!

I'll stick with declaring db = CurrentDB when the program loads to set my global variable based on your final remark, even though you said that DBEngine(0)(0) is much faster.

Thanks!

 

[shadow9449]

That is a common misconception among developers. Errors do not cause variables to be reset or go out of scope. This happens when you click the End button in the dialog. In fact, by default, VBA warns that will happen at that point, not before.

Instead, you can correct or bypass the error and press the Go button.

I forced an error to see what you mean and the choices are "End", "Debug" and "Help". What do you mean by bypass the error and press Go?

SHADOW

 

[CJ_London]

I think Galaxiom means the run option/the green triangle in the VBA window or press F5

Bypass the error - comment it out/fix the error

e.g. you get an error as you described a few posts back- db not initialised (can't remember the exact message)

Select Debug - line will be highlighted

at the start of that line put

set db=currentdb

and press return to move the errored code to the next line

click on the green triangle (or press F5 or F8 to see if the code steps through this time) and you are off again

Problems can be if you are referencing something like screen.activecontrol - it will no longer be active

 

[shadow9449]

I think Galaxiom means the run option/the green triangle in the VBA window or press F5

Bypass the error - comment it out/fix the error

e.g. you get an error as you described a few posts back- db not initialised (can't remember the exact message)

Select Debug - line will be highlighted

at the start of that line put

set db=currentdb

and press return to move the errored code to the next line

click on the green triangle (or press F5 or F8 to see if the code steps through this time) and you are off again

Problems can be if you are referencing something like screen.activecontrol - it will no longer be active

Ahh....interesting. I didn't get that from the original message. I tried it and it works.

Thank you

 

[CJ_London]

just remember to take that line out again when finished testing!

 

[shadow9449]

just remember to take that line out again when finished testing!

Well, the way I tested it was by simply creating a new form with a button that has the code:

dim i as integer

i = "foobar"

And then run it, to force a Type Mismatch. I clicked Debug, changed the line to i = 5 and then the arrow and it continued just fine.

 

[Galaxiom]

e.g. you get an error as you described a few posts back- db not initialised (can't remember the exact message)

Select Debug - line will be highlighted

at the start of that line put

set db=currentdb

and press return to move the errored code to the next line

click on the green triangle (or press F5 or F8 to see if the code steps through this time) and you are off again

This should not be necessary to add the line if a variable has been Set to CurrentDb and is still in scope. The variable should continue to hold that value provided End has not been clicked in the Break dialog or the Reset button (red square) pressed in the UI.

Errors do not clear the variables.