zimmer9
asked on
How to call a Sybase Stored Procedure from an Access application (mdb file) that resides on a Network drive ?
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.
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
ASKER
I should have noted that I can successfully execute the application on a Network drive from my machine.
However, when I execute the application on the Network Drive from a different machine, I cannot retrieve any data from the Stored Procedure.
However, when I execute the application on the Network Drive from a different machine, I cannot retrieve any data from the Stored Procedure.
Theres your problem. ODBC is local to each machine.
You will need to make sure you have the ODBC data source defined and working on that other machine. This may require installing the ODBC driver and/or Sybase Open Client (depending on which ODBC driver you're using - some are self-contained, some have to sit on top of the Sybase Client).
You will need to make sure you have the ODBC data source defined and working on that other machine. This may require installing the ODBC driver and/or Sybase Open Client (depending on which ODBC driver you're using - some are self-contained, some have to sit on top of the Sybase Client).
ASKER
In another application I use an ADP (Access Data Project) file. This application uses Access as the Front End an SQL Server as the back end database. To set up the connectivity with SQL Server
I perform the following setup so that the application can be executed from host machines without
regard to ODBC setup considerations.
I simply go to FILE > CONNECTION
to set up the Data Link Properties and Select a Server Name, a User Name and Password.
Then Select the Database on the server.
-------------------------- ---------- ---------- -------
Is there anyway to execute an Access application which calls a Sybase Stored Procedure
from host machines without regard to ODBC setup considerations ?
I perform the following setup so that the application can be executed from host machines without
regard to ODBC setup considerations.
I simply go to FILE > CONNECTION
to set up the Data Link Properties and Select a Server Name, a User Name and Password.
Then Select the Database on the server.
--------------------------
Is there anyway to execute an Access application which calls a Sybase Stored Procedure
from host machines without regard to ODBC setup considerations ?
ASKER
Perhaps I should state that the following steps are performed from within the Access application interface:
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------
I simply go to FILE > CONNECTION
to set up the Data Link Properties and Select a Server Name, a User Name and Password.
Then Select the Database on the server.
--------------------------
I simply go to FILE > CONNECTION
to set up the Data Link Properties and Select a Server Name, a User Name and Password.
Then Select the Database on the server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you move this to a network drive, are you still opening your MDB from the same host? ODBC drivers and data sources are per-machine, so if you're doing this from a different workstation or server you may need to check. (Mind you, the ODBC doesn't know or care which drive your MDB file is on either...)