DAO vs ADO? 5

[dabruins]

I've been asked to extend an existing Access database to include many more tables containing many more records (some tables contain over 1 million records). In order to handle these table sizes I've decided to move the tables into a MySQL database and utilize access as a frontend database. As I have been working on building the inteface to the data I have been reading many articles and postings on using access as a frontend to a database server and I feel myself becoming more confused than knowledgable. The application will be used by about 100 users in two offices in different cities. I'm looking for a good resource that will help me decide how to develop this application. If you can point me in the direction of a good text or site that would be great. The following are a few points I want to clarify.

1. My current application uses many bound forms. Is the use of bound forms advisable? Why or why not?

2. If forced to use unbound forms must I automatically move to using ADO or is DAO still an option? There seems to be much more coding required with unbound forms and I'm still struggling to determine exactly what functionality ADO and DAO do/do not provide.

3. Is DAO synonymous with Jet Database Engine and Access? I've read that relying on Jet for larger dataserver environments is not advisable, hence my concern about continuing to use DAO recordsets.

4. I'm also trying to find an example of how an unbound form might synchronize with a subform(s). I've gotten used to the bound form world and am trying to understand how to provide simliar functionality within an unbound form.

I apologize for the lengthy post. Again I'm not expecting anybody to solve all my problems. I'm just looking for a little direction on these points. Thanks.

 

[dabruins]

Hi PHV.

That option is turned on in my VBE.

 

[PHV]

What happen if you replace this:
On Error GoTo Err_cmdGotoNext_Click
with this ?
On Error GoTo 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ddiamond]

Here are the different flavors of resume:
Resume 'goto to line that caused error
Resume Next 'goto to line right after error
Resume <label> 'goto label

The problem is that you specified a label to resume to:
Resume Exit_cmdGotoNext_Click

If you don't specify the label and use resume by itself, it will resume to the line that caused the error. In most cases this will trigger the error again, but since the resume turned off the error handler, your code should break this time.

 

[ddiamond]

What I just posted about resume only works with the mdb file. Is and mdb or and mde?

 

[dabruins]

No that didn't have any effect either. But I would like to know what "0" refers to in the statement "On Error Goto 0"? I've seen it used in other posted code but I'm not sure what its referring to.

 

[PHV]

With the cursor inside the Error keyword in your code simply press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

BTW, have you tried to tick the Stop on all error checkbox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dabruins]

Yes its and mdb frontend file connecting to a MySQL database server via ODBC.

I tried using Resume without specifying a goto line but I ended up in a loop of error msgbox's. Not sure what you mean by "but since the resume turned off the error handler, your code should break this time". How does it turn off the error handler?

Again I'm not well schooled in this so I've been going by the examples I've picked up from the doc's and on-line. Thanks for your help ddiamond and PHV!

 

[PHV]

Have you tried the On Error Goto 0 instruction ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dabruins]

Doh! Thanks for the tip PHV.

Yes I tried the "stop on all errors" option but found no difference.

Actually this made things even more confusing for me. I reverted back to my code as posted above and wasn't even getting the msgbox window to appear showing an error as it had done earlier. I changed the option back to Break on Unhandle Errors and presto the msgbox appears as before. So it breaks at Unhandled errors but not at All errors! I can't figure out what that means?

I've now gone with "Resume next" in my error handler with the Break at Unhandled errors option and it does bring back the errors from the data provider. The problem is I'm again stuck in an error loop of popup msgbox's. I'm still not able to see which line is causing the error.

I think I'm getting closer on this. Thanks guys!

 

[dabruins]

Yes I have tried the On Error Goto 0 instruction but it did not yield any indication of an error.

 

[ddiamond]

I used google to find the following code example. It uses the function erl() to determine where the error occured.

Sub MyError ()
  On Error GoTo ErrorHandler
  INTEGERVAL% = 99999 'Generates Numeric Overflow error
  Debug.Print "Error was ignored"
  Exit Sub

ErrorHandler:
  If MsgBox("The following error has occurred at line " &_
    Trim(Str(Erl)) & ":" & Chr(13) & Chr(10) & Chr(13) &_
    Chr(10) & Error$, 17) = 1 Then Resume Next Else Stop
  Exit Sub
End Sub

 

[Golom]

ddiamond

Erl reports the Line Number at which the error occured. The sample code you provided doesn't have line numbers so Erl won't do much for you. You would need

Sub MyError ()
10   On Error GoTo ErrorHandler
20   IntegerVal% = 99999  'Generates Numeric Overflow error
30   Debug.Print "Error was ignored"
40   Exit Sub

[COLOR=green]' etc.[/color]

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ddiamond]

Golum,

I was afraid of that. Having to add line numbers is quite inconvenient.

- Dan

 

[Golom]

True ... but I cheat.

If you have Visual Studio 6 (big if, I grant) you can copy Access code to a VB project and use a VB addon called MZTools that will add line numbers to a block of code. Then just copy it back to Access.

If not .... well

10
20
30
40
:
:

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[dabruins]

Hi ddiamond.

Thanks for this code. I appreciate the help you've been giving me on this.

I used this code except I chose to use exit sub instead of resume next. The one thing about this is that you have to revert to the old basic line numbering in order for this to work otherwise you don't know where the error occurred. Again I was hoping to have the debugger jump to the line where the error occurred. Do you know of a way to automatically generate the line numbers in vba?

I'm looking into the way that errors are handled in the ADO connection object. I believe I have to check to see if the connection has any errors and then get a handle on it (conn.errors(0)),pass its information to the msgbox and then stop the code. Not sure if this will get me what I want either?

http://support.microsoft.com/?kbid=209855

 

[Golom]

If you want to process ADO Errors

Dim erADO As ADODB.Error
Dim myMsg As String
For Each erADO In cn.Errors  [COLOR=green]' replace cn with connection variable[/color]
   If Err.Number <> erADO.Number _
      Or Err.Description <> erADO.Description Then

      myMsg = myMsg _
              & "Error #" & CStr(erADO.Number) _
              & erADO.Description 

   End If
Next

An error raised by the standard error object (Err) may have a different message than the ADO error from which it originated. The above just searches the ADO errors collection associated with the ADO connection for any ADO errors that were raised.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[dabruins]

Hi Golam.

I've been reading about trapping ODBC errors passed via ADO and tried using this but for some reason I don't get the conn.errors.count > 1?? Maybe the conn setup is the problem? At any rate I don't think it will tell me where in the code the error occurred, only that an error has occurred and it origainted in the call to my data provider. I'm not sure if there is a way to communicate the line number of the error to the developer?

Thanks.

 

[sozzer]

Just a quick note on getting line numbers into code easily without VB6 etc:

Copy the entire code you want line numbers for a paste it into B1. In A1, type 10, 20, 30 etc and then use the fill handle to drag down to the end of your code.

In cell c1, type =A1&" "&B1

Copy row c and paste into the code window.

 

[JerryKlmns]

sozzer gets a star for that trick!