Exporting data from Access to Excel programmatically 1

[glab]

Hello everyone,

I have a question for you. Let's say I have a table in Access database and it has 100,000 records. I'd like to export this table's data to Excel spreadsheet. Docmd object has methods/actions OutputTo and TransferSpreadsheet to help to do this. But Excel has limits the number of rows - 65536 - per worksheet. Is there a workaround to solve this problem?
Please, any info or pointers will be greatly appreciated.

Thank you in advance.

 

[SkipVought]

glab,

The first question that I would ask you is, how are you planning to use this data in Excel. There may be other ways of getting the data than stuffing ALL of it into Excel. After all, it's AVAILABLE to you in Access FROM Excel.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[SkipVought]

BTW,

My current project in Excel, as a reporting tool, is accessing ACCESS where I have tables of more than 150,000 rows. But I never need more than several thousand at a time.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[glab]

Hello SkipVought,

Thanks for your response. Unfortunately, I will need all the records at a time. I can do it by inserting record by record into worksheet, creating a new one each time the limit was reached, but it takes a lot of time. What I'm looking for is a way to speed this process up.

Thank you.

 

[SkipVought]

What are you doing with 100,000+ rows in Excel?

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[glab]

This - Excel file - is a format our client wants us to provide the data in. Nothing I can do about it. No comma-delimited .txt files is allowed. And no discussions, too. Client's always right!?

 

[SkipVought]

Well is the clien wants to stuff 5 pounds in a 4 pound bag, it ain't gonna work!

Part of a good analyst's job is to explain the pros and cons, advantages and limitations of various tools. Some are just not suitable.

But...

Here's what I would suggest. From EXCEL, using Data/Get External Data on several sheets..., query your Access Table with a suitable criteria, in order to import all the data in manageable chunks, labeling each tab appropriately.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[SkipVought]

Glab,

You might not like the answer, but it's the fact!

65,536 rows per sheet! Thats ALL you get!

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[glab]

Well, right you are.
But...
Not all the clients listen. Even to the best of analysts.
Thank you anyway for your suggestion.
I thought there might be something out there much like when you're archiving files and archive gets spanned by, say WinZip, over several disks.
Well, guess not in my case.
Have a great day.

 

[SkipVought]

I thought there might be something out there much like when you're archiving files and archive gets spanned by, say WinZip, over several disks.

Isn't that like storing the data in a workbook across several sheets?

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[pduncan]

I am using Access and Excel 2002 - trying to export a table to an excel ss by using

 DoCmd.RunSQL "UPDATE tblMaintenanceHistory SET tblMaintenanceHistory.MaintenanceDate = Date() where MaintenanceDescription = ""Export Regional Price List"";", 0

but when I do, access reports an error message saying too many lines - the only thing is that I am only exporting 18,340 records. Any ideas?

PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[SkipVought]

PDUNCAN,

Please post in a new thread.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[TopJack]

Glab,

Skip is correct in challenging your reasons. I have also come across clients that are very stubborn in there ways.

I've come across a similiar problem before. This VBA code I think will give you what you want. It uses a DAO (Data Access Objects) technique to retrieve the records and export to another application.

This code will require a reference to a "Microsoft DAO Object Library" (probably 3.51 or higher).

I've not had a lot of time to syntax the code. The code could be tidied up better to control the output - but the principal is correct. Hopefully this will give you some ideas anyway.

Good Luck.

Sub get_my_data()
'******************************************************************************
'short routine to use DAO sql statement to retrieve records from MS Access and export to MS Excel
'******************************************************************************
Dim path As String, db As Database, rs As Recordset
Dim sql As String, sql_select As String, sql_from As String
Dim oExcel As Object, oBook As Object, ws As Object
Dim output_row As Long

On Error GoTo error_handler 'trap any errors

path = "C:\Temp\mydb.mdb"   'declare your database
output_row = 2  'declare the starting row

'open a link to the Access database
Set db = Workspaces(0).OpenDatabase(path, dbDriverNoPrompt, ReadOnly:=True)

'******************************************************************************
'build up the SQL statement ready for use
'******************************************************************************
sql_select = "SELECT [MyTable].* "
sql_from = "FROM [MyTable];"

sql = sql_select & sql_from   'create the final sql statement ready for use

'******************************************************************************
'main routine to get the records
'******************************************************************************
Set rs = db.OpenRecordset(sql) 'run SQL statement to retrieve records

If (rs.RecordCount > 0) Then    'if records are found
    'Start a new workbook in Excel
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True

    oExcel.Workbooks.Add    'add a new workbook

    Set ws = oExcel.Worksheets.Add  'set up reference to a new sheet

    'create field headings across the worksheet
    For i = 0 To rs.Fields.Count - 1
        ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i

    'format the headings to bold font
    ws.Range(ws.Cells(1, 1), ws.Cells(1, rs.Fields.Count)).Font.Bold = True

    With rs 'use recordset
        Do While (Not .EOF) 'loop for complete recordset, ie all records
            
            If (output_row >= 65000) Then 'prevent sheet overload
                Set ws = oExcel.Worksheets.Add  'set up reference to a new sheet
                output_row = 1  'reset output row back to the beginning
            End If
            
            For i = 0 To rs.Fields.Count - 1    'loop for all fields
                ws.Cells(output_row, i + 1).Value = rs.Fields(i)   'send output to sheet
            Next i
            
            output_row = output_row + 1 'increment to next row
                        
            .MoveNext   'next record
        Loop
    End With
Else
    MsgBox ("No Records Have Been Returned")    'give user message that no records have been retrieved
End If

'close recordset and database link to prevent 'bloating'
rs.Close
db.Close

Exit Sub

'exit routine if error occurs
error_handler:
MsgBox ("An Error Has Occurred (Error: " & Err.Number & " in get_my_data)")

End Sub

 

[TopJack]

By The Way - Using DAO this way you need to have your SQL very accurate. VBA will not help you syntax your SQL statement. I would use you Access database to create the SQL statement for you (Create a query and show the sql behind it and copy it direct into your VB code).

Let me know if you still have a problem.