Tip: "Why must Access crash verses use the error handler?" (1 Viewer)

M

mdlueck

Sr. Application Developer
Local time
Today, 12:05
Joined
Jun 23, 2011
Messages
2,631
Greetings All,

I thought to share a debugging suggestion based on a finding just now, and ones I can recall as well.

In my Form Open event, I call to the business logic which the Requery button also is connected to. This functionality empties the FE temp table, then issues a pair of DAO queries to download records from the SQL BE to the FE temp table.

Suddenly doing so would totally crash Access and the box would come up offering to clean up the DB and reopen. hhhhmmmm..... :cool:

I went through the process of manually running each query interactively rather than through code. That means commenting the Execute of the queries, and also the deletion of the DAO.Querydef objects.

Then running one query interactively told me "You dummy, you have one more column selected than is in the FE temp table." and indeed I had forgotten to update the FE temp table schema with a column I had realized got missed.

"Why O why, MS, could you not handle that error gracefully via VBA automation!?!?!"

Likewise, I have code which populates the ODBC connection string into Linked Table objects. Suddenly that started crashing Access the same way. Turns out, I had linked a test table which I later deleted on the server. Finding no table to match the Linked Table object, Access decided best to crash on the spot. I deleted the Linked Table object, and that cleared up the crashing that time.

So, good as error trapping / handling is, there ARE times that MS will decide to just crash anyway and leave you scratching your head as to what got into Access. "You have been warned!" (This message will be destructed by an organization headquartered in Redmond, WA! ;) )
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 17:05
Joined
Sep 12, 2006
Messages
15,680
you might as well say, if a compiler can detect an "easy" error in your logic - eg end sub, rather than end function - why doesn't it fix it automatically

it just doesn't, that's all.

it provides lots of facilities to help developers not get these sort of problems - eg in your case, do a belt and braces check. check that every column in the input table is present in the destination table.
 
G

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:05
Joined
Jan 20, 2009
Messages
12,854
It is not practical for any program to cater for all forms of sloppy programming.
 
M

mdlueck

Sr. Application Developer
Local time
Today, 12:05
Joined
Jun 23, 2011
Messages
2,631
jdraw said:
You mention error handling, but it isn't clear, to me at least, that you actually had an Error handling routine in your code
Click to expand...

Yes I have developed my own generic error handling routine which I place in most functions / subroutines. The exception is for methods which just call another method, such as a button calling directly to shared code.

It generates for me a standardized logging format, sample as follows:
Code: 
Date: 20111010 Time: 13:21:56 UserID: c_mlueck
AppErrorMsg: Class: clsObjPartsTbl, Function: Update()
Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Number: -2147217900
Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function 'clsObjPartsTbl_Update' expects parameter '@quotespoprice', which was not supplied.
MessageText: Error not found.

Date: 20111011 Time: 13:30:32 UserID: c_mlueck
AppErrorMsg: Form: Form_partsedit, Subroutine: btnPickBuyerCode_Click()
Error Source: Fandango_FE
Error Number: 7782
Error Description: You can't create a new instance of this form or report while it is in Design view.
MessageText: Error not found.

Date: 20111012 Time: 09:24:51 UserID: c_mlueck
AppErrorMsg: Form: Form_partsmfgmethselect, Subroutine: Requery_Click()
Error Source: Fandango_FE
Error Number: 2580
Error Description: The record source 'frmqryclsObjPartsBuyerCodeTypeTbl_RefreshLocalTmpTbl' specified on this form or report does not exist. 
MessageText: Error not found.
 
M

mdlueck

Sr. Application Developer
Local time
Today, 12:05
Joined
Jun 23, 2011
Messages
2,631
gemma-the-husky said:
check that every column in the input table is present in the destination table.
Click to expand...

Yes of course... just that it becomes extremely tedious to make sure when adding a column to the various queries in a class pertaining to said table that all queries get updated.

For my multiple record forms, there are three queries involved... two DAO.Querydef objects to download records to the FE temp table, then another Querydef to select records from the FE temp table to display in the form.

This time I happened to catch all of the queries and missed adding the col to the FE temp table.
 
G

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:05
Joined
Jan 20, 2009
Messages
12,854
BTW I recommend against having temporary tables in the Front End.

Google the forum for "Side End" if you are interested in the discussion.
 
M

mdlueck

Sr. Application Developer
Local time
Today, 12:05
Joined
Jun 23, 2011
Messages
2,631
Galaxiom said:
Google the forum for "Side End" if you are interested in the discussion.
Click to expand...

I basically have that, all in the FE DB.

I have a desktop shortcut for the applicaiton which runs a .CMD script located on the fileserver. That script checks for the existance of a directory in the user profile "Local Settings\Application Data\CompanyName\ProgramName". Then it copies a fresh copy of the FE DB there, and finally launches the DB and exits.

So each time the app is started, a fresh copy of the FE DB is deployed to the node. Bye bye distributing client updates. Bye bye worries of the FE DB becoming corrupted.
 
G

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:05
Joined
Jan 20, 2009
Messages
12,854
Corruption is not an issue if you never write to the FE. Using a disposable SideEnd is very simple.

I use AutoFEUpdater which is just a fancy way to do much the same as your script. It checks a log for the time the existing FE was copied and only copies again if the master is newer. Saves a little bit of time and traffic copying the FE unnecessarily. Setting up the desktop shortcut automatically is also a nice touch.

The version I use is before Tony started charging for it but there is also Bob Larson's free tool. Surely nobody distributes client updates manually?
 
M

mdlueck

Sr. Application Developer
Local time
Today, 12:05
Joined
Jun 23, 2011
Messages
2,631
Galaxiom said:
It checks a log for the time the existing FE was copied and only copies again if the master is newer. Saves a little bit of time and traffic copying the FE unnecessarily.
Click to expand...

A valid thought, Galaxiom.

I will consider splitting my FE into an FE + SE if I can find a reliable means to only copy the FE if it has changed on the server. RoboCopy does not seem to be a standard part of this company's preload.

OTOH, merely moving from a file server shared database file to a cached local FE DB with SQL BE is a wow of a performance improvement already!

The network here seems to be having a slow start to the new week. Seemingly simple Access operations were taking my machine hostage for 15 seconds. I copied my FE DB to my local workstation and I am seeing easily a 10X performance improvement. Likewise with production use of this application.

So with that much of an improvement already, a couple of seconds to copy the FE to the local workstation as the application starts is acceptable.
 
M

mdlueck

Sr. Application Developer
Local time
Today, 12:05
Joined
Jun 23, 2011
Messages
2,631
Galaxiom said:
Using a disposable SideEnd is very simple.
Click to expand...

How do you handle the path of the FE <--> SE Linked Table objects? As-is, I see a user specific path getting hard coded in. (Peeking inside of MSysObjects I saw hard coded paths to my personal profile directory... YUCK! Haffa change that!!)

My first thought is to locate the SE in the All User profile directory, however that does not seem an ideal location.

I decided to simply run RoboCopy from the server directory. Executing that across the LAN is less expensive bandwidth wise than copying the FE.

So suggestions how to handle the SE <--> FE link table path, and I think I should be all set.
 
M

mdlueck

Sr. Application Developer
Local time
Today, 12:05
Joined
Jun 23, 2011
Messages
2,631
mdlueck said:
So suggestions how to handle the SE <--> FE link table path, and I think I should be all set.
Click to expand...

Even with nothing getting written to the FE DB, Access still changes the file so RoboCopy always makes a fresh copy.

I think I shall skip trying to get FE/SE working for now based on two negatives:
1) Hard coded path to the SE DB
2) Access still changes the FE DB file, thus not safe not to copy each time the app starts
 
G

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:05
Joined
Jan 20, 2009
Messages
12,854
mdlueck said:
I think I shall skip trying to get FE/SE working for now based on two negatives:
1) Hard coded path to the SE DB
Click to expand...

Hard coded? The Connect Property of the linked tables is easily changed. The path to the user's ApplicationData or LocalSettings folder is easily determined. Relinking tables is hardly an unusual or complicated process.

2) Access still changes the FE DB file, thus not safe not to copy each time the app starts
Click to expand...

Not safe? In what way?

Moreover I can't see how this is a negative about using a Side End.
 
M

mdlueck

Sr. Application Developer
Local time
Today, 12:05
Joined
Jun 23, 2011
Messages
2,631
Galaxiom said:
Relinking tables is hardly an unusual or complicated process.
Click to expand...

Yes, I suppose much the same as my script to update the password in Linked table objects to the SQL Server BE DB which I have in my development copy of the application.

Though I do not recall now if I receive some error message when Access opened the FE and found the SE missing... an error before I could sneak in and update the path.

Galaxiom said:
Not safe? In what way?

Moreover I can't see how this is a negative about using a Side End.
Click to expand...

When I revisited this old thread, I found my #2 point not totally clear. I decided not to clarify what I had written, so will clarify now...

When I configured my application with a FE / SE DB, then Access still made updates to both the SE AND FE DB's.

If I were to implement a non-disposable FE DB, then I would expect Access not to make updates to the FE DB as I was using RoboCopy (in place of XCopy) to provide smart copy where if the file was the same, do not copy the file again. Since Access alters the FE DB, RoboCopy always copies it... so why go to the bother of splitting FE / SE?!

I saw that as defeating the purpose of splitting FE/SE, so why jump through the hoops needed to develop such an environment if it is not going to provide benefit!?
 
You must log in or register to reply here.

Similar threads

P
Win64 version crashing
2 3
Replies
40
Views
998
The_Doc_Man
The_Doc_Man
C
Solved FAYT Listbox crashes Access
Replies
10
Views
384
MajP
MajP
W
Trouble Accessing Linked Tables in Access 2021 via OLEDB Connection: 'Could Not Find Installable ISAM' Error
Replies
6
Views
722
wardah
W
Gasman
Pin to Quick Access
Replies
0
Views
154
Gasman
Gasman
C
Getting "The error 3061, Too few paramaters. Expected 6"
2
Replies
29
Views
1,439
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom