Importing Data from Access to Excel

[dnnr]

I have a query I want to import into Excel. I can run it just fun in Access 2003, but when I try to import into the spreasheet, it gives me this message:

[Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error.

Any ideas?

 

[boblarson]

A lot more information would be good. I'm not trying to be rude, but your question is much like saying, "I can't start my car, what am I doing wrong?" Well, I could not be using the key, not have gas in it, it might be 40 below outside, I might have just gone through 4 feet of water, etc.

Without providing the steps that I've gone through, it is very hard to say why I can't start my car. So, is the same is with your question.

 

[dnnr]

Fair enough, should have thought of that myself. I just assumed someone would "google" me the answer based on the error message (which I had no success doing so this makes sense) . . .

The data I am trying to import into my Excel sheet is on a shared drive. I would detail the process I go through in Excel, but I'm fairly confident there is no problem there because I can import data from different queries in the same database.

The information I query might be the problem. Basically, it is a querythat totals how many hours a project was worked on. The table I am running the query from contains a row EVERY TIME an employee entered hours and assigned those hours to a project. I would like each project listed with the sum of the hours. The query runs fine in access, but here is a description of the three data columns I'm working with:

1st column - a number unique to every project (some projects do not have a number but I have put a criteria in here to exclude them considering I can't gather meaningful data from them anyway)

2nd column - SumOfHours - prior to summing these hours, the 1st column contained duplicate project records. This consolidates these duplicate records into one row and gives a TOTAL amount of hours.

3rd column - projected hours. How much we estimated.

And that's it. It seems pretty straightforward, but unfortunately is not cooperating.

 

[dnnr]

Alright, did a little more trouble shooting.

The only problem that exists is the projected hours column. I can get the other columns to import just fine. I even tried to import JUST projected hours without any joints to other tables or queries without success.

I don't know why this particular piece of data is bad, because I'm getting it from a query where I can import other data easily. I can even run the query and get projected hours without a hitch in access. I just can't get it over to excel.

What a peculiar problem . . .

 

[dnnr]

SOLVED IT (well, my manager solved it)

It couldn't import the data from Access to Excel because there were a ton of null values that, I guess, "tripped" the computer up. In other words, it didn't know exactly what to do with them. So, I created a new column in access that changed the null values to zeros. Now it imports just fine. Here's a sample:

Projected Hours: IIf(IsNull([Projected Hours]),0,[Projected Hours])

I hope this can be of use to someone else.

 

[boblarson]

Good Job! Glad to hear you got it sorted. Sorry I couldn't respond sooner. I have started a new job as an actual Access developer and, for some reason, my attention has been on my programming.

 

[Ron_dK]

....and, for some reason, my attention has been on my programming.

Shame on you Mr. Larson, that will cost you points from your reputation