Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connecting Excel, Access and SQL Server
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connecting Excel, Access and SQL Server
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connecting Excel, Access and SQL Server
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" wrote: 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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connecting Excel, Access and SQL Server
So, you have a query in Access which refers to one or more linked tables
within Access? "mp80237" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connecting Excel, Access and SQL Server
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" wrote: So, you have a query in Access which refers to one or more linked tables within Access? "mp80237" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connecting Excel, Access and SQL Server
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" wrote in message ... 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" wrote: So, you have a query in Access which refers to one or more linked tables within Access? "mp80237" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connecting Excel, Access and SQL Server
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" wrote: 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" wrote in message ... 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" wrote: So, you have a query in Access which refers to one or more linked tables within Access? "mp80237" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Connecting Excel sheets to Access Tables??? | Excel Discussion (Misc queries) | |||
connecting to back end server | Excel Discussion (Misc queries) | |||
adodb connecting to a server issues | Excel Programming | |||
Runtime error 91 when connecting to SQL Server 2000 | Excel Programming | |||
Excel Connecting to SQL Server Database - Need your Help | Excel Programming |