Moving ADO routine back to DAO

[Ian B]

Hi All
I have a client running an app (Access MDE code SQL2000 data) I developed
using
Windows 2000, Access 2000 under very rigid update control.
I find difficulty in matching MDAC & dll versions between my development
PC's
and Client PC's.
This sometimes results in hard "Catastrophic failure -2147418113" errors on
the client PC's after creation of new MDE's

I use a function to make the ADO connection and return a recordset, which is
called from hundreds of places through the code.
See code snip below.
Anyone care to comment on any increase in stability by reverting to DAO
recordset handling

Ian B

~~~~~~~~~~~~~~~
Public Function MakeRS(sSql As String) As ADODB.Recordset
On Error GoTo MakeRS_Err
100: Dim errsl As Errors
110: Dim errloop As Error
120: Dim i As Integer
130: Dim myCn As ADODB.Connection
140: On Error GoTo adoError
150: Set myCn = CurrentProject.Connection
160: myCn.CursorLocation = adUseClient
170: Set MakeRS = New ADODB.Recordset
180: MakeRS.CursorType = adOpenKeyset
190: MakeRS.LockType = adLockPessimistic
200: MakeRS.Open sSql, myCn, , adCmdTable
MakeRS_Exit:
Exit Function

MakeRS_Err:
Dim strErrString As String
MsgBox "Error in SQL = " & sSql
strErrString = "Error Information..." & vbCrLf
strErrString = strErrString & "Error#: " & Err.Number & vbCrLf
strErrString = strErrString & "Error Description: " & vbCrLf &
Err.Description
MsgBox strErrString, vbCritical + vbOKOnly, "Function: MakeRS" & vbCrLf
Resume MakeRS_Exit
adoError:
i = 1
On Error Resume Next
Set errsl = myCn.Errors
For Each errloop In errsl
MsgBox "Error " & i & vbCrLf & errloop.Number & vbCrLf &
errloop.Description & vbCrLf & errloop.NativeError
i = i + 1
Next
MsgBox "SQL = " & sSql

End Function

 

[Brendan Reynolds]

If you were beginning work on a new app, I'd probably advise sticking with
DAO. Problems with missing DAO 3.6 references are not unheard of, but they
are quite rare, and can often be resolved simply by re-registering the DLL.

I'm not sure that I would go so far as to recommend revising an existing,
non-trivial app, though. If your client has a standardised environment, with
the same version of MDAC on all the target PCs, you might be able to resolve
the problem by refreshing the references and creating the MDE on one of the
client's PCs. If necessary, this can be done remotely using Remote Desktop,
PC Anywhere, etc., and you can delete the MDB from the client's PC once the
MDE has been created.

 

[Ian B]

Thanks Brendan

The recompile of the MDE on Client PC's is a good suggestion.

Ian B.

 

[Guest]

If you know the MDAC version on the client PC's, just load the version onto
your pc, and then go into the VB editor, Tools > References and change the
MDAC version in there, and compile your MDE on your pc. Just leave the
version set in the references, and you'll have no problems. I compile a MDAC
2.7 and 2.8 version of one of my system for several clients who have seperate
systems.

Works great.
Mark