Displaying fields from SQL Stored Procedures in MS ACCESS 2003 fields

[JeffBarker]

Hi all,

We are currently looking at migrating some of our MS Access 2003 databases onto SQL Server in order to speed them up, but we'll still be looking to use Access 2003 as the user frontend.

I have been tasked with displaying one "parameter" (my boss's word, not mine!) from the SQL Stored Procedure in a field on a form using VBA - but I have no clue on how to get started.

Any advice you guys can give would be greatly appreciated - I have the name of the SQL DB, its server location and the name of the SProc and Input Parameter, I just don't know how to get Access talking to SQL to display the information I need.

Many Thanks in advance,

Jeff.

*EDIT* After searching through the rest of forum for Stored Procedure threads/posts, I really can't find anything that helps. In addition to this, I've been asked to carry this out without linking the MSAccess frontend to the SQL backend.*EDIT*

 

[winshent]

Hi All.. I work with Jeff and I've been looking at this..

This code works so far as that I can loop thru the recordset returned from the stored procedure.. However, I still cannot bind it to the form.

Private Sub RetrieveSiteInformation()

  Dim cmd As New ADODB.Command
  Dim cnn As New ADODB.Connection
  Dim rs As ADODB.Recordset, f As ADODB.Field

  With cnn
    .Provider = "SQLOLEDB"
    .ConnectionString = "data source=UKFCSVR;initial catalog=ACACB;Trusted_Connection=Yes"
    .Open
  End With

  Dim param1  As ADODB.Parameter

  If Nz(txtSiteID_Search.Value, vbNullString) <> vbNullString Then
    Set param1 = cmd.CreateParameter("@SiteID", adBigInt, adParamInput)
    param1.Value = txtSiteID_Search.Value
    cmd.Parameters.Append param1
  End If

  With cmd
   .ActiveConnection = cnn
   .CommandText = "spSiteInformation_Retrieve"
   .CommandType = adCmdStoredProc

    ' THIS FAILS
    Me.Recordset = .Execute

    ' THIS LOOP WORKS FINE
    '    Set rs = .Execute
    '    rs.MoveFirst
    '
    '    For Each f In rs.Fields
    '      Debug.Print f.Name
    '    Next
    '
    '    With rs
    '      Do While Not .EOF
    '        Debug.Print ![CompanyName] & " " & ![Postcode]
    '        .MoveNext
    '      Loop
    '
    '    End With
  End With

  cnn.Close

End Sub

Anyone have any ideas of how to get this to work..

It Fails on

Me.Recordset = .Execute

with the error 'Operation is not supported for this type of object'..

Just to add, this is an mdb file and not an adp project..

 

[winshent]

Hi

Have now managed to fix this with help from the guys on Stack Overflow..

These were the lines of code that fixed it..

    With cn
        .Provider = "Microsoft.Access.OLEDB.10.0"
        .Properties("Data Provider").Value = "SQLOLEDB"
        .Properties("Data Source").Value = "Server"
        .Properties("Integrated Security").Value = "SSPI"
        .Properties("Initial Catalog").Value = "Test"
        .Open
    End With

Here is the complete code..

    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim param1  As New ADODB.Parameter

    With cn
        .Provider = "Microsoft.Access.OLEDB.10.0"
        .Properties("Data Provider").Value = "SQLOLEDB"
        .Properties("Data Source").Value = "Server"
        .Properties("Integrated Security").Value = "SSPI"
        .Properties("Initial Catalog").Value = "Test"
        .Open
    End With

    If Nz(txtSiteID_Search, vbNullString) <> vbNullString Then
        Set param1 = cmd.CreateParameter("@SiteID", adBigInt, adParamInput)
        param1.Value = txtSiteID_Search
        cmd.Parameters.Append param1
    End If

    With cmd
        .ActiveConnection = cn
        .CommandText = "spSiteInformation_Retrieve"
        .CommandType = adCmdStoredProc
        Set Me.Recordset = .Execute
    End With

 

[dara11]

I just want to say thank you for the information.
It is very valuable for me..newbie here and encountering the same situation.
Thanks and keep up the good work..