Extracting certain rows to excel

[brites]

Hi there, I have a table with 225.000 rows, I want to extract them 65.000 by 65.000 to an excel spreadsheet.

I dont have any numbering fild to help me. I got the first 65.000 with the instruction:
SELECT TOP 65000 * FROM TABLE_NAME,

How can I extract rows 65001 to 130000 and so on?

Thanks in advance!

Brites

 

[james_lankford]

hopefully that large table has a primary key;
so maybe create a new table that has a column named id, type autonumber and a column that is named and typed the same as the primary key of the large table
then select the primary keys of the large table into the new table and the new table's autonumber will auto populate
then join the two tables on the large table's primary key and where the new table's autonumber is between 1 and 60000 ;
after that do another query where its between 60001 and 120000 and so on

 

[SydneyGeek]

I tend to run this from Excel, rather than from Access -- maybe it's force of habit, but it works...
In Excel, place this code in to a new module.
Set a reference to the Microsoft ActiveX Data Objects 2.8 Library ( or the highest 2.x in your list)
Adjust the table name and the MyConnn string that defines the source database.
Run the code; you will create a series of sheets numbered Data_1 to Data_n, each with 65K rows of data.

Sub TransferTableFromAccess()
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field
   Dim MyConn
   Dim i As Long, j As Long
   Dim ShDest As Worksheet
   Const SOURCE_TABLE = "tblMain"
   Set cnn = New ADODB.Connection
   MyConn = "C:\Test\DB1.mdb"
   With cnn
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Open MyConn
   End With
   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   rst.Open Source:=SOURCE_TABLE, _
            ActiveConnection:=cnn, _
            CursorType:=adOpenDynamic, _
            LockType:=adLockOptimistic, _
            Options:=adCmdTable
   j = 0
   Do While Not rst.EOF
       j = j + 1
       Set ShDest = Worksheets.Add
       ActiveSheet.Name = "Data_" & j
 
       'create field headers
       i = 0
       With Range("A1")
         For Each fld In rst.Fields
          .Offset(0, i).Value = fld.Name
          i = i + 1
        Next fld
       End With
 
       'transfer data to Excel
       Range("A2").CopyFromRecordset rst, 65000
   Loop
 
   ' Close the connection and clean up references
   rst.Close
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub

Denis

 

[brites]

Perfect!
Thanx it works beautifully

 

[#BoB#]

Hi,

I have a similar problem as discussed in this thread. The only difference is I am trying to pass a query to fetch the data instead of copying the entire table.

The query is:
sSQL = "Select Client+, Entry-ID, Type of Contact, Manual Creation, Date Created, Transfer-date, Respond SLA FROM BASE DATA"

The problem I am facing is in:

rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdText

The error I am getting is:

Run-Time error '-2147........'

Method 'Open' of Object '_Recordset' failed


I am new to database connectivity. Any help will be greately appreciated.


~Cheers,

BoB

 

[james_lankford]

did you set the reference as Sydney said ?
and declare all your objects with the ADODB. prefix ?

it is very important to say
dim rst as ADODB.recordset
instead of
dim rst as recordset

and you shou;d probably put square brackets around field names that have spaces or punctuation in them
[Client+], [Entry-ID], [Type of Contact], and so on

 

[#BoB#]

Hi,

The references and declarations are perfect.

I think the problem is with the fieldnames....as you said.

Will put square brackets around them and see if it works.


Thanks,

BoB

 

[Raysoc]

Sorry to bring this back up but I cant find out how to reference the Active X library. So this code crashes on the first line every time

 

[SydneyGeek]

This should help

Denis

 

[jjm3066]

I need some help on this macro. Here is what I have: its getting hung up where it is pink.

Sub TransferTableFromAccess()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim MyConn
Dim i As Long, j As Long
Dim ShDest As Worksheet
Const SOURCE_TABLE = "Kroger POS"
Set cnn = New ADODB.Connection
MyConn = "[S:\Data Management\Kroger\Market 6 Data\Access Databases\Kroger Data - 2011.mdb]"
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=SOURCE_TABLE, _
ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
j = 0
Do While Not rst.EOF
j = j + 1
Set ShDest = Worksheets.Add
ActiveSheet.Name = "Data_" & j

'create field headers
i = 0
With Range("A1")
For Each fld In rst.Fields
.Offset(0, i).Value = fld.Name
i = i + 1
Next fld
End With

'transfer data to Excel
Range("A2").CopyFromRecordset rst, 65000
Loop

' Close the connection and clean up references
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub