SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exec Stored Procedure?

    I'm using MS Access and I have a stored procedure which I can run by entering the exec command in a Pass-Through query (linked to the SQL server), the stored procedure is on the SQL server, how can I pass variables from my local machine in to the call to the stored procedure? As it is now I have to rewrite the execution line with the new values to the different variables all the time. I want to be able to pass them on from e.g a form or a table. Do you have to use VBA for this, in that case, how?

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,635
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    You have to use VBA. Most expedient method is to generate querydefs on the fly to input the parameters.

    WWB

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99
    You have to use VBA. Most expedient method is to generate querydefs on the fly to input the parameters.

    WWB
    I'm trying this, but it doesn't work...

    Code:
    Public Sub NewSQLStatement()
    
        Dim rst As New ADODB.Recordset
        Dim cnn1 As ADODB.Connection
        Dim strSQL As String
        Dim Startdatum As String
            
        Startdatum = "'20040101 00:00:00'"
            
        strSQL = "Exec sp_wSeeligAndelprocent " & Startdatum & ", '20040131 00:00:00', '20040331 00:00:00', 100, 4000, 8999"
    
        Set cnn1 = New ADODB.Connection
    
        cnn1.ConnectionString = "With correct connection string"
        cnn1.ConnectionTimeout = 240
        cnn1.Open
        rst.Open strSQL, cnn1
        cnn1.Close
    End Sub
    But I get an error that the Time period has been exceded, it does seam to ignore my "cnn1.ConnectionTimeout" definition since it doesn't care what I define it as. Am I far off with this soloution or???

  4. #4
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,635
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Inside of Access, I avoid ADO like the plague. DAO is faster and alot more access friendly, and it is what is actually running anyhow. With DAO this works for me:

    Code:
    Set QDf = DB.CreateQueryDef(strQDefName)
    QDf.Connect = ConnSTR 'I am using an ODBC connection here
    QDf.SQL = "EXEC wwb_GetPersonsForTopic " & SelectedRecords(intCount)
    WWB

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99
    Inside of Access, I avoid ADO like the plague. DAO is faster and alot more access friendly, and it is what is actually running anyhow. With DAO this works for me:

    Code:
    Set QDf = DB.CreateQueryDef(strQDefName)
    QDf.Connect = ConnSTR 'I am using an ODBC connection here
    QDf.SQL = "EXEC wwb_GetPersonsForTopic " & SelectedRecords(intCount)
    WWB
    Sounds G-R-E-A-T! Exactly what I'm looking for! But how you declare your varibles?

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Pfeni
    Sounds G-R-E-A-T! Exactly what I'm looking for! But how you declare your varibles?
    It's ok I've managed anyway, thanks for all your help, pricless!! Thanx!

    /Pfenix

  7. #7
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,635
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Glad you got it. For posterity:

    1) Add a reference to Data Access Objects Library from the tools->References menu.

    2) Declare the following:
    Code:
    Dim DB as DAO.Database
    Dim Qdf as DAO.QueryDef
    WWB


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •