using double quotes with variables

[Ron M]

Hi all

for an ODBC stored procedure I am trying to execute from
Access 2003, i have this line of code

Set Rs1 = Conn1.Execute("exec dbo.Purge_tempTable", 1,
adCmdText)

When I realized I had to execute several procedures, it
made sense to just create the whole code a function
called 'execProc(myProc as string)' and pass the
procedure names (in the example above =
dbo.Purge_tempTable) as myProc. The problem is my code
ends up with a line like

Set Rs1 = Conn1.Execute('exec dbo.Purge_tempTable', 1,
adCmdText)

OR

Set Rs1 = Conn1.Execute(exec dbo.Purge_tempTable, 1,
adCmdText)

which obviously will both give me syntax errors.

How do I then make sure that the double quotes are
included in the variable and force the Jet engine to
believe they are double quotes?

Thanks for anyones help in advance.

cheers
Ron M

 

[Dirk Goldgar]

Hi all

for an ODBC stored procedure I am trying to execute from
Access 2003, i have this line of code

Set Rs1 = Conn1.Execute("exec dbo.Purge_tempTable", 1,
adCmdText)

When I realized I had to execute several procedures, it
made sense to just create the whole code a function
called 'execProc(myProc as string)' and pass the
procedure names (in the example above =
dbo.Purge_tempTable) as myProc. The problem is my code
ends up with a line like

Set Rs1 = Conn1.Execute('exec dbo.Purge_tempTable', 1,
adCmdText)

OR

Set Rs1 = Conn1.Execute(exec dbo.Purge_tempTable, 1,
adCmdText)

which obviously will both give me syntax errors.

How do I then make sure that the double quotes are
included in the variable and force the Jet engine to
believe they are double quotes?

Thanks for anyones help in advance.

cheers
Ron M

How about:

Set Rs1 = Conn1.Execute("exec " & MyProc, 1, adCmdText)

(passing "dbo.Purge_tempTable" in the argument MyProc), or even:

Set Rs1 = Conn1.Execute("exec dbo." & MyProc, 1, adCmdText)

(passing only "Purge_tempTable" in MyProc)?

 

[Ron M]

Thanks Dirk

but it is being evaluated as

Set Rs1 = Conn1.Execute(exec dbo.Purge_tempTable, 1,
adCmdText)

in both cases, I actually tried this before posting the
question but had to try it again thinking I may have
typed them in wrong.

I also tried the suggestion on the help file to use
chr$(34) by using a string, strQuote to no avail. It just
comes back as a single quote.

any more ideas please?

Ron M

 

[Dirk Goldgar]

Thanks Dirk

but it is being evaluated as

Set Rs1 = Conn1.Execute(exec dbo.Purge_tempTable, 1,
adCmdText)

in both cases, I actually tried this before posting the
question but had to try it again thinking I may have
typed them in wrong.

I also tried the suggestion on the help file to use
chr$(34) by using a string, strQuote to no avail. It just
comes back as a single quote.

any more ideas please?

That doesn't make sense to me. What is the actual code for this
function of yours? It can't be what I thought, if you're getting the
sort of result you describe.

 

[Ron M]

Hi Again Dirk

here is the function

Private Function execStoredProc(myProc As String)
Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Rs1 As ADODB.Recordset
Dim Connect As String
Dim strQuote As String
strQuote = Chr$(34)

sConnect = "driver={sql server}; server=xxx.xx.x.xxx;
Database=mydb; UID=userid; PWD=pwd;"

MsgBox "strQuote is " & strQuote
Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = sConnect
Conn1.Open sConnect
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
MsgBox "about to execute procedure"
Set Rs1 = Conn1.Execute("exec " & myProc, 1, adCmdText)
Set Rs1 = Nothing

Rs1.Close
End Function

what do you think?

Ron

 

[Dirk Goldgar]

Offline for the night. Will look for your response in the morning.

 

[Douglas J. Steele]

SInce you're just "throwing away" the recordset that you instantiate, why
bother with it?

Private Function execStoredProc(myProc As String)
Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command

Dim Connect As String

sConnect = "driver={sql server}; server=xxx.xx.x.xxx;
Database=mydb; UID=userid; PWD=pwd;"

Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = sConnect
Conn1.Open sConnect
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "Exec " & myProc
Cmd1.Execute

End Function

FWIW, you're setting the recordset to Nothing and then attempting to Close
it. Once you've set it to nothing, there's nothing to close: reverse the
order of those commands.

 

[Dirk Goldgar]

Hi Again Dirk

here is the function

Private Function execStoredProc(myProc As String)
Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Rs1 As ADODB.Recordset
Dim Connect As String
Dim strQuote As String
strQuote = Chr$(34)

sConnect = "driver={sql server}; server=xxx.xx.x.xxx;
Database=mydb; UID=userid; PWD=pwd;"

MsgBox "strQuote is " & strQuote
Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = sConnect
Conn1.Open sConnect
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
MsgBox "about to execute procedure"
Set Rs1 = Conn1.Execute("exec " & myProc, 1, adCmdText)
Set Rs1 = Nothing

Rs1.Close
End Function

what do you think?

Ron

I agree with Doug Steele's comment that you have no need to create a
recordset in this case. However, that doesn't explain why you're not
getting the result you report, at least not to me. But I'm puzzled.
If, as you say, this works:

Set Rs1 = Conn1.Execute("exec dbo.Purge_tempTable", 1, adCmdText)

Then I can think of no reason why this would not work:

Dim MyProc As String

MyProc = "dbo.Purge_tempTable"
Set Rs1 = Conn1.Execute("exec " & MyProc, 1, adCmdText)

And therefore, this ought to work:

Function ExecProc(MyProc As String)

' ... preliminary code ...

Set Rs1 = Conn1.Execute("exec " & MyProc, 1, adCmdText)

' ... rest of code ...

End Function

when called as

ExecProc "dbo.Purge_tempTable"

or

Call ExecProc("dbo.Purge_tempTable")

There's no difference in the arguments that get passed to Conn1.Execute.
What is the exact error message you're getting? Have you stepped
through the code to verify that the proc name is being passed correctly?

 

[Ron M]

Hi Dirk/Doug

Thanks you guys for the help, I am indeed puzzled how the
double quote issue is not being resolved, stepping
through did not help as it came up with the same problem.
The message I get with

Set Rs1 = Conn1.Execute("exec " & myProc, 1, adCmdText)

is "Object variable or With block variable not set"

I swear

Set Rs1 = Conn1.Execute("exec dbo.delTemp", 1, adCmdText)

works fine.

Anyhow, I tried Doug's suggestion and it works fine.

thanks again guys

Ron M

 

[Dirk Goldgar]

Hi Dirk/Doug

Thanks you guys for the help, I am indeed puzzled how the
double quote issue is not being resolved, stepping
through did not help as it came up with the same problem.
The message I get with

Set Rs1 = Conn1.Execute("exec " & myProc, 1, adCmdText)

is "Object variable or With block variable not set"

I swear

Set Rs1 = Conn1.Execute("exec dbo.delTemp", 1, adCmdText)

works fine.

Anyhow, I tried Doug's suggestion and it works fine.

Hm. I'd expect you to get that error message on the Set Rs1 line if
Conn1 hadn't been instantiated or opened. I'd expect you to get it on
some subsequent line if Rs1 wasn't successfully set. Without carefully
tracing it, I don't know why it wasn't working right. But since you've
got it working following Doug's advice, we can forget about it.

 

[Ron M]

Hi Doug,

thanks for that, got it working. I actually replied to
Dirk earlier from his reply and thanked you there but
just thought I'd sned this one to make sure you get my
thanks, so thanks again...

cheers

Ron M