Generating Pass Through Queries

Cross-posted to;
microsoft.public.access.externaldata
microsoft.public.access.modulesdaovba
microsoft.public.access.odbcclientsvr

I want to grab data from an odbc source into my application.

I have found the best method to be using a pass-through query.
Unfortunately, the ODBC source needs to be totally variable. I know how to
build basic queries from SQL satements with vba, but I now need to know how
to create a pass-through query, embedding the datasource, database,
username, password, etc. into it.

Sorry about the cross-post, but I really don't know where this question
should be asked...

Thanks,

Nick

Hi Nick-
I think I have some experience dealing with you're problem, but I'm not sure
I fully understand what you're asking (I haven't looked at responses to
other groups either), but I do have to deal with multiple database drivers
in my work so maybe I can help.
First, an assumption, you use the term "pass-through query" so I assume you
are using Access/Jet for data access.
Your best bet may be to require the user to define a DSN and use that to
connect to the datasource. In code you define a string variable for the
connection string and use that to open your connection. If DSN is not an
option, you need some facility to build the string from user input or a
config file. Either way, you will need to use a string variable to define
the connection string in code and get it at runtime, from a table, config
file, or the user.
Remember that with pass-through queries Jet passes the query string to the
provider, and different providers accept different syntax, i.e. T-SQL will
fail if you use Now() in a query where Access is happy (SQL is GETDATE() ),
and Oracle is again different (SYSDATE, in format dd/mmm/yyyy), so depending
on your provider you may need different query syntax too. If this is so, is
there a reason to use the ODBC provider through Jet rather than an OLEDB
provider (no pass-though query, but direct access, + speed)?
HTH
Ben


"NH" <nhNOSPAMPLEASE@trada.net> wrote in message
news:ez7xEeCWDHA.484@TK2MSFTNGP09.phx.gbl...
> Cross-posted to;
> microsoft.public.access.externaldata
> microsoft.public.access.modulesdaovba
> microsoft.public.access.odbcclientsvr
>
> I want to grab data from an odbc source into my application.
>
> I have found the best method to be using a pass-through query.
> Unfortunately, the ODBC source needs to be totally variable. I know how to
> build basic queries from SQL satements with vba, but I now need to know
how
> to create a pass-through query, embedding the datasource, database,
> username, password, etc. into it.
>
> Sorry about the cross-post, but I really don't know where this question
> should be asked...
>
> Thanks,
>
> Nick
>
>

When I log on I set the connect property for each SPT query based on the
connect string to the ODBC datasource:
Set qdf = db.QueryDefs("sptCountItem")
qdf.Connect = dbODBC.Connect

Then in code I modify the SQL property and call it:

db.QueryDefs("sptCountItem").SQL = strSQL
mNumrecs = DLookup("Total", "sptCountItem")


--
Joe Fallon
Access MVP



"NH" <nhNOSPAMPLEASE@trada.net> wrote in message
news:ez7xEeCWDHA.484@TK2MSFTNGP09.phx.gbl...
> Cross-posted to;
> microsoft.public.access.externaldata
> microsoft.public.access.modulesdaovba
> microsoft.public.access.odbcclientsvr
>
> I want to grab data from an odbc source into my application.
>
> I have found the best method to be using a pass-through query.
> Unfortunately, the ODBC source needs to be totally variable. I know how to
> build basic queries from SQL satements with vba, but I now need to know
how
> to create a pass-through query, embedding the datasource, database,
> username, password, etc. into it.
>
> Sorry about the cross-post, but I really don't know where this question
> should be asked...
>
> Thanks,
>
> Nick
>
>

Thanks Ben.

The reason I ask is that I am writing an relatively simple application for
Accountants. Most of the prospective users are already running ODBC
compliant packages such as Sage Line 50... Rather than build my own contact
management system into my application, I just want to download all of the
contacts from Sage...

The problem is that I need a way of asking for the username and password for
Sage, and then hard-coding it into the app, so they don't have to keep
re-entering it.

I originally had linked ODBC tables in the app, but I found that they were
very temperamental, much more so than pass-through queries.

I am able to create normal select queries from SQL using code, but am not
sure how to make pass-through ones, and how to store the username and
passwords..

(The SQL for a pass-though query appears the same as any select query, so
how do I tell Access that this new query is a pass-through, and where do I
store the passwords?)

Thanks


"Ben" <afishneedsabicycle@nospamyahoo.com> wrote in message
newsWFWa.56184$7O4.1217759@twister.rdc-kc.rr.com...
> Hi Nick-
> I think I have some experience dealing with you're problem, but I'm not
sure
> I fully understand what you're asking (I haven't looked at responses to
> other groups either), but I do have to deal with multiple database drivers
> in my work so maybe I can help.
> First, an assumption, you use the term "pass-through query" so I assume
you
> are using Access/Jet for data access.
> Your best bet may be to require the user to define a DSN and use that to
> connect to the datasource. In code you define a string variable for the
> connection string and use that to open your connection. If DSN is not an
> option, you need some facility to build the string from user input or a
> config file. Either way, you will need to use a string variable to define
> the connection string in code and get it at runtime, from a table, config
> file, or the user.
> Remember that with pass-through queries Jet passes the query string to the
> provider, and different providers accept different syntax, i.e. T-SQL will
> fail if you use Now() in a query where Access is happy (SQL is
GETDATE() ),
> and Oracle is again different (SYSDATE, in format dd/mmm/yyyy), so
depending
> on your provider you may need different query syntax too. If this is so,
is
> there a reason to use the ODBC provider through Jet rather than an OLEDB
> provider (no pass-though query, but direct access, + speed)?
> HTH
> Ben
>
>
> "NH" <nhNOSPAMPLEASE@trada.net> wrote in message
> news:ez7xEeCWDHA.484@TK2MSFTNGP09.phx.gbl...
> > Cross-posted to;
> > microsoft.public.access.externaldata
> > microsoft.public.access.modulesdaovba
> > microsoft.public.access.odbcclientsvr
> >
> > I want to grab data from an odbc source into my application.
> >
> > I have found the best method to be using a pass-through query.
> > Unfortunately, the ODBC source needs to be totally variable. I know how
to
> > build basic queries from SQL satements with vba, but I now need to know
> how
> > to create a pass-through query, embedding the datasource, database,
> > username, password, etc. into it.
> >
> > Sorry about the cross-post, but I really don't know where this question
> > should be asked...
> >
> > Thanks,
> >
> > Nick
> >
> >
>
>

In Design View of the Query Builder, on the menu, Query | SQL-specific |
Passthrough. There are some differences in the SQL, depending on the server
database. One, for example, is that an "*" means "whatever else" in Access
SQL while some servers use "%" for the same purpose.

UID and Password would be part of the Connect property of the Query. But, we
always wanted the users to have to manually log in, for security's sake.

I have not, however, found linked tables to be problematical. What kind of
problems have you experienced?

Please clarify, if you wish, in a follow-up here in this thread in the
newsgroup, not by e-mail. Thanks.

Larry Linson
Microsoft Access MVP

"NH" <nhNOSPAMPLEASE@trada.net> wrote in message
news:u9r6MqoWDHA.2328@TK2MSFTNGP12.phx.gbl...
> Thanks Ben.
>
> The reason I ask is that I am writing an relatively simple application for
> Accountants. Most of the prospective users are already running ODBC
> compliant packages such as Sage Line 50... Rather than build my own
contact
> management system into my application, I just want to download all of the
> contacts from Sage...
>
> The problem is that I need a way of asking for the username and password
for
> Sage, and then hard-coding it into the app, so they don't have to keep
> re-entering it.
>
> I originally had linked ODBC tables in the app, but I found that they were
> very temperamental, much more so than pass-through queries.
>
> I am able to create normal select queries from SQL using code, but am not
> sure how to make pass-through ones, and how to store the username and
> passwords..
>
> (The SQL for a pass-though query appears the same as any select query, so
> how do I tell Access that this new query is a pass-through, and where do I
> store the passwords?)
>
> Thanks
>
>
> "Ben" <afishneedsabicycle@nospamyahoo.com> wrote in message
> newsWFWa.56184$7O4.1217759@twister.rdc-kc.rr.com...
> > Hi Nick-
> > I think I have some experience dealing with you're problem, but I'm not
> sure
> > I fully understand what you're asking (I haven't looked at responses to
> > other groups either), but I do have to deal with multiple database
drivers
> > in my work so maybe I can help.
> > First, an assumption, you use the term "pass-through query" so I assume
> you
> > are using Access/Jet for data access.
> > Your best bet may be to require the user to define a DSN and use that to
> > connect to the datasource. In code you define a string variable for the
> > connection string and use that to open your connection. If DSN is not an
> > option, you need some facility to build the string from user input or a
> > config file. Either way, you will need to use a string variable to
define
> > the connection string in code and get it at runtime, from a table,
config
> > file, or the user.
> > Remember that with pass-through queries Jet passes the query string to
the
> > provider, and different providers accept different syntax, i.e. T-SQL
will
> > fail if you use Now() in a query where Access is happy (SQL is
> GETDATE() ),
> > and Oracle is again different (SYSDATE, in format dd/mmm/yyyy), so
> depending
> > on your provider you may need different query syntax too. If this is so,
> is
> > there a reason to use the ODBC provider through Jet rather than an OLEDB
> > provider (no pass-though query, but direct access, + speed)?
> > HTH
> > Ben
> >
> >
> > "NH" <nhNOSPAMPLEASE@trada.net> wrote in message
> > news:ez7xEeCWDHA.484@TK2MSFTNGP09.phx.gbl...
> > > Cross-posted to;
> > > microsoft.public.access.externaldata
> > > microsoft.public.access.modulesdaovba
> > > microsoft.public.access.odbcclientsvr
> > >
> > > I want to grab data from an odbc source into my application.
> > >
> > > I have found the best method to be using a pass-through query.
> > > Unfortunately, the ODBC source needs to be totally variable. I know
how
> to
> > > build basic queries from SQL satements with vba, but I now need to
know
> > how
> > > to create a pass-through query, embedding the datasource, database,
> > > username, password, etc. into it.
> > >
> > > Sorry about the cross-post, but I really don't know where this
question
> > > should be asked...
> > >
> > > Thanks,
> > >
> > > Nick
> > >
> > >
> >
> >
>
>

Sorry, I don't think I was very clear..

What I was trying to say was that the actual query needs to be changed by
the application (to suit the users' needs). The way I achieve this is to use
vba. I.e. create a string containing an SQL statement depending on the
users' options, and then create a query using that string using
db.CreateQueryDef.

My problem is that now I need to do the same thing but with outside data
(from an ODBC source). What I need to do is exactly the same as above, but
also include the username, password, and ODBC connection details in the
newly created query. My users will not have any idea how to create the
query, so I must do it via code.

In the most basic terms, how do I change the following code to create a
passthrough query connecting to dsn "MYDSN" using "USER" as username, and
"PASSWORD" ans password;

db.CreateQueryDef "MyNewQuery", "SELECT * FROM tbl_contacts"

I hope that makes a bit more sense..

Thanks, Nick



"Larry Linson" <larry.linson@ntpcug.org> wrote in message
news:OPcoZZrWDHA.1384@TK2MSFTNGP10.phx.gbl...
> In Design View of the Query Builder, on the menu, Query | SQL-specific |
> Passthrough. There are some differences in the SQL, depending on the
server
> database. One, for example, is that an "*" means "whatever else" in Access
> SQL while some servers use "%" for the same purpose.
>
> UID and Password would be part of the Connect property of the Query. But,
we
> always wanted the users to have to manually log in, for security's sake.
>
> I have not, however, found linked tables to be problematical. What kind of
> problems have you experienced?
>
> Please clarify, if you wish, in a follow-up here in this thread in the
> newsgroup, not by e-mail. Thanks.
>
> Larry Linson
> Microsoft Access MVP
>
> "NH" <nhNOSPAMPLEASE@trada.net> wrote in message
> news:u9r6MqoWDHA.2328@TK2MSFTNGP12.phx.gbl...
> > Thanks Ben.
> >
> > The reason I ask is that I am writing an relatively simple application
for
> > Accountants. Most of the prospective users are already running ODBC
> > compliant packages such as Sage Line 50... Rather than build my own
> contact
> > management system into my application, I just want to download all of
the
> > contacts from Sage...
> >
> > The problem is that I need a way of asking for the username and password
> for
> > Sage, and then hard-coding it into the app, so they don't have to keep
> > re-entering it.
> >
> > I originally had linked ODBC tables in the app, but I found that they
were
> > very temperamental, much more so than pass-through queries.
> >
> > I am able to create normal select queries from SQL using code, but am
not
> > sure how to make pass-through ones, and how to store the username and
> > passwords..
> >
> > (The SQL for a pass-though query appears the same as any select query,
so
> > how do I tell Access that this new query is a pass-through, and where do
I
> > store the passwords?)
> >
> > Thanks
> >
> >
> > "Ben" <afishneedsabicycle@nospamyahoo.com> wrote in message
> > newsWFWa.56184$7O4.1217759@twister.rdc-kc.rr.com...
> > > Hi Nick-
> > > I think I have some experience dealing with you're problem, but I'm
not
> > sure
> > > I fully understand what you're asking (I haven't looked at responses
to
> > > other groups either), but I do have to deal with multiple database
> drivers
> > > in my work so maybe I can help.
> > > First, an assumption, you use the term "pass-through query" so I
assume
> > you
> > > are using Access/Jet for data access.
> > > Your best bet may be to require the user to define a DSN and use that
to
> > > connect to the datasource. In code you define a string variable for
the
> > > connection string and use that to open your connection. If DSN is not
an
> > > option, you need some facility to build the string from user input or
a
> > > config file. Either way, you will need to use a string variable to
> define
> > > the connection string in code and get it at runtime, from a table,
> config
> > > file, or the user.
> > > Remember that with pass-through queries Jet passes the query string to
> the
> > > provider, and different providers accept different syntax, i.e. T-SQL
> will
> > > fail if you use Now() in a query where Access is happy (SQL is
> > GETDATE() ),
> > > and Oracle is again different (SYSDATE, in format dd/mmm/yyyy), so
> > depending
> > > on your provider you may need different query syntax too. If this is
so,
> > is
> > > there a reason to use the ODBC provider through Jet rather than an
OLEDB
> > > provider (no pass-though query, but direct access, + speed)?
> > > HTH
> > > Ben
> > >
> > >
> > > "NH" <nhNOSPAMPLEASE@trada.net> wrote in message
> > > news:ez7xEeCWDHA.484@TK2MSFTNGP09.phx.gbl...
> > > > Cross-posted to;
> > > > microsoft.public.access.externaldata
> > > > microsoft.public.access.modulesdaovba
> > > > microsoft.public.access.odbcclientsvr
> > > >
> > > > I want to grab data from an odbc source into my application.
> > > >
> > > > I have found the best method to be using a pass-through query.
> > > > Unfortunately, the ODBC source needs to be totally variable. I know
> how
> > to
> > > > build basic queries from SQL satements with vba, but I now need to
> know
> > > how
> > > > to create a pass-through query, embedding the datasource, database,
> > > > username, password, etc. into it.
> > > >
> > > > Sorry about the cross-post, but I really don't know where this
> question
> > > > should be asked...
> > > >
> > > > Thanks,
> > > >
> > > > Nick
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Uh...
I answered this 3 days ago.

Didn't you see it?
--
Joe Fallon
Access MVP



"NH" <nhNOSPAMPLEASE@trada.net> wrote in message
news:eI8vR31WDHA.2056@TK2MSFTNGP11.phx.gbl...
> Sorry, I don't think I was very clear..
>
> What I was trying to say was that the actual query needs to be changed by
> the application (to suit the users' needs). The way I achieve this is to
use
> vba. I.e. create a string containing an SQL statement depending on the
> users' options, and then create a query using that string using
> db.CreateQueryDef.
>
> My problem is that now I need to do the same thing but with outside data
> (from an ODBC source). What I need to do is exactly the same as above, but
> also include the username, password, and ODBC connection details in the
> newly created query. My users will not have any idea how to create the
> query, so I must do it via code.
>
> In the most basic terms, how do I change the following code to create a
> passthrough query connecting to dsn "MYDSN" using "USER" as username, and
> "PASSWORD" ans password;
>
> db.CreateQueryDef "MyNewQuery", "SELECT * FROM tbl_contacts"
>
> I hope that makes a bit more sense..
>
> Thanks, Nick
>
>
>
> "Larry Linson" <larry.linson@ntpcug.org> wrote in message
> news:OPcoZZrWDHA.1384@TK2MSFTNGP10.phx.gbl...
> > In Design View of the Query Builder, on the menu, Query | SQL-specific |
> > Passthrough. There are some differences in the SQL, depending on the
> server
> > database. One, for example, is that an "*" means "whatever else" in
Access
> > SQL while some servers use "%" for the same purpose.
> >
> > UID and Password would be part of the Connect property of the Query.
But,
> we
> > always wanted the users to have to manually log in, for security's sake.
> >
> > I have not, however, found linked tables to be problematical. What kind
of
> > problems have you experienced?
> >
> > Please clarify, if you wish, in a follow-up here in this thread in the
> > newsgroup, not by e-mail. Thanks.
> >
> > Larry Linson
> > Microsoft Access MVP
> >
> > "NH" <nhNOSPAMPLEASE@trada.net> wrote in message
> > news:u9r6MqoWDHA.2328@TK2MSFTNGP12.phx.gbl...
> > > Thanks Ben.
> > >
> > > The reason I ask is that I am writing an relatively simple application
> for
> > > Accountants. Most of the prospective users are already running ODBC
> > > compliant packages such as Sage Line 50... Rather than build my own
> > contact
> > > management system into my application, I just want to download all of
> the
> > > contacts from Sage...
> > >
> > > The problem is that I need a way of asking for the username and
password
> > for
> > > Sage, and then hard-coding it into the app, so they don't have to keep
> > > re-entering it.
> > >
> > > I originally had linked ODBC tables in the app, but I found that they
> were
> > > very temperamental, much more so than pass-through queries.
> > >
> > > I am able to create normal select queries from SQL using code, but am
> not
> > > sure how to make pass-through ones, and how to store the username and
> > > passwords..
> > >
> > > (The SQL for a pass-though query appears the same as any select query,
> so
> > > how do I tell Access that this new query is a pass-through, and where
do
> I
> > > store the passwords?)
> > >
> > > Thanks
> > >
> > >
> > > "Ben" <afishneedsabicycle@nospamyahoo.com> wrote in message
> > > newsWFWa.56184$7O4.1217759@twister.rdc-kc.rr.com...
> > > > Hi Nick-
> > > > I think I have some experience dealing with you're problem, but I'm
> not
> > > sure
> > > > I fully understand what you're asking (I haven't looked at responses
> to
> > > > other groups either), but I do have to deal with multiple database
> > drivers
> > > > in my work so maybe I can help.
> > > > First, an assumption, you use the term "pass-through query" so I
> assume
> > > you
> > > > are using Access/Jet for data access.
> > > > Your best bet may be to require the user to define a DSN and use
that
> to
> > > > connect to the datasource. In code you define a string variable for
> the
> > > > connection string and use that to open your connection. If DSN is
not
> an
> > > > option, you need some facility to build the string from user input
or
> a
> > > > config file. Either way, you will need to use a string variable to
> > define
> > > > the connection string in code and get it at runtime, from a table,
> > config
> > > > file, or the user.
> > > > Remember that with pass-through queries Jet passes the query string
to
> > the
> > > > provider, and different providers accept different syntax, i.e.
T-SQL
> > will
> > > > fail if you use Now() in a query where Access is happy (SQL is
> > > GETDATE() ),
> > > > and Oracle is again different (SYSDATE, in format dd/mmm/yyyy), so
> > > depending
> > > > on your provider you may need different query syntax too. If this is
> so,
> > > is
> > > > there a reason to use the ODBC provider through Jet rather than an
> OLEDB
> > > > provider (no pass-though query, but direct access, + speed)?
> > > > HTH
> > > > Ben
> > > >
> > > >
> > > > "NH" <nhNOSPAMPLEASE@trada.net> wrote in message
> > > > news:ez7xEeCWDHA.484@TK2MSFTNGP09.phx.gbl...
> > > > > Cross-posted to;
> > > > > microsoft.public.access.externaldata
> > > > > microsoft.public.access.modulesdaovba
> > > > > microsoft.public.access.odbcclientsvr
> > > > >
> > > > > I want to grab data from an odbc source into my application.
> > > > >
> > > > > I have found the best method to be using a pass-through query.
> > > > > Unfortunately, the ODBC source needs to be totally variable. I
know
> > how
> > > to
> > > > > build basic queries from SQL satements with vba, but I now need to
> > know
> > > > how
> > > > > to create a pass-through query, embedding the datasource,
database,
> > > > > username, password, etc. into it.
> > > > >
> > > > > Sorry about the cross-post, but I really don't know where this
> > question
> > > > > should be asked...
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Nick
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

I don't create queries at runtime. I modify existing queries at runtime.
(as Joe has demonstrated). One reason is, because it is easier.
Another reason is, because it causes less database bloat.


(david)

"NH" <nhNOSPAMPLEASE@trada.net> wrote in message
news:eI8vR31WDHA.2056@TK2MSFTNGP11.phx.gbl...
> Sorry, I don't think I was very clear..
>
> What I was trying to say was that the actual query needs to be changed by
> the application (to suit the users' needs). The way I achieve this is to
use
> vba. I.e. create a string containing an SQL statement depending on the
> users' options, and then create a query using that string using
> db.CreateQueryDef.
>
> My problem is that now I need to do the same thing but with outside data
> (from an ODBC source). What I need to do is exactly the same as above, but
> also include the username, password, and ODBC connection details in the
> newly created query. My users will not have any idea how to create the
> query, so I must do it via code.
>
> In the most basic terms, how do I change the following code to create a
> passthrough query connecting to dsn "MYDSN" using "USER" as username, and
> "PASSWORD" ans password;
>
> db.CreateQueryDef "MyNewQuery", "SELECT * FROM tbl_contacts"
>
> I hope that makes a bit more sense..
>
> Thanks, Nick
>
>
>
> "Larry Linson" <larry.linson@ntpcug.org> wrote in message
> news:OPcoZZrWDHA.1384@TK2MSFTNGP10.phx.gbl...
> > In Design View of the Query Builder, on the menu, Query | SQL-specific |
> > Passthrough. There are some differences in the SQL, depending on the
> server
> > database. One, for example, is that an "*" means "whatever else" in
Access
> > SQL while some servers use "%" for the same purpose.
> >
> > UID and Password would be part of the Connect property of the Query.
But,
> we
> > always wanted the users to have to manually log in, for security's sake.
> >
> > I have not, however, found linked tables to be problematical. What kind
of
> > problems have you experienced?
> >
> > Please clarify, if you wish, in a follow-up here in this thread in the
> > newsgroup, not by e-mail. Thanks.
> >
> > Larry Linson
> > Microsoft Access MVP
> >
> > "NH" <nhNOSPAMPLEASE@trada.net> wrote in message
> > news:u9r6MqoWDHA.2328@TK2MSFTNGP12.phx.gbl...
> > > Thanks Ben.
> > >
> > > The reason I ask is that I am writing an relatively simple application
> for
> > > Accountants. Most of the prospective users are already running ODBC
> > > compliant packages such as Sage Line 50... Rather than build my own
> > contact
> > > management system into my application, I just want to download all of
> the
> > > contacts from Sage...
> > >
> > > The problem is that I need a way of asking for the username and
password
> > for
> > > Sage, and then hard-coding it into the app, so they don't have to keep
> > > re-entering it.
> > >
> > > I originally had linked ODBC tables in the app, but I found that they
> were
> > > very temperamental, much more so than pass-through queries.
> > >
> > > I am able to create normal select queries from SQL using code, but am
> not
> > > sure how to make pass-through ones, and how to store the username and
> > > passwords..
> > >
> > > (The SQL for a pass-though query appears the same as any select query,
> so
> > > how do I tell Access that this new query is a pass-through, and where
do
> I
> > > store the passwords?)
> > >
> > > Thanks
> > >
> > >
> > > "Ben" <afishneedsabicycle@nospamyahoo.com> wrote in message
> > > newsWFWa.56184$7O4.1217759@twister.rdc-kc.rr.com...
> > > > Hi Nick-
> > > > I think I have some experience dealing with you're problem, but I'm
> not
> > > sure
> > > > I fully understand what you're asking (I haven't looked at responses
> to
> > > > other groups either), but I do have to deal with multiple database
> > drivers
> > > > in my work so maybe I can help.
> > > > First, an assumption, you use the term "pass-through query" so I
> assume
> > > you
> > > > are using Access/Jet for data access.
> > > > Your best bet may be to require the user to define a DSN and use
that
> to
> > > > connect to the datasource. In code you define a string variable for
> the
> > > > connection string and use that to open your connection. If DSN is
not
> an
> > > > option, you need some facility to build the string from user input
or
> a
> > > > config file. Either way, you will need to use a string variable to
> > define
> > > > the connection string in code and get it at runtime, from a table,
> > config
> > > > file, or the user.
> > > > Remember that with pass-through queries Jet passes the query string
to
> > the
> > > > provider, and different providers accept different syntax, i.e.
T-SQL
> > will
> > > > fail if you use Now() in a query where Access is happy (SQL is
> > > GETDATE() ),
> > > > and Oracle is again different (SYSDATE, in format dd/mmm/yyyy), so
> > > depending
> > > > on your provider you may need different query syntax too. If this is
> so,
> > > is
> > > > there a reason to use the ODBC provider through Jet rather than an
> OLEDB
> > > > provider (no pass-though query, but direct access, + speed)?
> > > > HTH
> > > > Ben
> > > >
> > > >
> > > > "NH" <nhNOSPAMPLEASE@trada.net> wrote in message
> > > > news:ez7xEeCWDHA.484@TK2MSFTNGP09.phx.gbl...
> > > > > Cross-posted to;
> > > > > microsoft.public.access.externaldata
> > > > > microsoft.public.access.modulesdaovba
> > > > > microsoft.public.access.odbcclientsvr
> > > > >
> > > > > I want to grab data from an odbc source into my application.
> > > > >
> > > > > I have found the best method to be using a pass-through query.
> > > > > Unfortunately, the ODBC source needs to be totally variable. I
know
> > how
> > > to
> > > > > build basic queries from SQL satements with vba, but I now need to
> > know
> > > > how
> > > > > to create a pass-through query, embedding the datasource,
database,
> > > > > username, password, etc. into it.
> > > > >
> > > > > Sorry about the cross-post, but I really don't know where this
> > question
> > > > > should be asked...
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Nick
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>