Hi: Tempy

Here is another example of ADO code, along with Jim's it should
give you a good understanding of the process. This example retrives
data from the nwind db that comes with Access. Cut and paste the
procedure and follow the comments.

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 should 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

.................................................. .............



"Tempy" wrote:

Thanks Jim

Tempy

*** Sent via Developersdex http://www.developersdex.com ***