mdb"
Set xlSheet = Sheets("Sheet1")
xlSheet.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
' Open connection to the database
' cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & ??
"Data Source=" & arr_sPath(0) & ";"
''When using the Access 2007 Northwind database
''comment the previous code and uncomment the following code.
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & ??
"Data Source=" & arr_sPath(0) & ";"
Set rs = New ADODB.Recordset
' Open recordset based on Orders table
rs.Open "Select * From Orders", cnn
iFieldCount = rs.Fields.Count
For i = 1 To iFieldCount
xlSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i
' Copy the recordset to the worksheet, starting in cell A2
xlSheet.Cells(2, 1).CopyFromRecordset rs
xlSheet.Select
'Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
'Range("A1").Select
CHAPTER 9 n ACTIVEX AND .NET 317
rs.Close
cnn.Close
Set xlSheet = Nothing
Set rs = Nothing
Set cnn = Nothing
End Sub
This code dropped the result of a SQL SELECT statement onto Sheet1 in our sample file.
Our ActiveX component allows us to choose which worksheet we put our data on and select
which data we want, giving us a quick tool for querying the Northwind database.
The VB 6 code here is also split into a data class and a second class named cExcelNwind.
The data class, cData, is doing some work this time. Its GetData method will return a recordset
to the cExcelNwind class.
Pages:
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296