EMoe:
As keepiTcool points out, ADO is the newest
(really not that new) and the recomended way
to go by people that recomend things like that.
Here is a procedure that willl get you statrd in that direction.
Good Luck
TK
Private Sub CommandButton4_Click()
On Error GoTo ErrHandler
Dim rg As Range
Set rg = ThisWorkbook.Worksheets(2).Range("a1")
'To use ADO objects in an application add a reference
'to the ADO component. From the VBA window select
'>Tools/References< check the box
' "Microsoft ActiveX Data Objects 2.x Library"
'You must fully quality the path to YOUR file
Dim db_Name As String
db_Name = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb")
Dim DB_CONNECT_STRING As String
DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data Source=" & db_Name & ";" & ", , , adConnectAsync;"
'Create the connection
Dim cnn As New ADODB.Connection
Set cnn = New Connection
cnn.Open DB_CONNECT_STRING
'Create the recordset
Dim rs As ADODB.Recordset
Set rs = New Recordset
'Determines what records to show
Dim strSQL As String
strSQL = "SELECT CompanyName, ContactName, City, Country " & _
"FROM Customers ORDER BY CompanyName"
'Retreive the records
rs.CursorLocation = adUseClient
rs.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic
'Test to see if we are connected and have records
Dim num As Integer
num = rs.RecordCount
Dim num1 As Integer
num1 = rs.Fields.Count
If cnn.State = adStateOpen Then
MsgBox "Welcome to! " & db_Name & " Records = " & num & " Fields =
" & num1, vbInformation, _
"Good Luck TK"
Else
MsgBox "Sorry. No Data today."
End If
'Copy recordset to the range
rs.MoveLast
rs.MoveFirst
rg.CopyFromRecordset rs
rg.CurrentRegion.Columns.AutoFit
'close connection
cnn.Close
Set cnn = Nothing
Set rs = Nothing
Exit Sub
ErrHandler:
MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly
End Sub
