Solved

Call Store Procedure From Ms Access?

Posted on 2001-09-12
3
278 Views
Last Modified: 2008-03-17
How can I call the store procedure in SQL and return the result back to Ms Access??

In my code, i already using the below method

    Dim tdfOldLink As TableDef
    Dim tdfNewLink As TableDef
    Dim strConnect As String
           
    Set tdfNewLink = CurrentDb.CreateTableDef("Temp")
    tdfNewLink.Connect = "ODBC;"
    tdfNewLink.SourceTableName = "SAMPLE"
    tdfNewLink.Attributes = dbAttachSavePWD
    CurrentDb.TableDefs.Append tdfNewLink
    strConnect = CurrentDb.TableDefs("Temp").Connect
    CurrentDb.TableDefs.Delete "Temp"



Thankx
0
Comment
Question by:Kelvsat
3 Comments
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6478736
to call a stored procedure from sql server using ADO

dim recTheResultSet as New ADODB.Recordset
dim cmdTheStoredProcedure as New ADODB.Command

cmdTheStoredProcedure.CommandText = "spYourStoredProc"
cmdTheStoredProcedure.Type = StoredProcedure
cmdTheStoredProcedure.Connection = CurrentProject.Connection


Set recTheResultSet = cmdTheStoredProcedure.Execute
0
 
LVL 4

Accepted Solution

by:
zuijdhoek earned 100 total points
ID: 6478760
Kelvsat,

I don't understand what the purpose of your code.
You are trying to link a table dynamically and then what?
If you want to run a stored procedure (SQL Server?) using Access you have to create a pass-through query
(Create new query, select form the menubar Query -> Sqlspecific -> Pass-through. Check the properties of the query,  define the connectionstring and make sure records will be returned )

In case you are SQL Server as backend database you can write a SQL-statement somewhat like this

EXEC <YourProcedureName>

In case you want to store the results of this query in a new table you can create another make-table query which invokes the pass-through query.

Hope this might give you some idea.

Mark
0
 

Author Comment

by:Kelvsat
ID: 6489460
I got it. Thanx.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

828 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