Hey gents, I solved my original question ages ago. ADO is an ActiveX object which is not part of either Access 97 or Access 2000 but is used to connect to OLEDB, ODBC, or SQLServer. I have used ADO to successfully extract the data from Access (either 97 or 2000) and place it in Excel. The concepts are the same if it is in Access. The below function does more than just build a recordset and copy it into a worksheet but you can analyze and modify as needed. I also built a function that would run a stored procedure on Access from Excel through ADO. But that is another story.
Public Function CreateReportFromQueryADO(Optional DbPath As String, _
Optional SQL_Query As String) As Boolean
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim ws As Worksheet
Dim iCols As Integer
On Error Resume Next
' Open the connection.
Set cnnConn = New ADODB.Connection
'With cnnConn
' .ConnectionString = _
' "Provider=Microsoft.Jet.OLEDB.4.0"
' .Open DefaultDbPath
'End With
' If the DbPath was provided verify it
' otherwise open from the default database.
If Len(DbPath) > 0 Then
' Check to verify that the database exists at the location stated
' in the input parameter
If Len(Dir(DbPath)) > 0 Then
With cnnConn
'.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & DbPath & ";" _
& "Jet OLEDB:System Database=C:\Dmats\system2000.mdw;" _
& "Jet OLEDB
atabase Password=guest"
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open DbPath
End With
Else
MsgBox "Invalid database path (" & DbPath & "
"
Exit Function
End If
Else
If Len(Dir(DefaultDbPath)) > 0 Then
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open DefaultDbPath & ";UID=guest;PWD="
End With
Else
MsgBox "Invalid database path (" & DefaultDbPath & "
"
Exit Function
End If
End If
On Error GoTo HandleErr
' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = SQL_Query
.CommandType = adCmdText
.Execute
End With
If cnnConn.Errors.Count > 0 Then
Err.Raise 10621, "cnnConn", "Error Returns following execute."
End If
' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand
' If there are no records then just notify the user
' and exit the procedure.
If rstRecordset.EOF Then
MsgBox "There are no records for this request. Please " _
& "review your request to ensure it is correct."
Exit Function
Else
CreateNewSheet
End If
'Worksheets.Add Count:=1, Before:=Sheets(1)
Set ws = Worksheets(1)
For iCols = 0 To rstRecordset.Fields.Count - 1
ws.Cells(1, iCols + 1).Value = rstRecordset.Fields(iCols).Name
Next
ws.Range(ws.Cells(1, 1), _
ws.Cells(1, rstRecordset.Fields.Count)).Font.Bold = True
ws.Range("A2"
.CopyFromRecordset rstRecordset
Exit_Proc:
On Error Resume Next
' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
cnnConn.Close
Set cnnConn = Nothing
Exit Function
HandleErr:
Select Case Err.Number
Case Else
Call HandleTheError("", "frmB2ReportGenerator.CreateReportFromQueryADO", Err)
End Select
Resume Exit_Proc
Resume
End Function ----------------------
Steve King
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------
