access adp, sql server and recordsets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have an access application with an SQL server backend database. Recently
it has started throwing errors. When I investigated I found that the command
used to retrieve database information through a recordset was the problem as
below.

Dim objrs As Recordset

Set objrs = CurrentProject.Connection.Execute("exec ComplaintIdentifier_INS")
CCID = (objrs!NewCCID)

The stored procedure complaintIdentifier_INS returns returns a field
sucessfull and NewCCID. It can no longer retrieve the NewCCID field with the
code above. It returns the error "item can not be found in collection
correspondong to the request name or ordinal". I have run the stored
procedure through query analyser and it does return the correct information.

Any ideas what might be the cause?

Thanks
 
Maybe a permission problem.

Make sure that the Recordset is not empty (not objrs.EOF) and not closed and
that it is not returning a multiple resultsets (might happen if you don't
use the SET NOCOUNT ON statement).
 
Hello,

You may want to set a breakpoint at line below and add objrs to watch by
right clicking the objrs in code->Add watch

Set objrs = CurrentProject.Connection.Execute("exec
ComplaintIdentifier_INS")

Please check if "NewCCID" is listed under Fields->Item<x>, and
recordcount>0.

Also, you may try to use a new connection other than the connection of
currentproject to test:

set conn= new adodb.connection
call conn.open (CurrentProject.Connection. ConnectionString)
set objrs=conn.Execute ("exec ComplaintIdentifier_INS")
CCID= (objrs!NewCCID)
conn.close

If you have any update, please feel free to let's know. We look forward to
your reply.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hello,

Just want to know if you have tried to debug the code and/or use a new
connection as suggested? Please feel free to post back if you need any
update.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
Hi,

Thanks for all your replies, the situation so far is as follows.

1. I am using the an administration account.
2. I have switched the project back to my local MSDE copy of the database
and it works ok.
3. I haven't used the watch command before but tried it and I could not see
the NewCCID in the objrs>Fields where it says count value 0.
4. I can run the stored procedure under query analyser and it does return
the correct CCID number.
5. I am using the SET NOCONT ON or OFF commands to ensure only one values
is returned. If I didn't do this I usually get an error statement about
multiple returns or something like that.

The database on my local MSDE install should be the same as the server copy
and this was what I used to setup the server copy. As I said the server copy
worked until recently so I can't under stand what could have changed to stop
this working.

Hope this info is helpful. Any ideas on where to go from here?
 
Hi,

In addition the the reply I just sent I have tried the code you suggested

Set conn = New ADODB.Connection
Call conn.Open(CurrentProject.Connection.ConnectionString)
Set objrs = conn.Execute("exec ComplaintIdentifier_INS")
CCID = (objrs!NewCCID)
conn.Close

but it returns loggin failed for user xxxx

The username and password I supplied in the access adp connection settings
is accepted and ok when I click the test button.

Thanks
 
Hello,

Since the issue does not occur with local MSDE, it seems to be a permission
related issue.

I suggest that you try the following steps to isolate the issue:

1. On remote sql server, add the domain user you logged on on local machine
to run the adp to a SQL server login, and grant it system admin role.

2. Open ADP on local machine, click File->Connection, and change the
authentication mode to "Use Windows Integrated security".

3. Close and restart ADP, try to run the code again.

If that works, please try to use "sa" login of remote SQL Server in above
connection configuration to test.

The code I provided shall work if the connection secruity configuration. If
above tests all fail, you may try sample northwind database, and use remote
SQL database to check if the issue is related to this specific database.

If you have any update, please feel free to let's know. I look forward to
your reply.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
Hello,

Just want to know if you have opportunity to perform test as I suggested.
We may need to isolate if the issue is related to permission first. Look
forward to hear from you. Thanks.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
Hello,

I'm still interested in this issue. Another thought on this issue is
checking if you have latest SP2 installed for Access. Please feel free to
post back if there is any update.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
Hi,

Just to let you know the problem resolved iteself a week ago. I can only
assume that the regular security patches that are loaded onto the windows OS
stopped the SQL server functioning correctly and the more recent security
patch/reboot resolved the issues. I am still not sure of this and concerned
it may happen again.

Another unanswered question is why would the stored procedure work through
query analyser and not access?

At least the application is functioning again.

Thanks for your help
 
Hi,

Since the issue only occurs on remote SQL Server. I suspect there might be
some issues on Windows authentication/stored credentials of Access on the
machine. A rebooting of machine after security patches may have resolved
the problem. This is also why the stored procedure work via Query Analyzer.

You may want to check the security log on the SQL Server and reboot client
machine hosting Access program to test if you encounter the similar issues
later. Also, you may want to creast a new ADP and database on the SQL
Server to see if the issue occurs on all ADPs.

Anyway it'nice to see the applciation works now. If you encounter the
issues later, please feel free to post back so we may be able to continue
to work on the issue. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
Back
Top