Connecting Excel, Access and SQL Server

G

Guest

I have a Access database connected to a SQL server. When I connect to the
Access database it requests the username and password to the SQL server.
Access is used to do queries that SQL Server cannot (we have a older
version). I added a link to Excel to create my charts (excel cannot handle
the full queries Access can). When I link Excel to Access to get my data,
any query or report that has a table that is linked to the SQL server I get
the following error: ODBC - connection to "[My SQL SERVER NAMEJ" failed.
Now, if I use a query for a table that I created that is not linked to the
SQL Server it links perfectly and I can refresh fine. Any ideas?
I connect by data - import external data - New database query Thanks!
 
M

MH

This is really an Access question...so here's the Access answer:

When you use linked tables in Access, tick the checkbox that says something
like "Remember Password" and guess what? You guessed it, Access remembers
the password so you don't have to supply it.

MH
Access is used to do queries that SQL Server cannot (we have a older
version).

Just out of interest, what version of SQL Server are you running that Access
is used to do queries it cannot? What type of queries?

mp80237 said:
I have a Access database connected to a SQL server. When I connect to the
Access database it requests the username and password to the SQL server.
Access is used to do queries that SQL Server cannot (we have a older
version). I added a link to Excel to create my charts (excel cannot
handle
the full queries Access can). When I link Excel to Access to get my data,
any query or report that has a table that is linked to the SQL server I
get
the following error: ODBC - connection to "[My SQL SERVER NAMEJ" failed.
Now, if I use a query for a table that I created that is not linked to the
SQL Server it links perfectly and I can refresh fine. Any ideas?
I connect by data - import external data - New database query Thanks!
 
G

Guest

It never asks for my password in Excel. I think that is the problem is.
When I open the query in Access (which is linked from a table from the SQL
server), I get LoginID and password field. There is not a checkbox field to
remember password. When I link that query in Excel, I don't get a dialog
box. Only a error. I believe that is where the problem lies. I need a
dialog box to enter the username and password in Excel and have the option to
remember the password. Is there something in Microsoft Query (what is
grabbing the query from Access) or in Excel that needs to be reset so I am
given a dialog box?

Thanks! MP

MH said:
This is really an Access question...so here's the Access answer:

When you use linked tables in Access, tick the checkbox that says something
like "Remember Password" and guess what? You guessed it, Access remembers
the password so you don't have to supply it.

MH
Access is used to do queries that SQL Server cannot (we have a older
version).

Just out of interest, what version of SQL Server are you running that Access
is used to do queries it cannot? What type of queries?

mp80237 said:
I have a Access database connected to a SQL server. When I connect to the
Access database it requests the username and password to the SQL server.
Access is used to do queries that SQL Server cannot (we have a older
version). I added a link to Excel to create my charts (excel cannot
handle
the full queries Access can). When I link Excel to Access to get my data,
any query or report that has a table that is linked to the SQL server I
get
the following error: ODBC - connection to "[My SQL SERVER NAMEJ" failed.
Now, if I use a query for a table that I created that is not linked to the
SQL Server it links perfectly and I can refresh fine. Any ideas?
I connect by data - import external data - New database query Thanks!
 
M

MH

So, you have a query in Access which refers to one or more linked tables
within Access?


mp80237 said:
It never asks for my password in Excel. I think that is the problem is.
When I open the query in Access (which is linked from a table from the SQL
server), I get LoginID and password field. There is not a checkbox field
to
remember password. When I link that query in Excel, I don't get a dialog
box. Only a error. I believe that is where the problem lies. I need a
dialog box to enter the username and password in Excel and have the option
to
remember the password. Is there something in Microsoft Query (what is
grabbing the query from Access) or in Excel that needs to be reset so I am
given a dialog box?

Thanks! MP

MH said:
This is really an Access question...so here's the Access answer:

When you use linked tables in Access, tick the checkbox that says
something
like "Remember Password" and guess what? You guessed it, Access
remembers
the password so you don't have to supply it.

MH
Access is used to do queries that SQL Server cannot (we have a older
version).

Just out of interest, what version of SQL Server are you running that
Access
is used to do queries it cannot? What type of queries?

mp80237 said:
I have a Access database connected to a SQL server. When I connect to
the
Access database it requests the username and password to the SQL
server.
Access is used to do queries that SQL Server cannot (we have a older
version). I added a link to Excel to create my charts (excel cannot
handle
the full queries Access can). When I link Excel to Access to get my
data,
any query or report that has a table that is linked to the SQL server I
get
the following error: ODBC - connection to "[My SQL SERVER NAMEJ"
failed.
Now, if I use a query for a table that I created that is not linked to
the
SQL Server it links perfectly and I can refresh fine. Any ideas?
I connect by data - import external data - New database query Thanks!
 
G

Guest

The queries in Access refer to the linked tables from the SQL server. I am
able to access those queries perfectly. When I open a query in Access I get
a dialog box with a username and password - no checkbox to remember password.
That username and password is the username and password to connect to the
SQL Server - ODBC. So I enter it and the query comes up perfectly. Now,
when I am trying to LINK the queries I created in ACCESS into Excel it does
not ask me for a username and password, I only receive a error"ODBC -
connection to [My SQL SERVER NAME] failed." That is pulled up by Microsoft
Query. Could it be a DCN issue?

MH said:
So, you have a query in Access which refers to one or more linked tables
within Access?


mp80237 said:
It never asks for my password in Excel. I think that is the problem is.
When I open the query in Access (which is linked from a table from the SQL
server), I get LoginID and password field. There is not a checkbox field
to
remember password. When I link that query in Excel, I don't get a dialog
box. Only a error. I believe that is where the problem lies. I need a
dialog box to enter the username and password in Excel and have the option
to
remember the password. Is there something in Microsoft Query (what is
grabbing the query from Access) or in Excel that needs to be reset so I am
given a dialog box?

Thanks! MP

MH said:
This is really an Access question...so here's the Access answer:

When you use linked tables in Access, tick the checkbox that says
something
like "Remember Password" and guess what? You guessed it, Access
remembers
the password so you don't have to supply it.

MH

Access is used to do queries that SQL Server cannot (we have a older
version).

Just out of interest, what version of SQL Server are you running that
Access
is used to do queries it cannot? What type of queries?

I have a Access database connected to a SQL server. When I connect to
the
Access database it requests the username and password to the SQL
server.
Access is used to do queries that SQL Server cannot (we have a older
version). I added a link to Excel to create my charts (excel cannot
handle
the full queries Access can). When I link Excel to Access to get my
data,
any query or report that has a table that is linked to the SQL server I
get
the following error: ODBC - connection to "[My SQL SERVER NAMEJ"
failed.
Now, if I use a query for a table that I created that is not linked to
the
SQL Server it links perfectly and I can refresh fine. Any ideas?
I connect by data - import external data - New database query Thanks!
 
M

MH

If you have a linked table within Access (a table which appears in Access
with a little arrow by it which is basically a pointer to a table stored
elsewhere i.e. SQL Server), then you should re-link them.

Right click anywhere within the white space in the Tables area.
Select "Link Tables..."
"Files of Type" should point to ODBC Data Sources
Select the tables you wish to re-link and check the box which says "Remember
password"

Now you can run your queries without having to supply the username and
password in Access or any other application.

MH

mp80237 said:
The queries in Access refer to the linked tables from the SQL server. I
am
able to access those queries perfectly. When I open a query in Access I
get
a dialog box with a username and password - no checkbox to remember
password.
That username and password is the username and password to connect to the
SQL Server - ODBC. So I enter it and the query comes up perfectly. Now,
when I am trying to LINK the queries I created in ACCESS into Excel it
does
not ask me for a username and password, I only receive a error"ODBC -
connection to [My SQL SERVER NAME] failed." That is pulled up by
Microsoft
Query. Could it be a DCN issue?

MH said:
So, you have a query in Access which refers to one or more linked tables
within Access?


mp80237 said:
It never asks for my password in Excel. I think that is the problem
is.
When I open the query in Access (which is linked from a table from the
SQL
server), I get LoginID and password field. There is not a checkbox
field
to
remember password. When I link that query in Excel, I don't get a
dialog
box. Only a error. I believe that is where the problem lies. I need
a
dialog box to enter the username and password in Excel and have the
option
to
remember the password. Is there something in Microsoft Query (what is
grabbing the query from Access) or in Excel that needs to be reset so I
am
given a dialog box?

Thanks! MP

:

This is really an Access question...so here's the Access answer:

When you use linked tables in Access, tick the checkbox that says
something
like "Remember Password" and guess what? You guessed it, Access
remembers
the password so you don't have to supply it.

MH

Access is used to do queries that SQL Server cannot (we have a older
version).

Just out of interest, what version of SQL Server are you running that
Access
is used to do queries it cannot? What type of queries?

I have a Access database connected to a SQL server. When I connect
to
the
Access database it requests the username and password to the SQL
server.
Access is used to do queries that SQL Server cannot (we have a older
version). I added a link to Excel to create my charts (excel cannot
handle
the full queries Access can). When I link Excel to Access to get my
data,
any query or report that has a table that is linked to the SQL
server I
get
the following error: ODBC - connection to "[My SQL SERVER NAMEJ"
failed.
Now, if I use a query for a table that I created that is not linked
to
the
SQL Server it links perfectly and I can refresh fine. Any ideas?
I connect by data - import external data - New database query
Thanks!
 
G

Guest

Hello,

Once I deleted Server from my System DSN and reconnected it, I was given the
option to remember the password. Thank you so much for your help. It is
working wonderfuly now.

Thank you!

MH said:
If you have a linked table within Access (a table which appears in Access
with a little arrow by it which is basically a pointer to a table stored
elsewhere i.e. SQL Server), then you should re-link them.

Right click anywhere within the white space in the Tables area.
Select "Link Tables..."
"Files of Type" should point to ODBC Data Sources
Select the tables you wish to re-link and check the box which says "Remember
password"

Now you can run your queries without having to supply the username and
password in Access or any other application.

MH

mp80237 said:
The queries in Access refer to the linked tables from the SQL server. I
am
able to access those queries perfectly. When I open a query in Access I
get
a dialog box with a username and password - no checkbox to remember
password.
That username and password is the username and password to connect to the
SQL Server - ODBC. So I enter it and the query comes up perfectly. Now,
when I am trying to LINK the queries I created in ACCESS into Excel it
does
not ask me for a username and password, I only receive a error"ODBC -
connection to [My SQL SERVER NAME] failed." That is pulled up by
Microsoft
Query. Could it be a DCN issue?

MH said:
So, you have a query in Access which refers to one or more linked tables
within Access?


It never asks for my password in Excel. I think that is the problem
is.
When I open the query in Access (which is linked from a table from the
SQL
server), I get LoginID and password field. There is not a checkbox
field
to
remember password. When I link that query in Excel, I don't get a
dialog
box. Only a error. I believe that is where the problem lies. I need
a
dialog box to enter the username and password in Excel and have the
option
to
remember the password. Is there something in Microsoft Query (what is
grabbing the query from Access) or in Excel that needs to be reset so I
am
given a dialog box?

Thanks! MP

:

This is really an Access question...so here's the Access answer:

When you use linked tables in Access, tick the checkbox that says
something
like "Remember Password" and guess what? You guessed it, Access
remembers
the password so you don't have to supply it.

MH

Access is used to do queries that SQL Server cannot (we have a older
version).

Just out of interest, what version of SQL Server are you running that
Access
is used to do queries it cannot? What type of queries?

I have a Access database connected to a SQL server. When I connect
to
the
Access database it requests the username and password to the SQL
server.
Access is used to do queries that SQL Server cannot (we have a older
version). I added a link to Excel to create my charts (excel cannot
handle
the full queries Access can). When I link Excel to Access to get my
data,
any query or report that has a table that is linked to the SQL
server I
get
the following error: ODBC - connection to "[My SQL SERVER NAMEJ"
failed.
Now, if I use a query for a table that I created that is not linked
to
the
SQL Server it links perfectly and I can refresh fine. Any ideas?
I connect by data - import external data - New database query
Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top