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*

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..

Hi

Have now managed to fix this with help from the guys on Stack Overflow (http://stackoverflow.com/questions/4784820/ms-access-bind-form-to-results-from-stored-procedure)..

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

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..