troubleshooting Question

How to call a Sybase Stored Procedure from an Access application (mdb file) that resides on a Network drive ?

Avatar of zimmer9
zimmer9Flag for United States of America asked on
Microsoft AccessSybase Database
6 Comments1 Solution888 ViewsLast Modified:
I have created an Access application using an mdb file that resides on my local C: drive and the application works fine. This application calls a Sybase Stored Procedure for which I do not have the ability to update.
The Sybase Stored Procedure is in the attached code snippet.
I use the following ODBC configuration for the Sybase connection:
********************************************************************************************************************
ODBC Data Source Administrator
User DSN tab
User Data Sources:  
Name                             Driver                                                               Add
PAP_ED                         DataDirect  4.0 Sybase Wire Protocol             Remove            
                                                                                                              Configure  <-- click on

4) ODBC Sybase Wire Protocol Driver Setup
    General tab
    Data Source Name         PAP_ED_TEST1
    Network Library Name   Winsock
    Network Address          nyuu,10467
    Database Name             a2query

    Test Connect button    <-- click on
   
    Logon to Sybase      
    Network Library                   WInsock      
    Network Address                nyuudb01,10467
    Login ID                                nitod
    Password                            ******
    Database                             a2query
**********************************************************************************************************************
In the attached code below the Stored Procedure is the code I use in the Access application to
retrieve the data from the Stored Procedure.

Do you know what revisions I would make to this application when I move it to a Network Drive
so that it will continue to work with the Sybase Stored Procedure ?

I tried to execute the application as is and I can successfully call the Sybase Stored Procedure when I execute the application from my local C: drive but when I execute this Access application from a Network drive, I DO NOT retrieve back any data from the Sybase Stored Procedure.
CREATE PROCEDURE getAccountRefData
(
    @application  varchar(15),
    @accountNumber char(9),
    @accountSystem int
)
AS
 
BEGIN
/*
************************************************************************
Dynamic SQL for MDA
************************************************************************
*/
declare @execCmd varchar(250),@fixParam varchar(2000),@param1 varchar(8000)
select @fixParam = '[UniqueExecutionID ='+newid(1)+'][ServerUserID='+convert(varchar,suser_id())+'][HostName='+host_name()+']'
select @execCmd=  ' exec getAccountRefData '
select @param1 = "@application="+case when @application is null then "NULL" else "'"+rtrim(@application)+"'" end +"," +
"@accountNumber="+case when @accountNumber is null then "NULL" else "'"+rtrim(@accountNumber)+"'" end +"," +
"@accountSystem="+case when @accountSystem is null then "NULL" else rtrim(convert(varchar,@accountSystem)) end
exec('--'+@fixParam+@execCmd+@param1)
 
/////////////////////////////////////////////////////////////////////
 
select DISTINCT TA.accountNumber, TA.accountSystemRN, TA.accountStatus, TA.accountCategoryRN,countryOfCitizenship, countryOfResidence,
       TGA.fullname1, TGA.address1, TGA.city, TGA.stateCode, RN.refName, TGA.postalCode
  from rdeTAPSAccount TA,
       TAPSGeneralAddress TGA,
       TAPSCustomerDesiStreetAccount TCDSA,
       refName RN
where TA.accountNumber = TCDSA.accountNumber
  and TA.accountSystemRN = TCDSA.accountSystemRN
  and TCDSA.accountNumber = TGA.accountNumber
  and TCDSA.accountSystemRN = TGA.accountSystemRN
  and TGA.mainAddressInd = 'Y'
  and TGA.countryCode = RN.refCode
  and TA.accountNumber = @accountNumber
  and TA.accountSystemRN = @accountSystem
 
  end
**************************************
There are 4 params as follows:
 
0 -  result  -  integer
1 -  @application
2 -  @accountNumber
3 -  @accountSystem
///////////////////////////////////////////////////////////////////////
Set oConn = CreateObject("ADODB.Connection")
 
oConn.Open "DSN=PAP_ED_RPT1;" & _
 "Uid=fsmainXX;" & _
 "Pwd=XXXXXX"
 
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
 
rst.Open "SELECT AccountNumber FROM tblNameAddress", CurrentProject.Connection
 
Do Until rst.EOF
 
Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = oConn
    .CommandType = adCmdText
    .CommandText = "exec getTATGATCDSAByAccount @ApplnNm='test', @accountNumber='" & rst("AccountNumber") & "'"
    .CommandText = .CommandText & ",@accountSystem=1435,@selectFields='accountNumber, accountStatus"
    .CommandText = .CommandText & ", fullName1, address1, city, stateCode, postalCode, taxID, accountClassification"
    .CommandText = .CommandText & ", fullName2, countryCode, countryOfResidence, countryOfCitizenship, accountCategory"
    .CommandText = .CommandText & ", cashIndicator, marginIndicator, codIndicator'"
    Set recNameAdress = .Execute
End With
 
strAccountNumber = recNameAdress("accountNumber")
strFullName1 = Nz(recNameAdress("fullName1"), " ")
strFullName2 = Nz(recNameAdress("fullName2"), " ")
 
CurrentProject.Connection.Execute "Update tblNameAddress Set FullName1 ='" & Replace(strFullName1, "'", "''") & "', " & _
"statecode ='" & strStateCode & "', postalcode ='" & strpostalCode & "', taxID ='" & strtaxID & "', "  
    
recNameAdress.Close
 
rst.MoveNext
Loop
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros