Executing SQL in Access Project

[nishantd]

If I just wanted to run a stored procedure (or query even) from within an Access Project, how would i go about doing that? Is the DoCmd.RunSQL method only used for Access databases?

thanks,
Nishant

 

[Kodo]

if you're using VBA in a project, then DoCmd.RunSQL should fire just fine, Though my preference is to Conn.execute(SQL).

 

[nishantd]

CurrentProject.Connection.Execute correct? I see how you can put SQL in there, but can you pass a stored procedure with parameters in there as well?

Thanks,
Nishant

 

[Kodo]

CurrentProject.Connection.Execute correct? I see how you can put SQL in there, but can you pass a stored procedure with parameters in there as well?

Thanks,
Nishant

ironically it's not much different than any other vb syntax.

If I wanted to pass a variable say an ID var to a query/sproc I just do somethng like this

Dim Conn as connection,SQL as string,MyID as integer,RS as recordset
Set conn=currentproject.connection
Set RS=ADODB.Recordset
MyID=1

SQL="Select BLAH from TABLEA where ID="& MyID
rs.open sql, conn,0,1
....

or for an update
Dim Conn as connection,SQL as string,MyID as integer
Set conn=currentproject.connection

SQL="Update TABLEA set blah='something' where ID="& MyID
conn.execute(SQL)
.....


if you had a sproc, just do something like this

SQL="SPROCNAME "&MyID
conn.execute(SQL)

make sense?

 

[nishantd]

makes absolute sense. thanks!