Generating Pass Through Queries

  • Thread starter Thread starter NH
  • Start date Start date
N

NH

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
 
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")
 
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
 
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
 
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
 
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)
 
Back
Top