Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 14th, 2004, 03:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default Create Stored Procedure in VBA

How do I create a stored procedure in Access .ADP using VAB?

My SQL sting looks like:

Code:
mySQL6 = "SELECT [T_Tooling].ThreadLength " & _
                    "FROM [T_Tooling] INNER JOIN [T_ToolTypes] ON [T_Tooling].ToolTypeDesc = [T_ToolTypes].ToolTypeDesc " & _
                    "GROUP BY [T_Tooling].ToolTypeDesc, [T_Tooling].ThreadLength " & _
                    "HAVING ((([T_Tooling].ToolTypeDesc) = " & "'" & "Mandral" & "'" & ") AND (([T_Tooling].ThreadLength) Is Not Null)) " & _
                    "ORDER BY [T_Tooling].ThreadLength"
I tired:

Code:
Dim mySQL6 As String

mySQL6 = "CREATE PROCEDURE '00CreateSP' As " & _
        "SELECT     au_id, au_lname  FROM dbo.authors"

    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset

    ' Open a connection without using a Data Source Name (DSN).
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "driver={SQL Server};" & _
        "server=<MyServerName>;database=pubs"
    cnn.ConnectionTimeout = 30
    cnn.Open

        cnn.Execute mySQL6

    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing

'-------

Exit_btnCreateSToredProced_Click:
    Exit Sub

Err_btnCreateSToredProced_Click:
    MsgBox Err.Description
    Resume Exit_btnCreateSToredProced_Click

End Sub
But that did not work.
__________________
Mitch




Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored procedure and VBA jesseleon Access VBA 7 September 15th, 2011 12:46 PM
Get Stored Procedure Output using Access VBA LandLocked Access VBA 3 June 20th, 2007 11:20 AM
Execute Stored Procedure from VBA Romashka Access VBA 1 February 22nd, 2007 12:52 PM
Create Stored Procedure and Triggers gadhiav SQL Server 2000 4 December 13th, 2004 11:28 AM
MS SQL Stored procedure and VBA jesseleon Pro VB Databases 0 July 30th, 2004 01:16 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.