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.

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.

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.

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.

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.

This is what i use. I like to use the fields name.
rs.Fields("Field1").Value

Private Sub msAccess()
'Needs reference the Axtive X Library 2.0 or higher
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim rowNumber As Long

'C:\PathToYourMdb\Ilsa.mdb (Change)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\PathToYourMdb\Ilsa.mdb;Persist Security Info=False"
'sSQL = "Replace with your query"
sSQL = "SELECT Field1, Field2 From TableName"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open strConn
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
rowNumber = 2 'Starting Row Number for data
Do While (Not rs.EOF)
Range("A" & rowNumber) = rs.Fields("Field1").Value
Range("B" & rowNumber) = rs.Fields("Field2").Value
rowNumber = rowNumber + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

"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.

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.