"J_J" wrote:

Hi TK,
Me too will be very inetrested to see all of the code that retrieves and
questions data from Access *.mdb file.
J_J


Hi J_J

Try the following:


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


Good Luck
TK