Cannot Import more than 11 Access Expression Fields to Excel

[mdavidge]

Hello,

I have an Access db with 60 Expression Fields like the one below.

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

Using the Import External Data function in Excel, I receive this error message. "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."

I can use the same function to bring 11 of these fields into Excel without error. The problem occurs with 12 or more.

If I run a make table query using the problem query fields, all of the fields from the make table query come in without error.

Are there some query tricks that I can use to create a query of values only and loose the Expression link in queries? I want an automated link and would like to avoid the make table query.

Thanks,
Mike

 

[Norie]

Mike

Could you give some more examples of the data/fields/expressions?

 

[mdavidge]

Norie,

They are all the same with the field names changing like below.

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

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

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

IIf([PY DIFF PROD1]Is Null,1,[$CHG DIFF PROD1]/[PY DIF PROD1])

All of the fields are calculating the percentage change from current year sales of a product from prior year sales of a product. The feilds are layed out as below.


{PY SALES}{CY SALES}{$CHG SALES}{%EXPESSION IIf([PY SALES]Is Null,1,[$CHG SALES]/[PY SALES])}

Does this help?

Mike

 

[Norie]

Mike

Like I asked in a previous post why do you have so many fields?

Do you have a field for each product?

That isn't a very good way to structure the data.

You should have 1 field for the data and 1 field that identifies the product the data is associated with.

If you can't change the data structure at source you can change it in Access.

 

[mdavidge]

Norie,

I start with that structure, but want a structure like below in Excel as a final result.

--------PY PROD1--CY PROD1--$CHG PROD1--%CHG PROD1--PY PROD2--CY PROD2--$CHG PROD2--%CHG PROD2--ETC....
CUST1
CUST2
CUST3
CUST4
CUST5
ETC...

Thanks,
Mike

 

[Norie]

You start with what structure?

Can't you just use the data form Access as the source for a pivot table in Excel?

Or create a crosstab query in Access?

 

[mdavidge]

Norie,

The data exists in Access exactly as I want to veiw it in Excel. This query has everything that I want. The problem is that I want the information returned to Excel as a datalink.

Queries without expressions return all the fields I want. How do work around the expression limitations?

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

 

[DimPasPivotTable]

Subtle cause for "Data could not be retrieved from the database"

I get the same error in Excel 2007 and fixed it the same way, but mine was more subtle so I would like to share it.

The Access query I was importing to Excel contained within it a main table outer-joined to a subquery (to show every record from the main table whether or not a record existed in the subquery.)

The subquery used the Trim$() function to format one of its fields (which I was displaying in the main query) and, although both the subquery and main query returned error-free result sets and imported to Excel just fine for a while, the eventual appearance of empty data in the main table - in the field which outer-joined to the subquery - caused the Trim$() function to raise the same Excel error message. ("Data could not be retrieved from the database")

Note that on inspection the main query was error-free (just had empty fields in some records as expected) and so was the subquery, which had no empty or null values.

I replaced Trim$() with Trim() (the variant version which can handle nulls) and the "could not be retrieved" error disappeared. So the outer join mechanism was still evaluating my Trim$() expression - with null data and silently raising an error - when it needn't have done any work, and the only sign of error was when it failed to import in Excel.

Hope this helps.