Solved

Call Store Procedure From Ms Access?

Posted on 2001-09-12
3
274 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

707 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