convert access query to pass-thru TSQL

convert access query to pass-thru TSQL

Post by bobh » Wed, 13 Feb 2008 02:10:13


Hi All,

Actually I have two guestions using AccessXP;

1. can you have a pass-thru query(which is TSQL coded) be the source
of an access select query(SQL coded)??

2. Is there a third party product or access add-in that will convert
an access select query(SQL) to a pass-thru query(TSQL)??

thanks
bobh.
 
 
 

convert access query to pass-thru TSQL

Post by Rich » Wed, 13 Feb 2008 09:24:29

Hi bob

Answer to question 2) about 3rd party program: there probably is, but I
am not aware of any.

Answer to question 1) if you are using an Access Data project (ADP),
then yes because Access is acting as a front end to the Sql Server. So
everything you would see in the Access database windows (tables, queries
only) would be the stuff in your sql server DB.

But if you are using an MDB, then you could use ADODB to invoke a stored
procedure on the sql server.

Note: there are mixed feelings about ADODB and Access. The reason is
because people don't seem to be aware that even though you can use ADODB
within Access to query mdb tables -- ADODB is/was really desiged for
interfacing between Sql Server and Access at the com level (as opposed
to the .Net level). DAO is the data programming model that is native to
Access and thus, functions the most efficiently within Access. But DAO
cannot invoke a stored procedure that resides on a sql server. For this
you need ADODB.

With the proper permisions on the server, you can create an entire sql
Server DB directly from Access using ADODB. The cool thing about ADODB
is that it supports all of TSql and all of Jet Sql. But I wouldn't
bother using ADODB with Jet sql - it's over kill.

Rich

*** Sent via Developersdex http://www.yqcomputer.com/ ***

 
 
 

convert access query to pass-thru TSQL

Post by Albert D. » Wed, 13 Feb 2008 11:03:06


Any t-SQL procedure that returns records should well returned data to a
record set in access. Just make sure the query is a pass-through, and type
in the procedure name to run (exec my_sp). Whatever you type in the query
builder will passed to SQL server untouched if you make the query pass
through. You can even send SQL server set commands if you wish. However I
not tried sending more than one command stuffed in a single access query to
see if that works. if I'm reading your question correctly, you can most
certainly have t-sql procedures on the SQL side returned as a result set to
a query on the access side.


I'm not quite 100% clear on what your goal is here, are you asking for
something that converts access to SQL server syntax. Or, are you looking for
something that allows you to convert a standard query in access to a pass
through query?

For the most part if your SQL is quite clean, and does not do things like
have direct form references, or use any vba functions, then usually you can
just use the query menu option and say this is going to be a pass through
query, and it will work. If you've used vba functions, or forms references,
then am afraid no conversion tool will be able to fix the form references
issues for you anyway.

Keep in mind that if your query doesn't have any joins in it, then you often
don't really have to convert the query to pass through to gain any
additional performance. Jet will only pull the records it needs in most
cases However, I will be the first to admit that jet often does a really
poor job when you start involving other tables and joins. The solution this
case is to convert the SQL to an actual view on the server side, and then
link to that.

Therefore you really don't need to convert all queries to a pass through.
The approaches is to work the queries that run very slow, and simply to
leave the other ones until you have additional time to tweak them. Often I
find the best approach for the queries that have complex joins is to simply
chop from the access query builder into the SQL server side query builder.
When done you convert that new sql query to a view, and link to that view
from access. On the other hand if you're just talking about a query data
source for a report, then sure just make a pass through.

Keep in mind that those pass through queries are not updateable, but linking
to views are, and they yield nearly the same high performance. Using and
lining to view from access, or choosing pass-through's is usually dictated
by the need to update the resulting data sets.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
XXXX@XXXXX.COM
 
 
 

convert access query to pass-thru TSQL

Post by Albert D. » Wed, 13 Feb 2008 11:16:27

>But DAO
cannot invoke a stored procedure that resides on a sql server. For this

Huh????

If you make the query pass-through, you can send ANY command to sql server.

assuming pass-though

then go:

dim rst as dao.RecordSet
set rst = currentdb.QueryDefs("MyPassThought").Execute

If you want to execute a stored procedure of *your* choice at runtime, NOT
as a saved query, then again assuming you have that "one" catch all query
saved as pass-though, you go:

Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset

Set qdfPass = CurrentDb.QueryDefs("MyPass")
qdfPass.SQL = "exec sp_myProc"
qdfPass.Execute

We use dao querydefs all the time to fire off sql server commands,and it
usually less code then ADO....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
XXXX@XXXXX.COM
 
 
 

convert access query to pass-thru TSQL

Post by Rich » Thu, 14 Feb 2008 01:06:28

Sorry, my mistake. Since I have only used ADO against sql server from
Access I have pretty much forgotten about this feature.

If you make the query pass-through, you can send ANY command to sql
server.

assuming pass-though

then go:

dim rst as dao.RecordSet
set rst = currentdb.QueryDefs("MyPassThought").Execute

If you want to execute a stored procedure of *your* choice at runtime,
NOT
as a saved query, then again assuming you have that "one" catch all
query
saved as pass-though, you go:

Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset

Set qdfPass = CurrentDb.QueryDefs("MyPass")
qdfPass.SQL = "exec sp_myProc"
qdfPass.Execute

We use dao querydefs all the time to fire off sql server commands,and it
usually less code then ADO....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
XXXX@XXXXX.COM
<<
Rich

*** Sent via Developersdex http://www.yqcomputer.com/ ***
 
 
 

convert access query to pass-thru TSQL

Post by bobh » Sat, 16 Feb 2008 00:28:40

n Feb 11, 9:03m, "Albert D. Kallal" < XXXX@XXXXX.COM >
wrote:
> Edmonton, Alberta Canada> > XXXX@XXXXX.COM

This is the Access query that runs today, it works but it's slow, all
the tables are on SQLServer so I was thinking if I converted this to a
pass-thru query it might run faster but it's way to complex for me to
re-write it in T-SQL so I was hoping there was a product that would
help with converting it from SQL to T-SQL

SELECT dbo_tblCLIENT.ffname1, dbo_tblCLIENT.lname,
dbo_tblCLIENT.fname, IIf(Len(dbo_tblCLIENT!addr<)<1,dbo_tblCLIENT!
addr1,dbo_tblCLIENT!addr1 & " " & dbo_tblCLIENT!addr2) AS addr,
dbo_tblCLIENT.addr2, dbo_tblCLIENT.city, dbo_tblCLIENT.st,
dbo_tblCLIENT.zip, dbo_tblCLIENT.ssn, dbo_tblBASIC_LOSS.dol, [pol_sym]
& "-" & [pol_nbr] AS PolNbr, sbqryGetPolicyHolder.PolHolder,
dbo_tblESTB_FEA.clm_ofcnum, dbo_tblESTB_FEA.fea_nbr
FROM (((dbo_tblESTB_FEA INNER JOIN dbo_tblINVLD_PARTY ON
(dbo_tblESTB_FEA.item_nbr = dbo_tblINVLD_PARTY.item_nbr) AND
(dbo_tblESTB_FEA.clm_ofcnum = dbo_tblINVLD_PARTY.clm_ofcnum)) INNER
JOIN dbo_tblCLIENT ON (dbo_tblESTB_FEA.clm_ofcnum =
dbo_tblCLIENT.clm_ofcnum) AND (dbo_tblINVLD_PARTY.client_nbr =
dbo_tblCLIENT.client_nbr)) INNER JOIN dbo_tblBASIC_LOSS ON
dbo_tblESTB_FEA.clm_ofcnum = dbo_tblBASIC_LOSS.clm_ofcnum) INNER JOIN
sbqryGetPolicyHolder ON dbo_tblESTB_FEA.clm_ofcnum =
sbqryGetPolicyHolder.clm_ofcnum
WHERE (((dbo_tblINVLD_PARTY.category)="v" Or
(dbo_tblINVLD_PARTY.category)="j"))
GROUP BY dbo_tblCLIENT.ffname1, dbo_tblCLIENT.lname,
dbo_tblCLIENT.fname, IIf(Len(dbo_tblCLIENT!addr<)<1,dbo_tblCLIENT!
addr1,dbo_tblCLIENT!addr1 & " " & dbo_tblCLIENT!addr2),
dbo_tblCLIENT.addr2, dbo_tblCLIENT.city, dbo_tblCLIENT.st,
dbo_tblCLIENT.zip, dbo_tblCLIENT.ssn, dbo_tblBASIC_LOSS.dol, [pol_sym]
& "-" & [pol_nbr], sbqryGetPolicyHolder.PolHolder,
dbo_tblESTB_FEA.clm_ofcnum, dbo_tblESTB_FEA.fea_nbr
HAVING (((dbo_tblESTB_FEA.clm_ofcnum)=[Forms]![frmMainMenu]!
[bxClmNbr]) AND ((dbo_tblESTB_FEA.fea_nbr)=[Forms]![frmMainMenu]!
[bxFeatrNbr]));

bobh.
 
 
 

convert access query to pass-thru TSQL

Post by Rich » Sat, 16 Feb 2008 03:37:24

Hi Bob,

The reason your query is running so slowly is because of the combination
of a complex query with ODBC. Simple queries with ODBC work OK. In
your case, I would consider a stored procedure on the sql server. I
just tried Albert Kallal's method of connecting to a stored procedure as
a passthrough query using ODBC, and it does work OK.

So if you convert your query to a sql server stored procedure you should
see an improvement in the performance of your query. The trick now is
in converting the IIf function, because that is not supported in Tsql.
The Tsql version of the IIf function is the Case When Then Else
Statement

SELECT (CASE fld1 WHEN 'sec' THEN 's' WHEN 'xyz' THEN 'x' ELSE 'y' end)
As fldX FROM tbl1

Rich

*** Sent via Developersdex http://www.yqcomputer.com/ ***
 
 
 

convert access query to pass-thru TSQL

Post by Albert D. » Sun, 17 Feb 2008 09:25:57

You would only likely need to change the iff commands.

You are right however, it can be a bit of work in some cases to re-write the
sql. However, it is well worth the effort, and you don't actually have to
re-type the sql. I would paste that sql into the sql query builder....get it
working, and then paste it back into the access query, and change it to
pass-though, (or simply link to the resulting view that you have in sql
server once it starts working).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
XXXX@XXXXX.COM