|
|
|
date: Wed, 1 Mar 2006 11:56:29 -0800,
group: microsoft.public.excel.querydao
back
Re: MS Query from Excel 2002 to Access 2002
David:
There are two QueryTable properties you need to check. Open the VBE
(Alt+F11) and the Immediate Window (Ctl+G). In the IW, type
?Sheet1.QueryTables(1).Connection
?Sheet1.QueryTables(1).CommandText
CommandText is the SQL statement, so it will probably show the UNC path
since you've seen that it does in MSQuery. Connection is the connection
stirng and may be where the mapped drive path is and is causing the problem.
When you crete the query in Excel, I think it stores the path in the
connection string based on how you navigate to the database. If you use
your mapped drive, that's what it will store. If you navigate through
MyNetworkPlaces and go through the network share, it will store the UNC
path. I'm not 100% sure about that, but I think that's how it works.
If you're comfortable with VBA, you can change the connection string right
in the Immediate Window. You could use a statement like
Sheet1.QueryTables(1).Connection = Replace(Sheet1.QueryTables(1).Connection,
"E:\", \\server\share\)
using your specific data.
If you're not comfortable with VBA, and I'm right about how it stores the
path, you can delete your query and recreate it navigating the appropriate
way. You can read more about external data here
http://www.dicks-clicks.com/excel/ExternalData.htm
Dick
David wrote:
> hello all,
> Not sure if this the right forum, if not please direct me to the
> proper user group for Excel and Access automation.
>
> I am trying to build a data query in Excel 2002 to fetch values in
> Access 2002 backend database. I have Microsoft Office 10.0 Object
> Library.
> I'm actually somewhat successful, with one small glitch. I need to
> use the full name of the access database location of
> \\pkserver01\team\database_be.mdb versus e:\database_be.mdb. The
> user is getting an error message of e:\database_be.mdb not
> available... because their e drive is not mapped to the same server
> as my e drive. I do not get the error message.
>
> In excel i click on a cell in the data, choose data-->Imort External
> Data-->Edit query. I get a popup box that says "This query cannot be
> edited by the query wizard."
> I click ok, then click on the SQL button and can view the sql. The
> sql statement shows the full database location of
> \\pkserver01\team\database_be.mdb
>
> Where else do I need to look try and fix this problem?
date: Thu, 2 Mar 2006 08:06:02 -0600
author: Dick Kusleika
Re: MS Query from Excel 2002 to Access 2002
Dick,
Thank you! You are corect, the path you take to select the data source when
creating your query, is stored somewhere that I could not access.
The fix, When creating the query, I clicked the NETWORK button, then under
drive choose NONE and then manually entered the UNC path.
"Dick Kusleika" wrote:
> David:
>
> There are two QueryTable properties you need to check. Open the VBE
> (Alt+F11) and the Immediate Window (Ctl+G). In the IW, type
>
> ?Sheet1.QueryTables(1).Connection
> ?Sheet1.QueryTables(1).CommandText
>
> CommandText is the SQL statement, so it will probably show the UNC path
> since you've seen that it does in MSQuery. Connection is the connection
> stirng and may be where the mapped drive path is and is causing the problem.
> When you crete the query in Excel, I think it stores the path in the
> connection string based on how you navigate to the database. If you use
> your mapped drive, that's what it will store. If you navigate through
> MyNetworkPlaces and go through the network share, it will store the UNC
> path. I'm not 100% sure about that, but I think that's how it works.
>
> If you're comfortable with VBA, you can change the connection string right
> in the Immediate Window. You could use a statement like
>
> Sheet1.QueryTables(1).Connection = Replace(Sheet1.QueryTables(1).Connection,
> "E:\", \\server\share\)
>
> using your specific data.
>
> If you're not comfortable with VBA, and I'm right about how it stores the
> path, you can delete your query and recreate it navigating the appropriate
> way. You can read more about external data here
> http://www.dicks-clicks.com/excel/ExternalData.htm
>
> Dick
>
> David wrote:
> > hello all,
> > Not sure if this the right forum, if not please direct me to the
> > proper user group for Excel and Access automation.
> >
> > I am trying to build a data query in Excel 2002 to fetch values in
> > Access 2002 backend database. I have Microsoft Office 10.0 Object
> > Library.
> > I'm actually somewhat successful, with one small glitch. I need to
> > use the full name of the access database location of
> > \\pkserver01\team\database_be.mdb versus e:\database_be.mdb. The
> > user is getting an error message of e:\database_be.mdb not
> > available... because their e drive is not mapped to the same server
> > as my e drive. I do not get the error message.
> >
> > In excel i click on a cell in the data, choose data-->Imort External
> > Data-->Edit query. I get a popup box that says "This query cannot be
> > edited by the query wizard."
> > I click ok, then click on the SQL button and can view the sql. The
> > sql statement shows the full database location of
> > \\pkserver01\team\database_be.mdb
> >
> > Where else do I need to look try and fix this problem?
>
>
>
date: Thu, 2 Mar 2006 10:15:27 -0800
author: David
|
|
