July 25, 2008 at 1:59 pm
Can anyone enlighten me?
I am in the process of migrating a database from Access to SQL. I have put all the data on the SQL server and now want to use the power of the server as much as possible.
I have a simple parametrized Stored Proc, which selects data from a table. How do I use the proc from within access? (I am using 2003)
thanks
July 28, 2008 at 5:38 am
Make sure that you have a reference to ADO set, then do it this way (this is for an Update query):
Dim cnMain as ADODB.Connection
Dim cmd As ADODB.Command
Set CnMain = new ADODB.Connection
Set cnMain = Currentproject.Connection 'or define your connection object explicitly
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnMain
.CommandText = "spUPProformasForAMUpdates"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@ClientNumber") = "01234"
.Parameters("@AccountManager") = "John Smith"
.Parameters("@SalesTeam") = "Major Clients"
.Parameters("@DateFrom") = "01-Jul-2008"
.Execute
End With
To return a recordset to be worked with, do it this way:
Dim rstMain as ADODB.recordset
Dim cnMain as ADODB.Connection
Dim cmd as ADODB.command
SET cnMain = Currentproject.Connection
Set rstMain = New ADODB.Recordset
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnMain
.CommandText = "spSEImportedRecords"
.CommandType = adCmdStoredProc
End With
rstMain.Open cmd, , adOpenDynamic, adLockOptimistic, adCmdStoredProc
July 28, 2008 at 8:56 am
Thanks. I will try this.
Can those parameter values me a variable?
thanks again
July 29, 2008 at 2:11 am
Yes - I have just shown hard coded values for the sake of example - as long as the variable types match the parameter definitions in the stored procedure there is no problem, but if you try to assign a string variable to an int parameter, or assign a 50 character string to a parameter defined as Varchar(20) or some such error, you will generate errors
July 29, 2008 at 6:07 am
npsarros,
You should definitely check out pass-thru queries. A pass-thru query lets you execute T-SQL directly from Access, and you can use the results just like any other Access query.
For example, to execute a stored procedure you could write
EXEC dbo.MyStoredProcedure '7/29/2008'
and to change the parameter, in VBA you could use something like
CurrentDb.QueryDefs("MyPassThru").SQL = "EXEC dbo.MyStoredProcedure '7/30/2008' "
July 29, 2008 at 7:01 am
Public Function fAddReglajCurs(conn As ADODB.Connection, _
contCr As String, moneda As Long, curs As Double, Data As Date) As Long
'return id record
Dim cmd As New ADODB.Command
On Error GoTo PROC_ERR
With cmd
.ActiveConnection = conn
.CommandText = "dbo.sab_sp_addReglajFacturaAprov"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@contCr", adVarChar, adParamInput, 20, contCr)
.Parameters.Append .CreateParameter("@monedaID", adInteger, adParamInput, , moneda)
.Parameters.Append .CreateParameter("@curs", adDouble, adParamInput, , curs)
.Parameters.Append .CreateParameter("@dataSold", adDate, adParamInput, , Data)
.Execute
fAddReglajCurs = 1
End With
Exit_Here:
Set cmd = Nothing
Exit Function
PROC_ERR:
fAddReglajCurs = 0
Select Case Err.Number
Case Else
'MsgBox "Err addd : " & Err.Number & ", " & Err.Description
End Select
Resume Exit_Here
End Function
August 19, 2008 at 7:42 am
BillMitchell. I've been banging my head against this one all morning. I'm trying to pass a date, and my code looks exactly like yours. In the store procedure the parameter is defined as a date, and when I run it I get an error because it thinks I'm passing a Varchar
I've got another post on this out today, because I gave up with dates and just passed a number. I then used DATEADD in the sp to manipulate todays date. Both these options work when you run them on SQL Server but as soon as you take them into an Access Pass Thru query, you get data type errors. Using a number gives me a precision error, and believe me I've tried every number format and initialization I can think of.
Any ideas?
August 19, 2008 at 7:47 am
If passing a date, try passing it in dd-MMM-yyyy format - e.g. '19-AUG-2008' or '01-JAN-2000' - this works with almost every database I've tried it with - Access, SQL Server, Oracle etc
August 19, 2008 at 7:57 am
Thanks Chris - I've done all that. That's why I abandoned dates and started using numbers. I honestly can't pass anything from Access to SQL Server. If I use a date in any format, and I put a single or a double quote around it, I get an error about converting from a varchar to a number. If I take the quote off I get an error about the / or - or whatever other date separator I am using. If I use a number, I get the precision error. It's an absolute nightmare.
I can't see any other option than to create a table in SQL Server that will hold the date value and join it in the query in the store procedure. I can then link the table into Access and use access to manage it. How naff is that?
August 19, 2008 at 8:05 am
The code I posted in the first reply to this topic is a straight cut & paste from one of my systems and demonstrates passing in a date as a string - passing a date variable works just as well:
Dim cnMain as ADODB.Connection
Dim cmd As ADODB.Command
Dim dtDateFrom as Date
dtDateFrom = "01-Jul-2008"
Set CnMain = new ADODB.Connection
Set cnMain = Currentproject.Connection 'or define your connection object explicitly
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnMain
.CommandText = "spUPProformasForAMUpdates"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@ClientNumber") = "01234"
.Parameters("@AccountManager") = "John Smith"
.Parameters("@SalesTeam") = "Major Clients"
.Parameters("@DateFrom") = dtDateFrom
.Execute
End With
The stored procedure is as follows:
CREATE PROCEDURE spUPPendingOrdersForAMUpdates (
@ClientNumber [varchar](5),
@AccountManager [varchar](3),
@SalesTeam [varchar](3),
@DateFrom [datetime]) AS
UPDATE tblPendingOrders
SET
SalesPersonCode = @AccountManager,
SalesTeamCode = @SalesTeam
WHERE
ClientNumber = @ClientNumber
AND
OrderDate >= @DateFrom)
GO
August 19, 2008 at 8:22 am
Chris, I will try this and report back.
Trouble is that is a lot of code compared to what everyone else is able to achieve - 1 line is a pass-thru query
August 19, 2008 at 8:59 am
Nope. I can't get the connection to work. Don't know enough about it and don't have time to look it up. Here's hoping someone can tell me what I need to do to run a pass thru query like they can.
Meanwhile, for the sake of speed, I'm going to create a parameter table that the stored procedure can read and Access can control.
Thanks anyway
August 19, 2008 at 10:05 am
If you are using the stored procedure as the recordsource for a form or report, you can use the InputParameters property of the form/report to pass the data to the stored procedure.
Set the Recordsource of the form or report to the name of the stored procedure, the RecordSourceQualifier to the sp's owner (e.g. dbo) and then add your InputParameters in this way (parameters separated by commas):
@RangeFrom=[forms]![frmProduceDailyReports]![txtDateFrom], @RangeTo=[forms]![frmProduceDailyReports]![txtDateTo]
You have to be careful, as sometimes when you edit the form or report and save your changes, the input parameters disappear!
August 19, 2008 at 11:15 pm
From Access ,use dateserial(year,month,day)
and use in client app.- 3 textbox -year,month,day
August 20, 2008 at 2:11 am
Thanks for all the tips. I'm not using any forms or anything. We provide the pass thru query and the people who run it will export to Excel or whatever takes their fancy.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply