select database file path location for excel query

I am using a program that will create an access table for estimating purposes
and I'm trying to create a change order template in excel with a query that
will import certain fields from the table. Each project will have its own
access database. What I am trying to achieve is to have the query prompt for
the database location. The location seems to be fixed. Can anyone help or
provide other alternatives. I know little about queries and even less about
editing the sql.

if you have a string like

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDB:Database Password=MyDbPassword", _
"myUsername", "myPassword"
you can incorporate a variable value with

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & MyVar & ";" & _
"Jet OLEDB:Database Password=MyDbPassword", _
"myUsername", "myPassword"


Likewise for other variable values as well.

You can select a folder with

http://www.j-walk.com/ss/excel/tips/tip29.htm

--
Regards,
Tom Ogilvy




"GerryE" wrote:

> I am using a program that will create an access table for estimating purposes
> and I'm trying to create a change order template in excel with a query that
> will import certain fields from the table. Each project will have its own
> access database. What I am trying to achieve is to have the query prompt for
> the database location. The location seems to be fixed. Can anyone help or
> provide other alternatives. I know little about queries and even less about
> editing the sql.

Just for completeness, would make similar adjustments to the SQL string.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

> if you have a string like
>
> oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=c:\somepath\mydb.mdb;" & _
> "Jet OLEDB:Database Password=MyDbPassword", _
> "myUsername", "myPassword"
> you can incorporate a variable value with
>
> oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & MyVar & ";" & _
> "Jet OLEDB:Database Password=MyDbPassword", _
> "myUsername", "myPassword"
>
>
> Likewise for other variable values as well.
>
> You can select a folder with
>
> http://www.j-walk.com/ss/excel/tips/tip29.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "GerryE" wrote:
>
> > I am using a program that will create an access table for estimating purposes
> > and I'm trying to create a change order template in excel with a query that
> > will import certain fields from the table. Each project will have its own
> > access database. What I am trying to achieve is to have the query prompt for
> > the database location. The location seems to be fixed. Can anyone help or
> > provide other alternatives. I know little about queries and even less about
> > editing the sql.

This is what I have in the SQL statement. I am using MS2000. How can I
create the string you provided?

SELECT Items.Quantity, Items.PN, Items.Tag1, Items.Tag3, Items.FinalSell
FROM `C:\Documents and Settings\ESCGE01\Desktop\Q-112804\Xpress`.Items Items

"Tom Ogilvy" wrote:

> if you have a string like
>
> oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=c:\somepath\mydb.mdb;" & _
> "Jet OLEDB:Database Password=MyDbPassword", _
> "myUsername", "myPassword"
> you can incorporate a variable value with
>
> oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & MyVar & ";" & _
> "Jet OLEDB:Database Password=MyDbPassword", _
> "myUsername", "myPassword"
>
>
> Likewise for other variable values as well.
>
> You can select a folder with
>
> http://www.j-walk.com/ss/excel/tips/tip29.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "GerryE" wrote:
>
> > I am using a program that will create an access table for estimating purposes
> > and I'm trying to create a change order template in excel with a query that
> > will import certain fields from the table. Each project will have its own
> > access database. What I am trying to achieve is to have the query prompt for
> > the database location. The location seems to be fixed. Can anyone help or
> > provide other alternatives. I know little about queries and even less about
> > editing the sql.

It depends on how much selecting the folder will get you and what the name
of the file is.

If the filesname will always be xpress, then
s = "SELECT Items.Quantity, Items.PN, Items.Tag1, Items.Tag3, Items.FinalSell
FROM `" & mVar & "\xpress`.Items Items"


The second .Items Items says to refer the database as "Items" and that is
what is used in qualifying the field names, so you can replace the path (and
if necessary, the name of the database) with whatever it needs to be.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy




"GerryE" wrote:

> This is what I have in the SQL statement. I am using MS2000. How can I
> create the string you provided?
>
> SELECT Items.Quantity, Items.PN, Items.Tag1, Items.Tag3, Items.FinalSell
> FROM `C:\Documents and Settings\ESCGE01\Desktop\Q-112804\Xpress`.Items Items
>
> "Tom Ogilvy" wrote:
>
> > if you have a string like
> >
> > oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=c:\somepath\mydb.mdb;" & _
> > "Jet OLEDB:Database Password=MyDbPassword", _
> > "myUsername", "myPassword"
> > you can incorporate a variable value with
> >
> > oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=" & MyVar & ";" & _
> > "Jet OLEDB:Database Password=MyDbPassword", _
> > "myUsername", "myPassword"
> >
> >
> > Likewise for other variable values as well.
> >
> > You can select a folder with
> >
> > http://www.j-walk.com/ss/excel/tips/tip29.htm
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "GerryE" wrote:
> >
> > > I am using a program that will create an access table for estimating purposes
> > > and I'm trying to create a change order template in excel with a query that
> > > will import certain fields from the table. Each project will have its own
> > > access database. What I am trying to achieve is to have the query prompt for
> > > the database location. The location seems to be fixed. Can anyone help or
> > > provide other alternatives. I know little about queries and even less about
> > > editing the sql.

Tom,
I have created a macro that will insert the access data into excel. How can
I add the Select Directory Function to my macro, in place of the specified
path?

"Tom Ogilvy" wrote:

>
>
> It depends on how much selecting the folder will get you and what the name
> of the file is.
>
> If the filesname will always be xpress, then
> s = "SELECT Items.Quantity, Items.PN, Items.Tag1, Items.Tag3, Items.FinalSell
> FROM `" & mVar & "\xpress`.Items Items"
>
>
> The second .Items Items says to refer the database as "Items" and that is
> what is used in qualifying the field names, so you can replace the path (and
> if necessary, the name of the database) with whatever it needs to be.
>
> --
> Regards,
> Tom Ogilvy
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "GerryE" wrote:
>
> > This is what I have in the SQL statement. I am using MS2000. How can I
> > create the string you provided?
> >
> > SELECT Items.Quantity, Items.PN, Items.Tag1, Items.Tag3, Items.FinalSell
> > FROM `C:\Documents and Settings\ESCGE01\Desktop\Q-112804\Xpress`.Items Items
> >
> > "Tom Ogilvy" wrote:
> >
> > > if you have a string like
> > >
> > > oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > > "Data Source=c:\somepath\mydb.mdb;" & _
> > > "Jet OLEDB:Database Password=MyDbPassword", _
> > > "myUsername", "myPassword"
> > > you can incorporate a variable value with
> > >
> > > oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > > "Data Source=" & MyVar & ";" & _
> > > "Jet OLEDB:Database Password=MyDbPassword", _
> > > "myUsername", "myPassword"
> > >
> > >
> > > Likewise for other variable values as well.
> > >
> > > You can select a folder with
> > >
> > > http://www.j-walk.com/ss/excel/tips/tip29.htm
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > >
> > > "GerryE" wrote:
> > >
> > > > I am using a program that will create an access table for estimating purposes
> > > > and I'm trying to create a change order template in excel with a query that
> > > > will import certain fields from the table. Each project will have its own
> > > > access database. What I am trying to achieve is to have the query prompt for
> > > > the database location. The location seems to be fixed. Can anyone help or
> > > > provide other alternatives. I know little about queries and even less about
> > > > editing the sql.

http://j-walk.com/ss/excel/tips/tip29.htm

--
Regards,
Tom Ogilvy

"GerryE" <[email protected]> wrote in message
news:[email protected]...
> Tom,
> I have created a macro that will insert the access data into excel. How
> can
> I add the Select Directory Function to my macro, in place of the specified
> path?
>
> "Tom Ogilvy" wrote:
>
>>
>>
>> It depends on how much selecting the folder will get you and what the
>> name
>> of the file is.
>>
>> If the filesname will always be xpress, then
>> s = "SELECT Items.Quantity, Items.PN, Items.Tag1, Items.Tag3,
>> Items.FinalSell
>> FROM `" & mVar & "\xpress`.Items Items"
>>
>>
>> The second .Items Items says to refer the database as "Items" and that
>> is
>> what is used in qualifying the field names, so you can replace the path
>> (and
>> if necessary, the name of the database) with whatever it needs to be.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>>
>>
>> "GerryE" wrote:
>>
>> > This is what I have in the SQL statement. I am using MS2000. How can
>> > I
>> > create the string you provided?
>> >
>> > SELECT Items.Quantity, Items.PN, Items.Tag1, Items.Tag3,
>> > Items.FinalSell
>> > FROM `C:\Documents and Settings\ESCGE01\Desktop\Q-112804\Xpress`.Items
>> > Items
>> >
>> > "Tom Ogilvy" wrote:
>> >
>> > > if you have a string like
>> > >
>> > > oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>> > > "Data Source=c:\somepath\mydb.mdb;" & _
>> > > "Jet OLEDB:Database Password=MyDbPassword", _
>> > > "myUsername", "myPassword"
>> > > you can incorporate a variable value with
>> > >
>> > > oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>> > > "Data Source=" & MyVar & ";" & _
>> > > "Jet OLEDB:Database Password=MyDbPassword", _
>> > > "myUsername", "myPassword"
>> > >
>> > >
>> > > Likewise for other variable values as well.
>> > >
>> > > You can select a folder with
>> > >
>> > > http://www.j-walk.com/ss/excel/tips/tip29.htm
>> > >
>> > > --
>> > > Regards,
>> > > Tom Ogilvy
>> > >
>> > >
>> > >
>> > >
>> > > "GerryE" wrote:
>> > >
>> > > > I am using a program that will create an access table for
>> > > > estimating purposes
>> > > > and I'm trying to create a change order template in excel with a
>> > > > query that
>> > > > will import certain fields from the table. Each project will have
>> > > > its own
>> > > > access database. What I am trying to achieve is to have the query
>> > > > prompt for
>> > > > the database location. The location seems to be fixed. Can anyone
>> > > > help or
>> > > > provide other alternatives. I know little about queries and even
>> > > > less about
>> > > > editing the sql.