I have misunderstood about Open method of recordset, so in my code,
rs.Open.. part was redundant. rs.Open "Select * from Case", cn is
enough. sorry for wrong explanation.

keiji

Varne wrote:
Thanks Keiji

Your codes flexibility regards to number of fields the codes cover. Useful.

Thank You.

"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

there are some ways to access a database with ADO. I think you didn't
open Recordset with your way, so your code fails at
ThisWorkbook.Sheets(3).Cells(i, h).Value = rs.Fields(i).Value
I am not sure whether this would work or not, but try this one.

Sub TestOne()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim fld
Dim h As Long, i As Long

ThisWorkbook.Sheets(3).Cells.ClearContents
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
ThisWorkbook.Path & "\Test.mdb"
'if your table name is [Case], Change Case below to [Case]
rs.Open "Select * from Case", cn.ConnectionString

h = 1
i = 1

'set fields name,
'if you don't need, delete code from "for" to "h=h+1"
For Each fld In rs.Fields
ThisWorkbook.Sheets(3).Cells(h, i).Value = fld.Name
i = i + 1
Next
h = h + 1

'put data into cells
Do While rs.EOF = False
i = 1
For Each fld In rs.Fields
ThisWorkbook.Sheets(3).Cells(h, i).Value = fld.Value
i = i + 1
Next
h = h + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

keiji

Varne wrote:
Hi

Thanks for replying.

The macro draws on the Access cell row 1 in field 1 throughout the loop.

And h is no string. It is the variable for column in Excel so just like 'i'
coming in both sides of the equation h also should come in both sides. Where
and How?

rs.Fields takes care of 'i' but what would take care of 'h'. 'rs.Records'
does not work. In Excel 'Cells' takes care of both.

I missed to declare 'h' last time. Revised codes;

Sub TestOne()

On Error GoTo a

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long

ThisWorkbook.Sheets(3).Cells.ClearContents

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
ThisWorkbook.Path & "\Test.mdb"
rs.Open "Select * from [Case]", cn

For h = 1 To 5
For i = 1 To 5
If rs.EOF = True Then
Exit Sub
End If
ThisWorkbook.Sheets(3).Cells(i, h).Value = rs.Fields(i).Value
Next i
Next h

rs.Close
cn.Close

a:
Exit Sub
End Sub


Thank You.

"Mike" wrote:

Sub TestOne()

On Error GoTo a
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long

ThisWorkbook.Sheets(3).Cells.ClearContents

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
ThisWorkbook.Path & "\Test.mdb"
rs.Open "Select * from [Case]", cn

For i = 1 To 5
If rs.EOF = True Then
Exit Sub
End If
'ThisWorkbook.Sheets(3).Cells(i, "h").Value = _
rs.Fields(i).Value '***the problem line
ThisWorkbook.Sheets(3).Cells(i, "h").Value = _
rs.Fields(1).Value '***the problem line
Next i
rs.Close
cn.Close

Exit Sub
a:

End Sub

"Varne" wrote:

Hi

Could you please help in completing the loop;

Sub TestOne()

On Error GoTo a
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long

ThisWorkbook.Sheets(3).Cells.ClearContents

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
ThisWorkbook.Path & "\Test.mdb"
rs.Open "Select * from [Case]", cn


For i = 1 To 5
If rs.EOF = True Then
Exit Sub
End If
ThisWorkbook.Sheets(3).Cells(h, i).Value = rs.Fields(i).Value ***
the problem line
Next i
rs.Close
cn.Close

a:
Exit Sub
End Sub


"Joel" wrote:

Your question can have a few different answers. You can put code in a loop
to extract the data. You need to move down one row at a time and then read
the data from the field(s) you want.

You can extract an entire table if you want depending on how you have yur
database setup. Excel can read predefined tables and query from Access.

One thing you can do is in excel start the macro recorder. then perform a
query by using the worksheet menu Data - Import External data - New Database
Query. Select Access as the database and browse for the MDB file. Then
select the option you want to import into excel. You can then use the
recorded macro in your own macro. You can also modify the recorded macro as
required.

"Varne" wrote:

Hi

The following can extract data from one row in an Access table;

(Connection and Recordset declared)

ThisWorkbook.Sheets(3).Cells(h, i).Value = rs.Fields(i).Value

But does Access has something similar to Cells(h,i) to enable extracting
data of a whole table using a 'For Loop'.

Can someone help?

Thanks.