General discussion

Locked

Run Stored SQL Server Proc.from Access97

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

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Run Stored SQL Server Proc.from Access97

by Glen_McLeod In reply to Run Stored SQL Server Pro ...

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

Collapse -

Run Stored SQL Server Proc.from Access97

by Glen_McLeod In reply to Run Stored SQL Server Pro ...

oops, if the StoredProc doesn't have any parameters, you don't need to append any.

Collapse -

Run Stored SQL Server Proc.from Access97

by frankus In reply to Run Stored SQL Server Pro ...

Thank you very much. I knew I had to get into ADO sooner or later.
This is very helpful.

Collapse -

Run Stored SQL Server Proc.from Access97

by donq In reply to Run Stored SQL Server Pro ...

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.

Collapse -

Run Stored SQL Server Proc.from Access97

by frankus In reply to Run Stored SQL Server Pro ...

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.

Collapse -

Run Stored SQL Server Proc.from Access97

by frankus In reply to Run Stored SQL Server Pro ...

This question was closed by the author

Back to Web Development Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums