General discussion

  • Creator
    Topic
  • #2091367

    Run Stored SQL Server Proc.from Access97

    Locked

    by frankus ·

    How do I program in VBA (MSAccess97) the execution of a Stored Procedure in SQL Server?

All Comments

  • Author
    Replies
    • #3880168

      Run Stored SQL Server Proc.from Access97

      by glen_mcleod ·

      In reply to Run Stored SQL Server Proc.from Access97

      The most straightforward way (using VBA) would be to create an ADO Command object, connect it to your SQL Server, then use parameters to execute the query.

      Dim conServer as ADODB.Connection
      Dim cmStoredProc as ADODB.Command

      Set conServer = New ADODB.Connection
      conServer.ConnectionString = “Your connection information here” ‘ or you could re-use an existing Connection object
      conServer.Open

      Set cmStoredProc = New ADODB.Command
      Set cmStoredProc.ActiveConnection = conServer
      cmStoredProc.CommandType = adCmdStoredProc
      cmStoredProc.CommandText = “StoredProc Name here”
      ‘ for each parameter required by the StoredProc:
      cmStoredProc.Parameters.Append _
      cmStoredProc.CreateParameter(“Name”, Type, Direction, Size)

      ‘ later you can determine the parameter values for each:
      cmStoredProc.Parameters(“Name”).Value = sWhatever

      ‘ for non record returning StoredProcs, just execute it
      cmStoredProc.Execute ‘with an optional long variable for records affected

      ‘ for record returning StoredProcs you need a recordset object:
      rsStoredProc.Open cmStoredProc

      I use parameters wherever possible, that way, once the command has been defined and the proper parameters appended (which happens once), you can re-use the command asmuch as you want just by changing the parameters and re-opening the recordset.

      Glen

    • #3879856

      Run Stored SQL Server Proc.from Access97

      by donq ·

      In reply to Run Stored SQL Server Proc.from Access97

      Never tried this BUT:
      1. Copy the SQL stored procedure to the clipboard.

      2. Open Access database (with like table structure)

      3. Open query and paste appropriate tables in the top of the queryview window.

      4. switch to SQL view and Paste
      That will get the server code where it is stored in Access, or of course you could drag and drop your query elements and let Access build your VBA code automatically?

      Good luck. Interesting and it should work IF your tables match.

      • #3880601

        Run Stored SQL Server Proc.from Access97

        by frankus ·

        In reply to Run Stored SQL Server Proc.from Access97

        The SQL syntax in SQL server is different than the one used in Access97.
        Also, I want to run the procedure in SQL Server since it runs much faster. In Access Query it often takes up to 15 minutes to run (1 Million plus records). I need to take advantage of the compiled speed in SQL Server.

    • #3880599

      Run Stored SQL Server Proc.from Access97

      by frankus ·

      In reply to Run Stored SQL Server Proc.from Access97

      This question was closed by the author

Viewing 2 reply threads