Solved

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

Posted on 2013-11-07
9
632 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 34

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 34

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 34

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 34

Author Comment

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

Author Closing Comment

by:Mike Eghtebas
ID: 39673043
Thank you.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

808 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