TwoHoot


I need to get a large number of Access 2000 Recordsets into Excel 2000 for evaluation. I have no problem setting up the connection and getting the entire table into Excel for testing by modifying the examples at

 http://support.microsoft.com/kb/247412/EN-US/

using either the QueryTable or ADO code outlined there.

The tables I need to access have over 200,000 records so getting the entire table is not an answer in this case. The SQL statement is complicated and changes 125 times in the course of the whole procedure.

Sample SQL generated by Microsoft Query by building the Query in design mode and then changing to View SQL (The Access Query SQL is slightly different it the query is built in Access):

SELECT tblCo.`Company Name`, tblCo.Symbol, tblCo.Edition, tblData.Date, tblData.Price, tblData.ProjHi, tblData.ProjLo, tblData.Performance, tblData.Timeliness, tblData.Safety, tblData.Technical, tblData.FS, tblData.PS, tblData.GP, tblData.EP, tblData.DeltaPrice, tblData.DeltaProjHi, tblData.DeltaProjLo, tblData.DeltaPerformance, tblData.DeltaTimeliness, tblData.DeltaSafety, tblData.DeltaTechnical, tblData.DeltaFS, tblData.DeltaPS, tblData.DeltaGP, tblData.DeltaEP, tblDeltaPrice.`1qDeltaPrice`, tblDeltaPrice.`1qDate`, tblCo.Symbol, tblData.DeltaTimeliness, tblData.DeltaTimeliness, tblData.DeltaPerformance  FROM `C:\Documents and Settings\jch\My Documents\YoYo\Dev\CritDev`.tblCo tblCo, `C:\Documents and Settings\jch\My Documents\YoYo\Dev\CritDev`.tblData tblData, `C:\Documents and Settings\jch\My Documents\YoYo\Dev\CritDev`.tblDeltaPrice tblDeltaPrice WHERE tblData.Symbol = tblCo.Symbol AND tblDeltaPrice.Symbol = tblCo.Symbol AND tblDeltaPrice.Symbol = tblData.Symbol AND  ((tblData.Date Between {ts '10/9/2003'} And {ts '10/9/2006'})  AND (tblDeltaPrice.`1qDate`=Date) AND (tblCo.Edition Like 'S%') AND (tblData.DeltaPerformance Is Null) AND (tblData.Performance Is Null))AND ((tblData.Timeliness)<3)AND ((tblData.Safety)<3)AND ((tblData.Technical)<3)

When I try to introduce this SQL statement into either procedure, it doesn't work.

Is the SQL incorrect or Am I missing something in changing the example VBA for my needs Am I even on the right track

My long suit is statistics, not programming, so if this is just a dumb question let me know. The frustrating thing is I can do it by hand but can't seem to automate it.

If some one will point me in the right direction or recommend some online or printed references, I would appreciate it.

Cordially,
TwoHoot

 




Re: Getting Access Data into Excel

TwoHoot


Maybe I have found the answer to my problem at

http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part3/ch15.mspx

About 80% of theway through this article, I found the following:

Defining the SQL Statement

If your data is coming from a relational data source that is properly normalized, you'll almost certainly have to define a query with multiple joined tables in order to get the data you want in the PivotTable report. By definition, a PivotTable report displays data from more than one table. For example, suppose you're creating a PivotTable report from the Northwind sample database to display information about sales by customer, customer city or customer country/region, and product or category. You'll need to create a query that includes the Customers, Products, Categories, and Order Details tables, at a minimum.

As noted earlier, lookup fields in an Access database can be problematic when you move data to Excel, because Excel displays only the stored numeric value, not the text value that appears in Access. In order to retrieve the text value, you must define a query containing inner joins between tables. If your query includes more than two tables, the SQL statement for such a query quickly becomes complex.

If the SQL statement has 255 or fewer characters, you can use it as-is as the second element of the two-dimensional array. If it's a longer string, however, you must either parse the string into 255-character segments, or create a saved query in the data source, if it supports saved queries, and base the SQL statement included in the array on that query.

If you choose to parse the SQL string into 255-character segments, you can simply add each segment as an additional element in the array, as long as the first element is the connection string. When you pass the array as the SourceData argument, Excel concatenates the strings together to form the complete SQL string. For example, the following line of code creates the array for the SourceData argument from four strings that include contiguous segments of the SQL statement:

varSource = Array(strConnect, strSQL1, strSQL2, strSQL3, strSQL4)

Parsing the SQL string into 255-character segments can be a hassle. If your data is in an Access database and you build the SQL statement in the Access query design grid, it's easiest to create a saved query in the database and use a simple SQL statement to retrieve data from that query. For example, the following code fragment creates a saved query based on a lengthy SQL string in an Access database. It then creates the two-dimensional array containing the connection string and a second SQL string that retrieves records from the saved query.

' Create new query in Northwind and append to Views collection. 
' This is one way to handle the problem of passing an SQL string 
' longer than 255 characters. 
' Open new ADO connection. 
Set cnnNwind = New ADODB.Connection 
cnnNwind.Open ADO_CONNECT_STRING 
Set catDb = New ADOX.Catalog 
' Open catalog on data source. 
Set catDb.ActiveConnection = cnnNwind 
' Create new command. 
Set cmdQuery = New ADODB.Command 
' Specify SQL string as command text. 
cmdQuery.CommandText = REGIONAL_SALES_SQL 
' Before appending new view, delete query with the 
' same name, if it exists. 
On Error Resume Next 
catDb.Views.Delete QUERY_NAME 
On Error GoTo 0 
' Append new view. 
catDb.Views.Append QUERY_NAME, cmdQuery 
' Create array containing connection string and SQL string. 
varSource = Array(ODBC_CONNECT_STRING, "SELECT * FROM " & QUERY_NAME) 
' Create PivotTable report. Start at cell B8 so there is enough room 
' for page fields. 
Set pvtTable = wksPivot.PivotTableWizard(xlExternal, varSource, _ 
wksPivot.Range("B8"))

This code fragment is taken from the CreatePivotTableFromMDB procedure in the modPivotTable module in the Northwind.xls sample file, which is available in the Samples\CH15 subfolder on the companion CD-ROM.

Is this likely to be what is wrong with my query

Cordially,
TwoHoot