Trying to call a stored procedure and getting error 3

[ajaeger]

I'm trying to run a stored procedure from an Access form. Below is the code. I'm getting the error message that The Microsoft Jet database engine cannot find the input table or query 'dbo.'

Private Sub cmdRefreshTbl_Click()

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.Open "ODBC;DATABASE=IMISDB;UID=sa;PWD=123;DSN=iMIS MS SQL"

Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn

cmd.CommandText = "dbo.rpt_MO_Profile_Export"
cmd.CommandType = adCmdStoredProc

Set rst = cmd.Execute

End Sub

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[abaldwin]

From the looks of it you are declaring a connection item (cnn) and then opening it.

Then you turn around and set the connection to the current connection (Access Database) and then try to run the sp.

I belienve you can simply remove the line
set cnn = currentproject.connection and run you code. If the sp exist on the sql server it should run.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[genomon]

Also if the tables are linked this syntax may be incorrect:
cmd.CommandText = "dbo.rpt_MO_Profile_Export"

try removing the period after dbo and replacing it with an underscore:
cmd.CommandText = "dbo_rpt_MO_Profile_Export"

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[JoeAtWork]

dbo" in SQL server means "Database Owner". You usually don't need to prefix your stored procedures with it because SQL Server assumes that "dbo" is the owner of a stored procedure if you don't specify it.

So just "rpt_MO_Profile_Export" will probably do the trick.

 

[belovedcej]

I know you didn't ask this - but I see something that concerns me. You have opened a connection and created a recordset but have closed neither. I had a bad experience with that - ended up with so many open connections by the end of the day that the system crashed!

So word to the wise -

before the End Sub put the following!

cnn.close
rst.close

Or alternatively,

Set cnn = nothing
Set rst = nothing

You may already know this - but I get panicky about it now.

 

[ajaeger]

I have the following code. It seems to be working as I am getting some of the updates that are written into the SP. However, after about 30 seconds, I'm getting a timeout error: Run-time error '-2147217871 (80040e31)': [Microsoft][ODBC SQL Server Driver] Timeout expired

Private Sub cmdRefreshTbl_Click()

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.Open "ODBC;DATABASE=IMISDB;UID=sa;PWD=123;DSN=iMIS MS SQL"

Set cmd.ActiveConnection = cnn

cmd.CommandText = "dbo.rpt_MO_Profile_Export"
cmd.CommandType = adCmdStoredProc

Set rst = cmd.Execute

cnn.Close
rst.Close

End Sub

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[PHV]

You may try this:
cmd.CommandType = adCmdStoredProc
[!]cmd.CommandTimeout = 0[/!]
Set rst = cmd.Execute

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ajaeger]

Thanks PHV. I was SLOWLY getting to that correct syntax. Here's what worked:

Private Sub cmdRefreshTbl_Click()

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.Open "ODBC;DATABASE=IMISDB;UID=sa;PWD=123;DSN=iMIS MS SQL"

Set cmd.ActiveConnection = cnn

cmd.CommandTimeout = 0
cmd.CommandText = "dbo.rpt_MO_Profile_Export"
cmd.CommandType = adCmdStoredProc

Set rst = cmd.Execute

cnn.Close

End Sub

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[abaldwin]

So taking out the line "Set cnn = CurrentProject.Connection" kept you connected to the SQL server and then it was a time out issue?


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.