Inserting data into Access form from SQL Stored Procedure

**MGFoster** · Nov 12 '05, 03:12 PM

Re: Inserting data into Access form from SQL Stored Procedure

Josh Strickland wrote:
[color=blue]
> I am attempting to create an Access database which uses forms to enter
> data. The issue I am having is returning the query results from the
> Stored Procedure back in to the Access Form.
>
> tCetecM1CUST (SQL Table that contains the Customer Information)
> tAccountingDeta il (SQL Table that contains the information in the
> form)
> frmAccountingEn try (Access form used to enter data)
> spGetCustomerIn formation (Stored Procedure which returns data using
> variable CUSTOMER_NUMBER entered in the Access.)
>
> Scenario is this. Open form, Enter 'Job Number' and 'Customer Number',
> form uses 'AfterUpdate' to run this...
>
> Private Sub CUSTOMER_NUMBER _AfterUpdate()
> Set gcn = Nothing
> Dim sConnect As String
> sConnect = "PROVIDER=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
> SECURITY INFO=FALSE;INIT IAL CATALOG=x;DATA SOURCE=x"
> Set gcn = New ADODB.Connectio n
> gcn.CursorLocat ion = adUseClient
> gcn.Open sConnect
>
> 'On Error GoTo ExitProcedure
> Dim rs As ADODB.Recordset
> Set rs = New ADODB.Recordset
> 'Call doConnect
>
> Dim cmd As ADODB.Command
> Set cmd = New ADODB.Command
>
> With cmd
> .CommandText = "spGetCustomerI nformation"
> .CommandType = adCmdStoredProc
> .Parameters.App end .CreateParamete r("@CUSTOMER_NU MBER", adVarChar,
> adParamInput, 6, Forms!frmAccoun tingEntry!CUSTO MER_NUMBER.Valu e)
> Set .ActiveConnecti on = gcn
> End With
>
> Set rs = New ADODB.Recordset
> rs.CursorLocati on = adUseServer
> rs.Open cmd, , adOpenStatic, adLockReadOnly
> Set rs = cmd.Execute
> If Not (rs.EOF And rs.BOF) Then
> MaybeMatch = True
> Else
> MaybeMatch = False
> End If
>
> ExitProcedure:
> On Error Resume Next
> Set rs = Nothing
>
> End Sub
>
> This passes the variable CUSTOMER_NUMBER which is located in the
> Access form to the Stored Procedure which is here..
>
> CREATE PROCEDURE dbo.spGetCustom erInformation
> (@CUSTOMER_NUMB ER varchar(6))
> AS
> SELECT CUSTOMER_NUMBER , CUSTOMER_NAME, ADDRESS_1, ADDRESS_2,
> ADDRESS_3, ADDRESS_4, SHIP_ADDRESS_1, SHIP_ADDRESS_2, SHIP_ADDRESS_3,
> SHIP_ADDRESS_4
> FROM dbo.tCetecM1CUS T
> WHERE (CUSTOMER_NUMBE R = @CUSTOMER_NUMBE R)
>
> Which then does nothing as far as returning the data to the current
> form. I can run the stored procedure in Access and a Message Box will
> come up prompting me to enter the 'CUSTOMER_NUMBE R'. If the number
> entered matches a record, then the record is displayed.
>
> So, what am I missing here? I feel like there must be another piece of
> code that puts the data back into the current record or form.
>
> Thanks to anyone out there who has a suggestion.
>
> -Josh[/color]
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't work w/ ADO - mainly 'cuz I can get the info I need in DAO in
about 5 lines of code where ADO requires 10-15 lines. Anyway, . . .
I'll assume you're using Access 2000, or Access XP; can't you set the
form's Recordset to the recordset returned by the stored procedure?
E.g.:

Set Me.Recordset = rs

Here's some code from Acc XP Help on "Recordset Property."

Global rstSuppliers As ADODB.Recordset
Sub MakeRW()
DoCmd.OpenForm "Suppliers"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.Cu rsorLocation = adUseClient
rstSuppliers.Op en "Select * From Suppliers", _
CurrentProject. Connection, adOpenKeyset, adLockOptimisti c

' See - this line sets the form's recordset...::: mgf
Set Forms("Supplier s").Recordse t = rstSuppliers

' Not sure why this line is needed, but it works...:::mgf
Forms("Supplier s").UniqueTa ble = "Suppliers"

End Sub

HTH,
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4dfQYechKq OuFEgEQKQGwCdHA 2DZvZUlq7C82CXf TdxrysnsNwAn2vd
AQO2Wudr+5FPVuV 37Fangt8/
=Rb4i
-----END PGP SIGNATURE-----

**Fletcher Arnold** · Nov 12 '05, 03:13 PM

Re: Inserting data into Access form from SQL Stored Procedure

"Josh Strickland" <jstrickland@bt celectronics.co m> wrote in message
news:ad196ba4.0 310101200.637aa 0de@posting.goo gle.com...[color=blue]
> I am attempting to create an Access database which uses forms to enter
> data. The issue I am having is returning the query results from the
> Stored Procedure back in to the Access Form.
>
> tCetecM1CUST (SQL Table that contains the Customer Information)
> tAccountingDeta il (SQL Table that contains the information in the
> form)
> frmAccountingEn try (Access form used to enter data)
> spGetCustomerIn formation (Stored Procedure which returns data using
> variable CUSTOMER_NUMBER entered in the Access.)
>
> Scenario is this. Open form, Enter 'Job Number' and 'Customer Number',
> form uses 'AfterUpdate' to run this...
>
> Private Sub CUSTOMER_NUMBER _AfterUpdate()
> Set gcn = Nothing
> Dim sConnect As String
> sConnect = "PROVIDER=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
> SECURITY INFO=FALSE;INIT IAL CATALOG=x;DATA SOURCE=x"
> Set gcn = New ADODB.Connectio n
> gcn.CursorLocat ion = adUseClient
> gcn.Open sConnect
>
> 'On Error GoTo ExitProcedure
> Dim rs As ADODB.Recordset
> Set rs = New ADODB.Recordset
> 'Call doConnect
>
> Dim cmd As ADODB.Command
> Set cmd = New ADODB.Command
>
> With cmd
> .CommandText = "spGetCustomerI nformation"
> .CommandType = adCmdStoredProc
> .Parameters.App end .CreateParamete r("@CUSTOMER_NU MBER", adVarChar,
> adParamInput, 6, Forms!frmAccoun tingEntry!CUSTO MER_NUMBER.Valu e)
> Set .ActiveConnecti on = gcn
> End With
>
> Set rs = New ADODB.Recordset
> rs.CursorLocati on = adUseServer
> rs.Open cmd, , adOpenStatic, adLockReadOnly
> Set rs = cmd.Execute
> If Not (rs.EOF And rs.BOF) Then
> MaybeMatch = True
> Else
> MaybeMatch = False
> End If
>
> ExitProcedure:
> On Error Resume Next
> Set rs = Nothing
>
> End Sub
>
> This passes the variable CUSTOMER_NUMBER which is located in the
> Access form to the Stored Procedure which is here..
>
> CREATE PROCEDURE dbo.spGetCustom erInformation
> (@CUSTOMER_NUMB ER varchar(6))
> AS
> SELECT CUSTOMER_NUMBER , CUSTOMER_NAME, ADDRESS_1, ADDRESS_2,
> ADDRESS_3, ADDRESS_4, SHIP_ADDRESS_1, SHIP_ADDRESS_2, SHIP_ADDRESS_3,
> SHIP_ADDRESS_4
> FROM dbo.tCetecM1CUS T
> WHERE (CUSTOMER_NUMBE R = @CUSTOMER_NUMBE R)
>
> Which then does nothing as far as returning the data to the current
> form. I can run the stored procedure in Access and a Message Box will
> come up prompting me to enter the 'CUSTOMER_NUMBE R'. If the number
> entered matches a record, then the record is displayed.
>
> So, what am I missing here? I feel like there must be another piece of
> code that puts the data back into the current record or form.
>
> Thanks to anyone out there who has a suggestion.
>
> -Josh[/color]

It's a bit hard to see exactly where it's going wrong but I would check here
first:
[color=blue]
> rs.Open cmd, , adOpenStatic, adLockReadOnly
> Set rs = cmd.Execute[/color]

You've already set rs, then you open it, then you re-set it? Why?

As you step through your code, can you check this is giving the value you
expect, before passing it to the stored procedure:

Forms!frmAccoun tingEntry!CUSTO MER_NUMBER.Valu e

Also what about some more general error handling for the sub and why is
there no dim statement for MaybeMatch and why does it not follow a naming
convention.

Just some ideas

Fletcher

Inserting data into Access form from SQL Stored Procedure

Inserting data into Access form from SQL Stored Procedure

Comment

Comment