Re: Stored Procedure from Access

My problem is that i'm linking to the sql tables. can your code be modified
so a server and login credentials be supplied?

can i use the same asp code i use within access to run a sproc?


"Dirk Goldgar" <dg@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23KB$91pYGHA.508@xxxxxxxxxxxxxxxxxxxxxxx
"scott" <sbailey@xxxxxxxxxxxxxxx> wrote in message
news:%23b35wcpYGHA.3832@xxxxxxxxxxxxxxxxxxxx
it doesn't return records, just transfers data.

can you give me some syntax? it's a hard topic to search the web for.

"Dirk Goldgar" <dg@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ugJSbcoYGHA.1764@xxxxxxxxxxxxxxxxxxxxxxx
"scott" <sbailey@xxxxxxxxxxxxxxx> wrote in message
news:eGCmnXoYGHA.4144@xxxxxxxxxxxxxxxxxxxx
Is there any way to execute a SPROC residing on the server from an
access db on my workstation? Also, the access front-end isn't an
access project, just a regular database file.

I plan to link to the sql data tables from access, but didn't want
to create a project.

Yes. You can do it with a pass-through query, or using an ADO
Command object. As pass-through query is easiest, if you don't need
to supply parameters. Does the procedure in question return records?

For a pass-through query, you just write the SQL to execute the sproc in
the syntax of the server database. The only trick there is that you
have to compose in SQL View, set the query type to Pass-Through, and set
the query's ODBC Connect Str property to point to the server database.

To use an ADO Command object, you can use code similar to this, cribbed
from one of my applications:

'------ start of code ------
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command

Dim lngReturnCode As Long
Dim blnUpdateError As Boolean

On Error GoTo Err_Handler

' Open a connection to the back-end database and set up a
' command object to work with it.

Set cnn = New ADODB.Connection
cnn.Open fncGetConnectString()

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn

' Set up and execute a call to the server to execute the appropriate
' stored procedure and get the result.

cmd.CommandText = "spInactivatePosition"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters("@PositionID") = m_lngOriginalPositionID
cmd.Parameters("@EndDate") = Me!txtEndDate
cmd.Parameters("@User") = CurrentUser()

cmd.Execute , , adExecuteNoRecords

' Get the proc's return code from the Command object.
lngReturnCode = cmd.Parameters("@return_value")

If lngReturnCode <> 0 Then
blnUpdateError = True
End If


Exit_Point:
On Error Resume Next
Set cmd = Nothing
If Not cnn Is Nothing Then
cnn.Close
Set cnn = Nothing
End If

' If the update was successful, return to the calling form.
If Not blnUpdateError Then
' Just make this form invisible so that the calling form can
' retrieve data from it.
Me.Visible = False
End If

Exit Sub
'------ end of code ------


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)




.

Relevant Pages

  • Re: SQLCeConnection
    ... Dim objConn As SqlConnection = Nothing ... instead of the SQL world. ... can ping the main server that holds the SQL Server Enterprise as will ... SQlCeConnection and when I actually need to use the SQlceConnection? ...
    (microsoft.public.sqlserver.ce)
  • Re: SQLCeConnection
    ... can ping the main server that holds the SQL Server Enterprise as will ... the server SQL and extract information from the table. ... Dim myConnection As Data.SqlClient.SqlConnection ... SQlCeConnection and when I actually need to use the SQlceConnection? ...
    (microsoft.public.sqlserver.ce)
  • Re: SQLCeConnection
    ... instead of the SQL world. ... can ping the main server that holds the SQL Server Enterprise as will ... Dim MyCommand As New Data.SqlClient.SqlCommand ... SQlCeConnection and when I actually need to use the SQlceConnection? ...
    (microsoft.public.sqlserver.ce)
  • Re: VBScriptADO Max Length of String
    ... I understand the changes to the SQLODEDB driver rather than the "SQL Server" driver and it was something I was working on when your reply arrived. ... Dim objFSO, objFile, strServers, arrServers, strServer ... Dim strTableName, strSql, objConn, strSvrName, strDBName ...
    (microsoft.public.windows.server.scripting)
  • Alternatives for long running processes?
    ... Dim cnn As Connection ... Dim sql As String ... Dim maxCompletedDate As Date ...
    (microsoft.public.access.modulesdaovba)
Loading