Solved

Stored procedure Access front end w/ sql-server backend...

Posted on 2013-11-07
9
631 Views
Last Modified: 2013-11-24
I am looking for vba code (connection and binding to the form, etc) to call spThreeCustFields from Access continuous form and display its content.

Create Proc spThreeCustFields
As
Begin
   SELECT [CustomerID]
      ,[CompanyName]
      ,[ContactName]
   FROM [Northwind].[dbo].[Customers]
End

Thank you.
0
Comment
Question by:Mike Eghtebas
  • 5
  • 4
9 Comments
 
LVL 84
ID: 39629872
The code below will open a Recordset based on your SP, and then bind "YourForm" to that recordset. "YourForm" would be expected to have controls bound to the Fields in the recordset:

Dim rst As New ADODB.Record
Dim con As New ADODB.Connection

con.CursorLocation = adUseClient
con.Open "connect string"

rst.Open "EXEC spThreeCustFields", con

Forms("YourForm").Recordset = rst

Open in new window

0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39629901
I am getting an error:

Private Sub Form_Load()
    Dim rst As New ADODB.Record   '<--*****Complie error: User-defined type not defined
    Dim con As New ADODB.Connection
    
    con.CursorLocation = adUseClient
    con.Open "Data Source=.;Initial Catalog=Northwind;Integrated Security=True"
    
    rst.Open "EXEC spThreeCustFields", con
    
    Me.Recordset = rst
End Sub

Open in new window


Also please check my connection string. To make sure, I have a continuous form with three text boxes bound and named as the three fields (CustomerID, CompanyName, and ContactName) included in the proc.
0
 
LVL 84
ID: 39629914
You apparently don't have a reference to the ADO library. As with all other versions of Access, you open the VBA Editor and click Tools - References, then find the ADO library and check the version you want to use (I used 2.7, I believe).
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39629930
I am getting another error at line 6:

Run-time error '-2147217887(80040e21)':
Multiple-step OLE DB operation generated errors, Check each OLE DB status value, if available. No work was done.

con.Open "Data Source=.;Initial Catalog=Northwind;Integrated Security=True"

also tried:
con.Open ".;Initial Catalog=Northwind;Integrated Security=True"
con.Open "(local);Initial Catalog=Northwind;Integrated Security=True"


fyi, I use the following in my c# app and it works fine:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="csDatabase"
         connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True"
         providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

Open in new window

0
 
LVL 84
ID: 39630381
The "multistep" error often means something is misspelled, or you're referring to a table incorrectly.

Can you run the SP directly in SQL Server Management Studio?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39630757
The stored procedure is tested before in SSMS and it works. This is what I have used in vs 2010 successfully:

connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True"

but is the following syntax correct for access?

con.Open ".;Initial Catalog=Northwind;Integrated Security=True"

Mike
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39630777
That connection string doesn't look right. You've got to tell Access the servername and such, so something like this perhaps:


Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;

There are many other examples here: http://www.connectionstrings.com/sql-server/
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39630799
Thanks for the link; it looks good.
0
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 39673043
Thank you.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question