Databases Reference
In-Depth Information
'ConnectString = "ODBC;DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=" & mdbfile
'REMOVE "ODBC;" PREFIX WHEN USING ADO CONNECTIONS
ConnectString
= "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="
& mdbfile
cn.Open ConnectString
Set rs = New ADODB.Recordset
'Return Recordset limited by Query
rs.Open SQLString, cn, adOpenForwardOnly, adLockReadOnly,
adCmdText
'Write in Headers (Done Automatically with XLODBC )
Call GetFieldNames((ConnectString),
frm8Extract.ComboBox_Tables.Text, Fields())
For col = 0 To rs.Fields.Count - 1
If frm8Extract.ComboBox_Fields.Text = "*" Then
ActiveSheet.Cells(1, col + 1).Value = Fields(col + 1)
Else
ActiveSheet.Cells(1, col + 1).Value =
Fields(frm8Extract.ComboBox_Fields.ListIndex + col)
End If
Next col
'Now Write Information to Worksheet
wksrow = 2 'Start at Row 2, Row 1 = Column Headers
rs.MoveFirst
Do Until rs.EOF '*** Use For Loop to .Fields of All listbox instead?
For col = 0 To rs.Fields.Count - 1
ActiveSheet.Cells(wksrow, col + 1).Value = rs.Fields(col)
Next col
'Increment Worksheet Row
wksrow = wksrow + 1
rs.MoveNext ' Move to next record in table.
Loop
'Release Objects!
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Notice that the ADO connection syntax is nearly identical to the DAO connection syntax. One
notable exception is that the connection string using ADO must not have the “ODBC”; prefix.
Doing so will result in a failed connection. Speaking of failed connections, a word of warning is
in order with respect to use of the
On Error Resume Next
syntax. Although including this
statement allows a macro to jump over database entries that trigger errors, it also allows the code
to continue running when ANY error results. This can be problematic when debugging subroutines
