Solved

Call Store Procedure From Ms Access?

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

636 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