Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?

Sounds like you may not have "rights" to the channel or the data.....check
with you MIS to see if you do............if you had then some time ago, maybe
the path has been changed.........

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?

Would this apply if the query runs via MS-Access?

"CLR" wrote:

Sounds like you may not have "rights" to the channel or the data.....check
with you MIS to see if you do............if you had then some time ago, maybe
the path has been changed.........

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?

I just noticed that when I run the query right in MS-Access I get the same
message but I'm prompted to enter a login ID and password. When trying to
run the query from Excel the difference is that I'm not prompted. If I could
get this to work it would make life much easier.

"CLR" wrote:

Sounds like you may not have "rights" to the channel or the data.....check
with you MIS to see if you do............if you had then some time ago, maybe
the path has been changed.........

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?

It sounds like you have an account set up authorizing you to use the path
from Access, but need a password, but do not have an account allowing you to
use Excel....I would check with your MIS dept, and/or administrator..

My account is set for only me to use and only from this one computer.....

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

I just noticed that when I run the query right in MS-Access I get the same
message but I'm prompted to enter a login ID and password. When trying to
run the query from Excel the difference is that I'm not prompted. If I could
get this to work it would make life much easier.

"CLR" wrote:

Sounds like you may not have "rights" to the channel or the data.....check
with you MIS to see if you do............if you had then some time ago, maybe
the path has been changed.........

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?

How would I or our I.S. guy be able to tell this?

"CLR" wrote:

It sounds like you have an account set up authorizing you to use the path
from Access, but need a password, but do not have an account allowing you to
use Excel....I would check with your MIS dept, and/or administrator..

My account is set for only me to use and only from this one computer.....

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

I just noticed that when I run the query right in MS-Access I get the same
message but I'm prompted to enter a login ID and password. When trying to
run the query from Excel the difference is that I'm not prompted. If I could
get this to work it would make life much easier.

"CLR" wrote:

Sounds like you may not have "rights" to the channel or the data.....check
with you MIS to see if you do............if you had then some time ago, maybe
the path has been changed.........

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?

YOU can't tell, except by being allowed or denied.....there are no error
messages that I am aware of. Your MIS guy should be able to check to see
what the account is set up for, or if it is set up at all, and be able to
make any alterations you might need.

Vaya con Dios,
Chuck, CABGx3


"Jim Moberg" wrote:

How would I or our I.S. guy be able to tell this?

"CLR" wrote:

It sounds like you have an account set up authorizing you to use the path
from Access, but need a password, but do not have an account allowing you to
use Excel....I would check with your MIS dept, and/or administrator..

My account is set for only me to use and only from this one computer.....

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

I just noticed that when I run the query right in MS-Access I get the same
message but I'm prompted to enter a login ID and password. When trying to
run the query from Excel the difference is that I'm not prompted. If I could
get this to work it would make life much easier.

"CLR" wrote:

Sounds like you may not have "rights" to the channel or the data.....check
with you MIS to see if you do............if you had then some time ago, maybe
the path has been changed.........

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?

I had one of our I.T. guys look into this and he seemed puzzled as to why it
would be looking for the ODBC driver. The ODBC driver links to our SQL
server. Do I need to set up a new ODBC driver for Excel to link to Access?

"Jim Moberg" wrote:

How would I or our I.S. guy be able to tell this?

"CLR" wrote:

It sounds like you have an account set up authorizing you to use the path
from Access, but need a password, but do not have an account allowing you to
use Excel....I would check with your MIS dept, and/or administrator..

My account is set for only me to use and only from this one computer.....

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

I just noticed that when I run the query right in MS-Access I get the same
message but I'm prompted to enter a login ID and password. When trying to
run the query from Excel the difference is that I'm not prompted. If I could
get this to work it would make life much easier.

"CLR" wrote:

Sounds like you may not have "rights" to the channel or the data.....check
with you MIS to see if you do............if you had then some time ago, maybe
the path has been changed.........

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?

I forgot to mention that what I'm trying to do here is have Excel as a front
end to Access. My thinking here is that when I refresh the data in Excel
from Access the Access query will run and all of the data will come into
Excel automatically. Is this even possible?

"Jim Moberg" wrote:

I had one of our I.T. guys look into this and he seemed puzzled as to why it
would be looking for the ODBC driver. The ODBC driver links to our SQL
server. Do I need to set up a new ODBC driver for Excel to link to Access?

"Jim Moberg" wrote:

How would I or our I.S. guy be able to tell this?

"CLR" wrote:

It sounds like you have an account set up authorizing you to use the path
from Access, but need a password, but do not have an account allowing you to
use Excel....I would check with your MIS dept, and/or administrator..

My account is set for only me to use and only from this one computer.....

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

I just noticed that when I run the query right in MS-Access I get the same
message but I'm prompted to enter a login ID and password. When trying to
run the query from Excel the difference is that I'm not prompted. If I could
get this to work it would make life much easier.

"CLR" wrote:

Sounds like you may not have "rights" to the channel or the data.....check
with you MIS to see if you do............if you had then some time ago, maybe
the path has been changed.........

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?

I'm not sure Jim.........my only experience with MSQuery is to bring data in
to Excel from either a database on the company main computer or from Access.
I've never tried to get Excel to get Access to get MSQuery to bring in info
that way........why not just bring the data straight in to Excel.......why go
through Access?

Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

I forgot to mention that what I'm trying to do here is have Excel as a front
end to Access. My thinking here is that when I refresh the data in Excel
from Access the Access query will run and all of the data will come into
Excel automatically. Is this even possible?

"Jim Moberg" wrote:

I had one of our I.T. guys look into this and he seemed puzzled as to why it
would be looking for the ODBC driver. The ODBC driver links to our SQL
server. Do I need to set up a new ODBC driver for Excel to link to Access?

"Jim Moberg" wrote:

How would I or our I.S. guy be able to tell this?

"CLR" wrote:

It sounds like you have an account set up authorizing you to use the path
from Access, but need a password, but do not have an account allowing you to
use Excel....I would check with your MIS dept, and/or administrator..

My account is set for only me to use and only from this one computer.....

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

I just noticed that when I run the query right in MS-Access I get the same
message but I'm prompted to enter a login ID and password. When trying to
run the query from Excel the difference is that I'm not prompted. If I could
get this to work it would make life much easier.

"CLR" wrote:

Sounds like you may not have "rights" to the channel or the data.....check
with you MIS to see if you do............if you had then some time ago, maybe
the path has been changed.........

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?

Thanks for sticking with me on this Chuck. Here is why I'm so bent on
getting this to work. Right now I have a pivot table set up that's getting
data from another worksheet in the workbook. Depending on the range of dates
I use the amount of data can exceed the number of available rows Excel has.
This also causes the spread sheets to be quite large. I'm thinking that if I
could have the pivot table just read the results of the Access query right
from Access then this would alleviate this one issue.

I have been working with MS-query and found that it doesn't have all of the
functionality of MS-Access. For instance on one field in the Access query I
have an alias set up with an IF..THEN statement to determine the value based
on two other fields in the database. I for the life of me can't get this to
work in MS-Query.

Any help/direction you can provide will be most appreciated. :-)

"CLR" wrote:

I'm not sure Jim.........my only experience with MSQuery is to bring data in
to Excel from either a database on the company main computer or from Access.
I've never tried to get Excel to get Access to get MSQuery to bring in info
that way........why not just bring the data straight in to Excel.......why go
through Access?

Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

I forgot to mention that what I'm trying to do here is have Excel as a front
end to Access. My thinking here is that when I refresh the data in Excel
from Access the Access query will run and all of the data will come into
Excel automatically. Is this even possible?

"Jim Moberg" wrote:

I had one of our I.T. guys look into this and he seemed puzzled as to why it
would be looking for the ODBC driver. The ODBC driver links to our SQL
server. Do I need to set up a new ODBC driver for Excel to link to Access?

"Jim Moberg" wrote:

How would I or our I.S. guy be able to tell this?

"CLR" wrote:

It sounds like you have an account set up authorizing you to use the path
from Access, but need a password, but do not have an account allowing you to
use Excel....I would check with your MIS dept, and/or administrator..

My account is set for only me to use and only from this one computer.....

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

I just noticed that when I run the query right in MS-Access I get the same
message but I'm prompted to enter a login ID and password. When trying to
run the query from Excel the difference is that I'm not prompted. If I could
get this to work it would make life much easier.

"CLR" wrote:

Sounds like you may not have "rights" to the channel or the data.....check
with you MIS to see if you do............if you had then some time ago, maybe
the path has been changed.........

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?

I don't do much with Pivot Tables, but Debra has a heap of informaton on her
site about them, including how to get data from Multiple Consolidation
Ranges........maybe some of that will help.

Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Thanks for sticking with me on this Chuck. Here is why I'm so bent on
getting this to work. Right now I have a pivot table set up that's getting
data from another worksheet in the workbook. Depending on the range of dates
I use the amount of data can exceed the number of available rows Excel has.
This also causes the spread sheets to be quite large. I'm thinking that if I
could have the pivot table just read the results of the Access query right
from Access then this would alleviate this one issue.

I have been working with MS-query and found that it doesn't have all of the
functionality of MS-Access. For instance on one field in the Access query I
have an alias set up with an IF..THEN statement to determine the value based
on two other fields in the database. I for the life of me can't get this to
work in MS-Query.

Any help/direction you can provide will be most appreciated. :-)

"CLR" wrote:

I'm not sure Jim.........my only experience with MSQuery is to bring data in
to Excel from either a database on the company main computer or from Access.
I've never tried to get Excel to get Access to get MSQuery to bring in info
that way........why not just bring the data straight in to Excel.......why go
through Access?

Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

I forgot to mention that what I'm trying to do here is have Excel as a front
end to Access. My thinking here is that when I refresh the data in Excel
from Access the Access query will run and all of the data will come into
Excel automatically. Is this even possible?

"Jim Moberg" wrote:

I had one of our I.T. guys look into this and he seemed puzzled as to why it
would be looking for the ODBC driver. The ODBC driver links to our SQL
server. Do I need to set up a new ODBC driver for Excel to link to Access?

"Jim Moberg" wrote:

How would I or our I.S. guy be able to tell this?

"CLR" wrote:

It sounds like you have an account set up authorizing you to use the path
from Access, but need a password, but do not have an account allowing you to
use Excel....I would check with your MIS dept, and/or administrator..

My account is set for only me to use and only from this one computer.....

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

I just noticed that when I run the query right in MS-Access I get the same
message but I'm prompted to enter a login ID and password. When trying to
run the query from Excel the difference is that I'm not prompted. If I could
get this to work it would make life much easier.

"CLR" wrote:

Sounds like you may not have "rights" to the channel or the data.....check
with you MIS to see if you do............if you had then some time ago, maybe
the path has been changed.........

hth
Vaya con Dios,
Chuck, CABGx3



"Jim Moberg" wrote:

Hi,

I'm trying to create a pivot table using data from an MS-Access query. I am
using the pivot table and pivot chart wizard. Here is what I am doing:

On step 1 I select "External data source".
Click on "Get Data"
I choose "MS-Access database*"
I choose the database I want to use.
I choose the query I want to use.
I don't want to add any filters or sorts because they are already in the
query so I click next to those prompts.
I leave the "Return data to microsoft excel" selected and click on finish.

I then get a message from Microsoft Query stating "ODBC connection to 'ODBC
connection name' failed."

What do I need to do to get this working?