by Rone (9 Submissions)
Category: Miscellaneous
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Fri 14th May 2004
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Queries an SQL database using Excel and displays it in a spreadsheet
VBcoders
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim iField As Long
Dim strSql As String
Dim strValues As String
Dim strClientCriteria As String
Dim strAMCriteria As String
Set oConn = New ADODB.Connection
Set oRS = New ADODB.Recordset
oConn.CursorLocation = adUseClient
oConn.Open "Provider=SQLOLEDB;Data Source=stagdata2;Initial Catalog=SupportSite;User ID=sa;Password=whatver"
'Retrieve additional criteria for the SQL for the CLIENT
strClientCriteria = Worksheets("Graphs").Range("Client").Value
If UCase(strClientCriteria) = "ALL" Then
strClientCriteria = ""
Else
strClientCriteria = "AND a.client like '" & strClientCriteria & "' "
End If
'Retrieve additional criteria for the SQL for the ACCOUNT MANAGER
strAMCriteria = Worksheets("Graphs").Range("Account_Manager").Value
If UCase(strAMCriteria) = "ALL" Then
strAMCriteria = ""
Else
strAMCriteria = "AND a.HistoryFullUserName like '" & strAMCriteria & "' "
End If
If UCase(strDataType) = "DATA" Then
strSql = " SELECT a.projectname, c.projectstepname, b.changedate " _
& "FROM project a, Project_History b, projectsteps c " _
& "WHERE a.ProjectID = b.ProjectID " _
& "AND b.newvalue = c.projectstepid " _
& "AND b.Field = 'ProjectStatus' " _
& "AND c.projectstepname <> 'Canceled' " _
& "AND a.status <>'closed' " _
& strClientCriteria _
& strAMCriteria _
& "ORDER BY a.projectname, b.changedate "
ElseIf UCase(strDataType) = "TOTAL_DOLLARS" Then
strSql = "SELECT a.projectname, b.projectstepname, a.estdollar, a.actdollar " _
& "FROM project a, projectsteps b " _
& "WHERE a.projectstatus = b.projectstepid " _
& "AND a.status <>'closed' " _
& "AND a.type in ('Feature Request', 'Change Request', 'Maintenance Request', 'Implementation Request') " _
& "AND a.projectstatus IS NOT NULL " _
& "AND a.client NOT LIKE '%steton%' " _
& strClientCriteria _
& strAMCriteria _
& "ORDER BY 1 "
End If
oRS.Open strSql, oConn
'Load the Data into the spreadsheet
If Not oRS.EOF Or oRS.BOF Then
Range("A18").CopyFromRecordset oRS
End If
'Close everything
oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
End Sub
No comments have been posted about Queries an SQL database using Excel and displays it in a spreadsheet. Why not be the first to post a comment about Queries an SQL database using Excel and displays it in a spreadsheet.