Hi!

This is actually for fellow users. The following codes pull data from Access
in chunks;

Sub TestOne()

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

Cells(1, 1).Select

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


rs.Move (500)
ThisWorkbook.Sheets(3).Cells(7, 2).CopyFromRecordset rs, 1000000, 6

End Sub

"Joel" wrote:

You can use text-to-columns

Columns("A:A").TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True


"Varne" wrote:

Hi Joel

Thank You.

I have been able to make use of your codes. I mean 'Chunk' copying. As per
your suggestion. It has worked. I am trying to insert variables into column
seperating statics. If you have refined ways you may give me but not
essential. Once done I give it down here.

Thanks again. Very useful contribution.

M Varnendra

"Joel" wrote:

the code below will create a comma delimited string. You can use split or
text to columns to seperae fields.

Option Explicit

Sub TestOne()

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

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



Do While Not rs.EOF
For i = 1 To 10
If rs.EOF = True Then
Exit For
End If

myrow = rs.GetString(StringFormat:=adClipString, _
numrows:=1, ColumnDelimeter:=",")
ThisWorkbook.Sheets(3).Cells(i, 1) = myrow
rs.MoveNext
Next i
Loop

rs.Close
cn.Close

End Sub


"Varne" wrote:

Hi!

The below given Macro reads data from Access on a cell by cell basis. Can
someone make adjustments on it make it copy a chunk of field 1. It is like
for example copying records(2,3,4) in Access and paste onto cells(1,1) in
Excel.

Thank You.

----------------------------------------------------------------

Option Explicit
------------------------------------------------------------------
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"
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