Call Store Procedure From Ms Access?

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
KelvsatAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
zuijdhoekConnect With a Mentor Commented:
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
 
Jonathan KellyCommented:
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
 
KelvsatAuthor Commented:
I got it. Thanx.
0
All Courses

From novice to tech pro — start learning today.