No errors for query in form

[AHendriks]

When executing queries from within the code behind an MS Access form (using linked tables linked to a MS SQL Server database), no error is generated if something is wrong with the query. The same query executed from a query builder window in MS Access will tell me what's wrong (syntax error, record not found, ...), but I do not get this information when the query is executed from code behind a form. When executed from within MS SQL Server, I also do get an error message. Is there a way to raise errors in that case, either by setting some options in MS Access, or by setting options in MS SQL Server. or in the ODBC driver or…

Background info: MS Access XP, SQL Server 2005 (SQL Express), SQL Driver 2000.085.1117.00 (XP sp 2)

Thanx,

André Hendriks

 

[Bilbo_Baggins_Esq]

I would suggest looking at your VBA code for that function first, to see if there is an error handler suppressing the messages.

If not in that function, there may be handlers elsewhere in the Form OnLoad, etc.

 

[AHendriks]

"Bilbo",

Yes there are error handlers defined. But they all DO display the error message. And by setting breakpoints on their entry point or by stepping through the code I can see they are not entered. When stepping through the code and checking all possible error variables just after executing a faulty query, I can see Access seems unaware of any problems.

André

 

[Rabbie]

Are you sure there is an error with the queries. Could it be they just don't select any records. What error is being shown when you run the query from query Builder.

 

[AHendriks]

Rabbie,

The error shown varies. Even when the query is not well formed (like 'SELECT & FROM table', where the & should have been a *) I get no error, just an empty recordset. Thus I am forced to check each generated query (depending on user settings) in the query builder or by running the same test on a different version of the project (with imported tables), costing me loads of time. I don't know why Access does not raise any errors, but it must have something to do with the fact that the tables are all linked tables. When running the project with imported tables Access does raise errors when there is something wrong with the query.

André

 

[Rabbie]

Are your linked tables in an Access database or something else? How exactly are you running your queries from VBA. are using docmd.runSQl or is it some different method. If so what?

 

[Rabbie]

Sorry I missed you were using MS SQL server. Are these pass-through queries being executed there or are the queries being run by JET using data from the linked table?

 

[AHendriks]

Rabbie,

The queries are stored as parameterized queries in MS Access or generated on the fly. Examples:

Set qryAppendSampleSel = db.QueryDefs("qryAppendSampleSel")
qryAppendSampleSel.Parameters("LocGroup") = strLocNetw
Set rstAppendSample = qryAppendSampleSel.OpenRecordset(dbOpenSnapshot)

and

strSQL = "SELECT Parameter.ID AS DATATYPE, " & _
"ParameterGroup.GroupCategory, " & _
"ParameterGroup.DESCRIPT " & _
"FROM Parameter INNER JOIN (ParameterGroup INNER JOIN " & _
"ParameterGrouping ON ParameterGroup.ID = ParameterGrouping.ParameterGroupID) ON Parameter.ID = ParameterGrouping.ParameterID " & _
"WHERE (((ParameterGroup.GroupCategory)=""" & mstrCategory & """) AND " & _
"((ParameterGroup.DESCRIPT)=""" & strGroup & """)); "
Set rs = CurrentDb().OpenRecordset(strSQL)

It all doesn't matter. As soon as I use the application with the linked tables no error information is provided. I am in the proccess of porting the old app, which used Access for all of it's data, to a version that uses MS SQL Server 2005, but the simple fact that it gives me no clue when I make a typing mistake drives me nuts.

André

 

[Rabbie]

Without seeing all your VBA and seeing what "ON Error" statements you have it is a little difficult for me to help you further.

 

[AHendriks]

The code for the error handlers looks like this:

Private Sub cmdCreate_Click()

On Error GoTo Err_cmdCreate_Click

... omitted sub body

Exit_cmdCreate_Click:

... omitted clean up code

Exit Sub

Err_cmdCreate_Click:

MsgBox "Error: " & Err.Number & " " & Err.Description, vbCritical Or vbOKOnly, strTitle

If blnInTransaction Then
wsp.Rollback
MsgBox "Nothing has been saved to the database", vbExclamation Or vbOKOnly, strTitle
End If

Resume Exit_cmdCreate_Click

When running this with tables in Access the error messagebox pops up when the queries generated (using parameter queries in this case) in this sub are wrong. When running with linked tables Access fires the updates and does not notice when anything goes wrong. If I had the time for it I would port the whole #@$ application to .Net and ADO, but I don't.

André

 

[Bilbo_Baggins_Esq]

I could be off base here, but perhaps it is related to setting in SQL Server?

Have you tried to check a condition first, before actually running the query?
That way, even if you can't set an error reporting state in SQL Server, at least you could predict the error condition and halt the code.

 

[Rabbie]

I suspect that SQL server is not returning an error when the SQL is faulty. So it might be worth researching how errors can be passed between SQL Server and Access. Google might help

 

[Bilbo_Baggins_Esq]

I have connected to SQL Server via Access, but have not encountered an error situations in VBA.
However, I have connected to both Access and SQL Server tables via VB, and have received error messages from each when written in VB.

I realize it would be best to get the errors, and I know it adds code, but I still believe you might be able to work around it with a test of conditions before you execute your queries.

 

[AHendriks]

Bilbo,

What do you mean by a test of conditions? In ADO various objects have an errors collection, but not so in DAO, which is still used by Access.

André

 

[Bilbo_Baggins_Esq]

Well, I mean that if there are certain conditions that could exist, or might not exist, that would preferrably cause the query to return an error under proper circumstances (e.g. no matching records, date range, etc), then write some additional code to check for those condition(s) BEFORE your code attempts to execute the queries.

That way you can detect an error condition BEFORE it runs and perform whatever alerting, or checking you need.

Essentially it is pre-emptive, or proactive error handling.

You could use some predefined Select/Group By/Count queries to to gather information BEFORE you run your problematic queries.
Or even build it on the fly as you have already done, but modified to gather counts, again BEFORE you run your problematic queries.

I have employed this "testing" procedure in numerous other circumstances quite successfully.
Ideally, you really would not want to run to an error anyway, (even with an error handler), if it can be avoided.