Re: Access 2003 Pass through Qry to SQL Server 2005

Maybe a corruption here or a change of binaries: when you install a new
version of Access or of service pack (for example, the recent SP3), you
should decompile your MDB file (I do it twice to make of removing any trace
of previous binary compilation) and compact it before using it again.
Importing everything into a blank new mdb file can also do the trick but
personally, I prefer to make a decompilation.

In your case, as you are making an heavy use of passthrough queries, mayby
using an ADP project would be more adapted to your need.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"JoeQ" <JoeQ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F5BEB0AE-6999-400B-9340-2A0A4B2EF32A@xxxxxxxxxxxxxxxx
Sylvain,

Thank you for your response. I made the changes as you suggested
implementing temporary querydef objects and setting ReturnsRecords
property
to false, but the problem still persisted.

I then took the whole process out of the Access database that I was using
and moved it to a new database and everthing ran just fine, even with the
way
I originally had it coded. In my operational Access database where this
code
is failing I open several views and I also open a form based on a couple
of
those views and something in those open views was blocking the pass
through
query from running.

To resolve the issue when I call this module I now close all forms and
drop
all SQL views. The process which is actually made up of several pass
through
queries runs fine. When it completes I then re-link all of my SQL views
and
re-open my forms.

Perhaps with SQL server 2005 I need to re-think having views linked all
the
time. Perhaps I should only link when I actually need instead of linking
them
and leaving them open so that they are available much like and Access
table
would be available att the time.

Anyway Thank you for your advice.

I do appreciate it

Joe


"Sylvain Lafontaine" wrote:

I don't know why you change the connection of the object and close it
before calling the Execute statement. As you are changing the sql
statement
of your querydef, you should use temporary querydef objects in order to
limit the growth of your database. For example, from
http://support.microsoft.com/kb/131534 :

Function ParamSPT (NewGroup As String)

Dim MyDb As Database, MyQ As QueryDef
Set MyDb = CurrentDB()

' Create a temporary QueryDef object that is not saved.
Set MyQ = MyDb.CreateQueryDef("")

' Type a connect string using the appropriate values for your server.
MyQ.connect = "ODBC;DSN=dsn1;UID=<username>;PWD=<strong
password>;DATABASE=test"

' Set ReturnsRecords to false in order to use the Execute method.
MyQ.returnsrecords = False

' Set the SQL property and concatenate the variables.
MyQ.sql = "sp_addgroup" & " " & NewGroup

Debug.Print MyQ.sql
MyQ.Execute
MyQ.Close
MyDb.Close

End Function

Also, as your querydef doesn't return any records, you should set its
ReturnsRecords property to false. See
http://support.microsoft.com/kb/124391/EN-US/ and
http://www.databasejournal.com/features/msaccess/article.php/3407531

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"JoeQ" <JoeQ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:695C93A0-F35F-4793-839E-347DDEC24C69@xxxxxxxxxxxxxxxx
Hello
I have Pass through queries that I can run successfully when opened
directly
in the Access Queries window, but when I try to run the queries from
VBA
code
module I end up with ODBC -- Call Failed. I have increased the timeout
values, I have switched to ADO command where I get a Timeout expired
err.
I
can run the stored procedures in SQL Server 2005 Query window with no
problems as well. I have run the SQL Server Profiler utility and while
trying
to run the pass through queries and the stored procedure that is called
will
show up and then end after the time out period of time and then it
kicks
back
to my VBA code with the ODBC -- call failed message.

The ODBC connection string is:
ODBC;DRIVER=sql server;SERVER=US0223333-WP05;APP=Microsoft Office
2003;WSID=US0223333-WP05;DATABASE=GME;Trusted_Connection=Yes
When I tried ADO the connection string:
Provider=SQLOLEDB.1;Initial Catalog=GME;Data
Source=US0223333-WP05;Extended
Properties="Trusted_Connection=yes"

It makes the connection I have forms and views based on recordsets that
are
called with the above that open with no problem. There seems to be some
problem with calling the pass through with VBA code. The code I am
using
worked just fine with SQL Server 2000. Here is a sample:

szSQL = "DelAllGMEResidentErrs"
Set qd = D.QueryDefs("LineErrorsR")
qd.Connect = D.TableDefs("dbo_Resident_v").Connect
qd.SQL = szSQL
qd.Close
qd.Execute

The stored procedure DelAllGMEResidentErrs on SQL Server 2005 looks
like:
USE [GME]
GO
/****** Object: StoredProcedure [dbo].[DelAllGMEResidentErrs]
Script
Date: 08/07/2008 12:24:47 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[DelAllGMEResidentErrs]
AS
TRUNCATE TABLE GME_Resident_Errors

Any insights any of you may have will be greatly appreciated!

Joe





.