Solved

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

Posted on 2013-11-07
9
635 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 85
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 85
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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