DAO to ADODB - I have no clue, please help

[fraser_lindsay]

I'll try and make this concise. I have a QBF with code from this web site and sample DBs. It works well and uses the DAO object library. Up to now I have been winging it with code basically.

I want to create a new QBF with addition pre-set search parameters in addition to facility to be able to vary some select parameters.

I bought a VBA book and this uses ADODB and tells me that DAO is dead. Which is nice, but not helpful seeing the code I have is DAO.

I have started trying to convert it to ADODB so I can add in my other parameters but it doesn't work.

Can anyone point me in the right direction here please?


ORIGINAL DAO CODE:
Private Sub btnPreview_Click()


Dim db As DAO.Database
Dim QD As DAO.QueryDef
Dim where As Variant
Dim varItm As Variant
Dim Answer As String
Dim F, T As Variant


On Error GoTo HandleErr
Set db = CurrentDb()
'Delete the existing dynamic query; trap the error if the query does not exist.
' TO DO: Turn normal error handler on when this condition is finished.
On Error Resume Next
db.querydefs.Delete ("Dynamic_Query99")


' TO DO: Turn normal error handler on when this condition is finished.
On Error GoTo 0


where = Null

If Not IsNull(Me.[cboDept]) Then
where = where & " AND [Department] = " & Me.[cboDept]
End If

If Not IsNull(Me.[cboCategory]) Then
where = where & " AND [Category] = " & Me.[cboCategory]
End If

If Not IsNull(Me.[cboBusiness]) Then
where = where & " AND [BusinessName] = " & Me.[cboBusiness]
End If

If Not IsNull(Me.[cboBusinessUnit]) Then
where = where & " AND [BusinessUnit] = " & Me.[cboBusinessUnit]
End If

If Not IsNull(Me.[cboLocation]) Then
where = where & " AND [Location] = " & Me.[cboLocation]
End If


'LITTLE PAL - The query below is what I made up. You can use your qryWeeklyOrderStatusRpt query but be
'sure that you have the MCID, ODPart and ODStatus in that query or the code above will not work...
Set QD = db.CreateQueryDef("Dynamic_Query99", _
"Select * from qryAllInfo " & (" where " + Mid(where, 6) & ";"))

'If selections did not return any results then let user know
If DCount("*", "Dynamic_Query99") = 0 Then
MsgBox "No records to display."
Exit Sub
End If

'DoCmd.OpenQuery "Dynamic_Query99"
DoCmd.OpenReport "rptRRReports", acViewPreview

ExitHere:
Exit Sub

' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at 11-15-2005 14:45:26 'ErrorHandler:$$D=11-15-2005 'ErrorHandler:$$T=14:45:26
HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Form_frmRRReports.btnPreview_Click" 'ErrorHandler:$$N=Form_frmRRReports.btnPreview_Click
End Select
' End Error handling block.
End Sub



ATTEMPTED CONVERSION (I HAVE NO IDEA REALLY)

Private Sub btnPreview_Click()

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim OHMset As New ADODB.Recordset
OHMset.ActiveConnection = cnn1

OHMset.Open "SELECT qryAllInfo.PersonnelNumber, qryAllInfo.Surname, qryAllInfo.FirstName, qryAllInfo.Location, qryAllInfo.Department, qryAllInfccupationalGroup, qryAllInfo.tblEmployeeDetails.OccProfileRef, qryAllInfo.Noise, qryAllInfo.HAVS, qryAllInfo.Skin, qryAllInfo.Respiratory, qryAllInfo.MSD, qryAllInfo.Psychosocial, qryAllInfo.Biological, qryAllInfo.Environment FROM qryAllInfo WHERE (((qryAllInfo.Noise)>=50))"


Dim where As Variant
Dim varItm As Variant
Dim Answer As String
Dim F, T As Variant

On Error GoTo HandleErr
Set db = CurrentDb()
'Delete the existing dynamic query; trap the error if the query does not exist.
' TO DO: Turn normal error handler on when this condition is finished.
On Error Resume Next
db.querydefs.Delete ("Dynamic_Query99")


' TO DO: Turn normal error handler on when this condition is finished.
On Error GoTo 0

where = Null


If Not IsNull(Me.[cboCategory]) Then
where = where & " AND [Category] = " & Me.[cboCategory]
End If

If Not IsNull(Me.[cboBusiness]) Then
where = where & " AND [BusinessName] = " & Me.[cboBusiness]
End If

If Not IsNull(Me.[cboBusinessUnit]) Then
where = where & " AND [BusinessUnit] = " & Me.[cboBusinessUnit]
End If

If Not IsNull(Me.[cboLocation]) Then
where = where & " AND [Location] = " & Me.[cboLocation]
End If

If Not IsNull(Me.[cboDept]) Then
where = where & " AND [Department] = " & Me.[cboDept]
End If




Set QD = db.CreateQueryDef("Dynamic_Query99", _
"Select * from qryAllInfo " & (" where " + Mid(where, 6) & ";"))

'If selections did not return any results then let user know
If DCount("*", "Dynamic_Query99") = 0 Then
MsgBox "No records to display."
Exit Sub
End If

'DoCmd.OpenQuery "Dynamic_Query99"
DoCmd.OpenReport "rptOHMStatus", acViewPreview

ExitHere:
Exit Sub

' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at 11-15-2005 14:45:26 'ErrorHandler:$$D=11-15-2005 'ErrorHandler:$$T=14:45:26
HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Form_frmOHM.btnPreview_Click" 'ErrorHandler:$$N=Form_frmOHM.btnPreview_Click
End Select
' End Error handling block.
End Sub

 

[pbaldy]

This isn't really a direct answer to your question, but I wouldn't bother trying to convert it. Rumors of DAO's demise are false; it's even back as a default reference in A2003.