MS Access Records
 

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.

MS Access Records
 

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.

MS Access Records
 

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.

MS Access Records
 

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.

MS Access Records
 

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.

MS Access Records
 

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.

MS Access Records
 

Stops at;

cnn.Open strConn

saying

Authentication failed.

Whats that?






"Mike" wrote:

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.

MS Access Records
 

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=" _
& ThisWorkbook.Path & "\Test.mdb"

'sSQL = "Replace with your query"
sSQL = "Select * from [Case]"

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:

Stops at;

cnn.Open strConn

saying

Authentication failed.

Whats that?






"Mike" wrote:

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.

MS Access Records
 

Hi Mike

I could not sort it out as yet. I will be back on Teusday after trying your
latest set of codes.

Thank you for your time upto now.

M Varnendra

"Mike" wrote:

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=" _
& ThisWorkbook.Path & "\Test.mdb"

'sSQL = "Replace with your query"
sSQL = "Select * from [Case]"

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:

Stops at;

cnn.Open strConn

saying

Authentication failed.

Whats that?






"Mike" wrote:

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.

MS Access Records
 

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.

MS Access Records
 

Hello Mike

Good Morning.

Your codes work. I changed "Field1" to 1 and so on. Now I get the results I
want. Also your codes have a few more things I have to know about but for
readers I am running briefer codes below;

Thank You.

Sub TestOne()

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"
'the above is one line or you can devide using _
rs.Open "Select * from [Case]", cn

Do While Not rs.EOF
For i = 1 To 10
If rs.EOF = True Then
Exit For
End If
ThisWorkbook.Sheets(3).Cells(i, 1).Value = rs.Fields(2).Value
ThisWorkbook.Sheets(3).Cells(i, 2).Value = rs.Fields(3).Value
rs.MoveNext
Next i
Loop

rs.Close
cn.Close

End Sub





"Mike" wrote:

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=" _
& ThisWorkbook.Path & "\Test.mdb"

'sSQL = "Replace with your query"
sSQL = "Select * from [Case]"

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:

Stops at;

cnn.Open strConn

saying

Authentication failed.

Whats that?






"Mike" wrote:

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.

MS Access Records
 

Hi

Thanks for the codes. I will go through the codes and reply. The last codes
sent by Mike do work.

Thanks again.

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

MS Access Records
 

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.

MS Access Records
 

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.