Access 2003 Pass through Qry to SQL Server 2005

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

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=;PWD= 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/featu...le.php/3407531

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


"JoeQ" wrote in message
news:695C93A0-F35F-4793-839E-347DDEC24C69-at-microsoft.com...
> 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

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=;PWD= > 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/featu...le.php/3407531
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "JoeQ" wrote in message
> news:695C93A0-F35F-4793-839E-347DDEC24C69-at-microsoft.com...
> > 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
>
>
>

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" wrote in message
news:F5BEB0AE-6999-400B-9340-2A0A4B2EF32A-at-microsoft.com...
> 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=;PWD= >> 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/featu...le.php/3407531
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "JoeQ" wrote in message
>> news:695C93A0-F35F-4793-839E-347DDEC24C69-at-microsoft.com...
>> > 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
>>
>>
>>