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]
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
