Excel 2002 - 50 field max Transfering Access Query to Excel?

[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()

'Code errors on Access Query with more than 50 fields. Code works fine on Access Tables with 146 fields - no errors.
'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 [/code]

 

[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.

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