Solved

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

Posted on 2013-11-07
9
626 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now