Cannot Import more than 11 Access Expression Fields to Excel

mdavidge

Board Regular
Joined
Oct 14, 2005
Messages
87
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Mike

Could you give some more examples of the data/fields/expressions?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top