ADO Connection to Backend SQL Server 1

[acv111]

Hi,

I am using MS Access forms as a front end to a backend SQL Server Database. I am trying to use an ADO connection to pass a parameter query to the SQL Server. I have been able to establish the connection to the SQL Server and have verified that the SQL statement is correct. I am completely new to ADO and I can't figure out how to display the data returned in the ADO recordset. Could someone please help me out with this? Is there a way to display the returned recordset in Access to be viewable by the user? Below is my code for your reference:

Private Sub btnViewReport_Click()
On Error GoTo Err_btnViewReport_Click

If cmbViewReport.Value = "Average Days for Completed Case - Decision" Then

Dim StartDate As Date
Dim EndDate As Date
Dim Connection
Dim DSN
Dim Recordset
Dim SQL

StartDate = InputBox("Enter the beginning date for the timeframe of the report in the format mm/dd/yyyy." & vbCrLf & "If you would like to view the report for all completed investigations, enter 1/1/1999.", "Start Date", #1/1/1999#)
EndDate = InputBox("Enter the end date for the timeframe " & vbCrLf & "of the report in the format mm/dd/yyyy." & vbCrLf & "If you would like to view the report" & vbCrLf & "for all completed investigations, enter 12/31/2030.", "End Date", #12/31/2030#)

DSN = "PROVIDER=SQLOLEDB;DRIVER={SQL Server};SERVER=MyServer;DATABASE=MyDBase;UID=MyID;PWD=MyPass"
SQL = _
"SELECT dbo.tblUpdateInvest.[InvestigationDecision], COUNT(dbo.tblPlan.[Plan]) AS [Number of Investigations]," & _
" AVG(CONVERT(DECIMAL, dbo.tblUpdateInvest.Date_Case_Completed - CONVERT(DATETIME, CONVERT(VARCHAR(10),DATEPART(mm, Right(Left(Right(Year(GetDate()),2),1) + Left(dbo.tblInvestigationInfo.ClaimNumber,4),3)-1)) + '/' +" & _
" CONVERT(VARCHAR(10),DATEPART(dd, Right(Left(Right(Year(GetDate()),2),1) + Left(dbo.tblInvestigationInfo.ClaimNumber,4),3)-1)) + '/' +" & _
" CONVERT(VARCHAR(10),CASE WHEN Left(Left(Right(Year(GetDate()), 2),1) + Left(dbo.tblInvestigationInfo.ClaimNumber,4),2) < 30 Then 2000 Else 1900 End + Left(Left(Right(Year(GetDate()),2),1) + Left(dbo.tblInvestigationInfo.ClaimNumber,4),2))))) AS [Age of Investigation]" & _
" FROM dbo.tblPlan INNER JOIN dbo.tblInvestigationInfo ON dbo.tblPlan.PlanCode = dbo.tblInvestigationInfo.[Plan] INNER JOIN" & _
" dbo.tblEmployee ON dbo.tblInvestigationInfwner = dbo.tblEmployee.Login LEFT OUTER JOIN dbo.tblUpdateInvest" & _
" ON dbo.tblInvestigationInfo.PrimaryKey = dbo.tblUpdateInvest.PrimaryKey WHERE (dbo.tblUpdateInvest.InvestigationDecision IS NOT NULL) AND (dbo.tblUpdateInvest.Date_Case_Completed Is Not Null) AND" & _
" (dbo.tblUpdateInvest.Date_Case_Completed >= '" & StartDate & "' AND dbo.tblUpdateInvest.Date_Case_Completed <= '" & EndDate & "') GROUP BY dbo.tblUpdateInvest.InvestigationDecision"

Set Connection = CreateObject("ADODB.Connection")
Set Recordset = CreateObject("ADODB.Recordset")

Connection.Open DSN
Recordset.Open SQL, Connection

Recordset.Close
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
Exit Sub
End If

Exit_btnViewReport_Click:
Exit Sub

Err_btnViewReport_Click:
MsgBox Err.Description
Resume Exit_btnViewReport_Click
End Sub

 

[PHV]

Have you tried something like this ?
Connection.Open DSN
Recordset.Open SQL, Connection
Set Me.Recordset = Recordset

Note: Avoid to use class names (Connection, Recordset) as variable names ...

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

 

[acv111]

Hi PH,

I changed the variable names and tried your suggestion...I am now receiving an error that reads, "The object you entered is not a valid Recordset property."

If cmbViewReport.Value = "Average Days for Completed Case - Decision" Then

Dim StartDate As Date
Dim EndDate As Date
Dim Conn
Dim DSN
Dim RS
Dim SQL

StartDate = InputBox("Enter the beginning date for the timeframe of the report in the format mm/dd/yyyy." & vbCrLf & "If you would like to view the report for all completed investigations, enter 1/1/1999.", "Start Date", #1/1/1999#)
EndDate = InputBox("Enter the end date for the timeframe " & vbCrLf & "of the report in the format mm/dd/yyyy." & vbCrLf & "If you would like to view the report" & vbCrLf & "for all completed investigations, enter 12/31/2030.", "End Date", #12/31/2030#)

DSN = "PROVIDER=SQLOLEDB;DRIVER={SQL Server};SERVER=MyServer;DATABASE=MyDbase;UID=MyID;PWD=MyPass"
SQL = _
"SELECT dbo.tblUpdateInvest.[InvestigationDecision], COUNT(dbo.tblPlan.[Plan]) AS [Number of Investigations]," & _
" AVG(CONVERT(DECIMAL, dbo.tblUpdateInvest.Date_Case_Completed - CONVERT(DATETIME, CONVERT(VARCHAR(10),DATEPART(mm, Right(Left(Right(Year(GetDate()),2),1) + Left(dbo.tblInvestigationInfo.ClaimNumber,4),3)-1)) + '/' +" & _
" CONVERT(VARCHAR(10),DATEPART(dd, Right(Left(Right(Year(GetDate()),2),1) + Left(dbo.tblInvestigationInfo.ClaimNumber,4),3)-1)) + '/' +" & _
" CONVERT(VARCHAR(10),CASE WHEN Left(Left(Right(Year(GetDate()), 2),1) + Left(dbo.tblInvestigationInfo.ClaimNumber,4),2) < 30 Then 2000 Else 1900 End + Left(Left(Right(Year(GetDate()),2),1) + Left(dbo.tblInvestigationInfo.ClaimNumber,4),2))))) AS [Age of Investigation]" & _
" FROM dbo.tblPlan INNER JOIN dbo.tblInvestigationInfo ON dbo.tblPlan.PlanCode = dbo.tblInvestigationInfo.[Plan] INNER JOIN" & _
" dbo.tblEmployee ON dbo.tblInvestigationInfwner = dbo.tblEmployee.Login LEFT OUTER JOIN dbo.tblUpdateInvest" & _
" ON dbo.tblInvestigationInfo.PrimaryKey = dbo.tblUpdateInvest.PrimaryKey WHERE (dbo.tblUpdateInvest.InvestigationDecision IS NOT NULL) AND (dbo.tblUpdateInvest.Date_Case_Completed Is Not Null) AND" & _
" (dbo.tblUpdateInvest.Date_Case_Completed >= '" & StartDate & "' AND dbo.tblUpdateInvest.Date_Case_Completed <= '" & EndDate & "') GROUP BY dbo.tblUpdateInvest.InvestigationDecision"

Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")

Conn.Open DSN
RS.Open SQL, Conn
Set Me.Recordset = RS

RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing
Exit Sub
End If

Thanks - A

 

[PHV]

I'm afraid that only .ADP forms may be bound to ADODB recordset.

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