Execute SQL Server 2000 stored procedure from Access 2000
-
I have gotten the form to update the sql pass thru query for the stored procedure and the variables are updating correctly. How do I execute the stored procedure which creates tables in SQL Server from Access 2000?
Any help would be greatly appreciated.
Thx,
CLM
-
if i understand correctly your form may be doing something along the lines of
dim db as database
set db=currentdb()
dim qdef as new querydef
set qd=db.querydefs("myquery")
qdef.sql="create table dbo.mytable.......blah blah....."
you can then simply add
qdef.execute
please accept my apologies if the code isn't 100% correct but by laptop with access is not available to check the code at the minute.
hope this helps
MVDBA
-
Actually all I am doing is updating the pass thru query that allows me to send 2 variables needed for a stored procedure on SQL Server. the pass-thru does NOT contain the create sql statement that is in the stored procedure.
CLM
-
dim qdf as querydef
dim sqlconnectstring as string
sqlconnectstring = codedb.tabledefs("MySQLServerAttachedTable").connect
set qdf = new codedb.createquerydef("")
qdf.connect = sqlconnectstring
qdf.sql = "spMyStoredProc " & myvariable1 & "," & myvariable2
qdf.execute
OR
set rs = qdf.openrecordset()
qdf.close
set qdf = nothing
-
Don't forget you need suitable permissions on the SQL2000 box to do this.
You may have read/write access to tables but DDL statements such as CREATE, ALTER and DROP may not have been granted to you.
I certainly wouldn't let an Access user create tables in my databases
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply