INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to quickly create a PivotTable from an external data source?

How to quickly create a PivotTable from an external data source?

(OP)
Hi,
The question is not as easy as it looks like.
I have a table in Access with about 300,000 rows and 150 columns.
I tried some methodologies of both DAO and ADO and it would take pretty long wait before the PTs to show up.
I found Asyncronous process from Paul Kimmel's 2003 VBA Programmer's Reference, which works like a background operation. But it won't work for me (the Debug.Print: Query Finished never showed so no way of checking if it's a faster process).
Thanks in advance.

Public Sub AsyncConnectionToDatabase()

 Const ConnectionString As String = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" + _
 "Data Source=C:\jqzhang\Novstuff\MA\linda\" + _
 "or_ods_unq.mdb;" + _
 "Persist Security Info=False"
                                                                                                                                        
 Set AsyncConnection = New ADODB.Connection
                                                                                                                                        
 AsyncConnection.ConnectionString = ConnectionString
 AsyncConnection.Open
                                                                                                                                        
 Const SQL As String = "SELECT * FROM or_ods "
                                                                                                                                        
 Call AsyncConnection.Execute(SQL, , CommandTypeEnum.adCmdText _
 Or ExecuteOptionEnum.adAsyncExecute)
                                                                                                                                        
 Debug.Print "I ran before the query finished"
 End Sub
                                                                                                                                        
 Private Sub AsyncConnection_ExecuteComplete( _
 ByVal RecordsAffected As Long, _
 ByVal pError As ADODB.Error, _
 adStatus As ADODB.EventStatusEnum, _
 ByVal pCommand As ADODB.Command, _
 ByVal pRecordset As ADODB.Recordset, _
 ByVal pConnection As ADODB.Connection)
                                                                                                                                        
 Debug.Print "Query finished"
                                                                                                                                        
 If (adStatus = EventStatusEnum.adStatusOK) Then
 Call Sheet1.Range("A1").CopyFromRecordset(pRecordset)
 End If
                                                                                                                                        
 If (pConnection.State = ObjectStateEnum.adStateOpen) Then
 pConnection.Close
 End If
 End Sub

 

RE: How to quickly create a PivotTable from an external data source?

The time the PT takes to load depends on the number of records that need to be summarized in your data source.
Summarizing your data in your DB prior to loading your PT will speed things up. Also limiting the size of the dataset will help, for example if you have data from 2000 to 2008 and you are only interested in the last 2 years then drop the older data.

RE: How to quickly create a PivotTable from an external data source?

(OP)
Hi,
Thanks for the tip. But the fact is that almost all the summarization has been done even before it comes to Access. The only reason why I used Access as my data format is Excel (before 2007) cannot handle 300,000 rows but Access can.
Thanks again.
John Z.

RE: How to quickly create a PivotTable from an external data source?





"The only reason why I used Access as my data format is Excel (before 2007)"

What???

I use PivotTables in Excel 2003 to summarize data from tables with MILLIONS of rows!  Typically, hunderds or thousands of rows are displayed in the PT.  Surely your PT is not returning 300,000 rows to Excel.

Using Excel, activate the PT Wizard and select External Data Source.  However, you must have configured a driver for the database you are accessing.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: How to quickly create a PivotTable from an external data source?

I think he means that prior to Excel 2007 you were limited to 65K rows in an Excel spreadsheet, not the number of records you can read into a PT.

If you want to speed things up then use MS SQL as the database, and SQL Analytical Services will make a huge difference.

RE: How to quickly create a PivotTable from an external data source?

(OP)
Hi,
Thanks folks. BTW, what's SQL Analytical Services? I'd never heard of it before. Please advise.
Thanks again.
 

RE: How to quickly create a PivotTable from an external data source?

It's part of MS SQL Server  2000, 2005 & 2008.
It allows you to build cubes, and you can use these cubes with pivot tables. Pivot tables are populated much faster from cubes since the number crunching is done by the cube.

RE: How to quickly create a PivotTable from an external data source?

(OP)
Thanks for the tip. But as far as I know, I don't have access to that application in the company I'm working for.
Thanks again for your help.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close