Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-07
9
Medium Priority
?
640 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

670 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