|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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).
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"smarty" <smarty[ at ]nospam.nospam> wrote in message
news:56FCB40C-7F60-4598-85FA-6E5D2ADBF96A[ at ]microsoft.com...
[Quoted Text] > 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
|
|
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?
"Peter Yang [MSFT]" wrote:
[Quoted Text] > 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,
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
"Peter Yang [MSFT]" wrote:
[Quoted Text] > 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.
> ======================================================
>
>
>
|
|
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.
======================================================
|
|
|