Excel limitted to 50 Fields with transfer from Access Query?

[mdavidge]

Hello,

I have an Access Query with 920 rows of information and 146 fields / columns. It appears that Excel is limitted to no more than 50 Fields for automated transfer of information from an Access Query Excel. This is not the case when transfering Access Tables to Excel. I receive error messages when using Import External Data or ADO code.

When using Import External Data, I receive the following error. "Data could not be retrieved from the database. Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again."

When running the ADO code below, I receive the following message with 51+ fields in a Query. "&H80004005(-2147467259)". This code works very well to import tables and queries. When I place the same information from the query into a table, this code transfers the information perfectly. This issue appears to be a limitation on Access Query fields that can be dealt with.

Sub AGetFinalReport()

'Only returns 50 fields from Access Query ZFINAL.  Will return 50 and less Fields wo issues from Access Query.
'Will return all (160 Fields?) from Table with same info.
   'Create a Recordset from all the records in the ZFINAL Query.
   Dim sNWind As String
   Dim conn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   sNWind = _
      "C:\SALES REPORT CRUNCHER.mdb"
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
   conn.CursorLocation = adUseClient
   Set rs = conn.Execute("ZFINAL", , adCmdTable)

   'Transfer the data to Excel
   ActiveSheet.Range("A2").CopyFromRecordset rs

   'Close the connection
   rs.Close
   conn.Close


End Sub
 

Is anyone aware of this limitation of 50 fields for Access Queries? Is there any suggestions for an automated work around for the issue? Running a Make Table query is manual for updating with all those pesky warning windows. 

Thanks in advance, 
Mike

 

[Norie]

Mike

Have you tried using TransferSpreadsheet from Access?

I've just done some testing and it worked no problem.

Also you can turn off those alerts in code.

By the way why do you have so many fields.

The only databases I've seen with that amount of fields have been legacy databases.

 

[mdavidge]

Hi Norie,

I recognize you from another post of mine. I beleive you are refering to code in Access? I only have experience patching googled Excel code in Excel.

Do you have a sample of such code and how I would link to a particular Exel file?

Thanks,
Mike

 

[mdavidge]

Norie,

To answer your questions:

Why so many fields? This is a sales report. There are roughly 920 accounts (Rows) and roughly 40 products and/or product groupings each with Current Year Sales, Prior Year Sales, $CHG Sales and %CHG of sales.

How do you turn off the alerts in code? Are the alerts not helpful?

Thanks,
Mike

 

[Norie]

Mike

Just goto the Access VBA help and look up TransferSpreadsheet, it's pretty self-explanatory.

It sounds to me as though your database isn't structured very well.

Do you have fields in a table for every different product?

Or are the multiple fields the result of a query?

The comment about turning off alerts was in regard to your mention of making tables.

I suppose how helpful the alerts are depends on what you are actually doing.

If you are trying to fully automate a process using code they could just get in the way.

 

[mdavidge]

I have since figured out that the issue is not a limitation on Query fields. It is a limitation on Query Fields that have been created through Expressions. It appears that you cannot get more than 12 of these in one transfer of information.

The %CHG fields have expressions like below.

IIf([PY SALES]Is Null,1,[$CHG SALES]/[PY SALES])

I am able to tranfer 11 of these fields but not 12 or more.

Mike

Mike

 

[mdavidge]

I am stupid. I just found that the expression in field 12 generated one #Error for a result. The error was caused by a Null value in division.

Thanks for the help. The query now populates properly to Excel.

Mike