Problem Executing Stored procdure in VBA

[Dan_T]

Hi,

I am having my first real stab at calling stored procedures from Access and I am having some difficulty getting past what seems like a silly hurdle.

I have built a very simple stored procedure to return a single value from a table. I have tested the sproc in the query analyser and can return results no problem.

I have put together the following code for testing purpose to call the procedure and although I can compile it I get the error:

"ActiveX component can't create object" with the third line of code [cm.CommandText = "GetContactDisplayName"] highlighted

Is it a reference setting or an MDAC issue or what?

Any ideas would be greatly appreciated.

Many thanks

Dan T

Dim cm As New ADODB.Command
Dim pm As New ADODB.Parameter

cm.CommandText = "GetContactDisplayName"
cm.CommandType = adCmdStoredProc

Set pm = cm.CreateParameter("CL_ID", adInteger, adParamInput, , intCL_ID)
cm.Parameters.Append pm
Set pm = cm.CreateParameter("CL_DisplayName", adBSTR, adParamOutput)
cm.Parameters.Append pm

cm.ActiveConnection = "Connection string removed"
cm.Execute

Print cm.Parameters("CL_DisplayName")

 

[tehNellie]

You appear to be missing the "createobject" command and the following format might be easier to follow

Dim cm As New ADODB.Command

[b]Set cm = CreateObject("ADODB.Command")[/b]
with cm
  .CommandText = "GetContactDisplayName"
  .CommandType = adCmdStoredProc
  .Parameters.Append .CreateParameter("CL_ID", adInteger, adParamInput, , intCL_ID)
  .Parameters.Append .CreateParameter("CL_DisplayName", adBSTR, adParamOutput)
  .ActiveConnection = "Connection string removed"
  .Execute
end with

If you're calling a SQL server stored procedure I think you'll need to include the '@' on the variable name.

 

[modest]

I don't believe he needs to create an object since he used the New keyword. Been a while since i've used ADO though. I'm also surprised "Print xxxx" worked for you. I receive errors when executing it and instead do a Debug.Print

 

[tehNellie]

hmmm you need the "set" command but looking at it, I get a different error (object variable not set) if I omit the "set".

A quick google does suggest it's possibly a reference issue:
http://support.microsoft.com/kb/319844

 

[Dan_T]

Thanks for both replies.

Modest, although the code compiled execution doesn't get beyond the first line.

tehNellie, As Modest says I don't think I need it as I am using the New keyword. However I did try the code example you posted and the same problem arrises. Not progressing further than the first line. In this case

[Set cm = CreateObject("ADODB.Command")]

Many thanks

Dan

 

[tehNellie]

maybe I'm just a tit today

 

[Dan_T]

haha not at all. I appreciate the replies.

 

[tehNellie]

My bad, I got fixated on a red herring due to my own carelessness (doh!) about reading the error messages/old vba habits.

No, you dont need to "set" the command if you've declared it as "new" (to me as much as anyone else).

To the problem at hand, I couldn't replicate it in code and I should have stuck to my first suggestion which is that it might well be a reference issue, but that's based on a simple google search. I would be interested in hearing how you got on.

Sorry if I confused matters.

 

[Dan_T]

tehNellie I am picking up with this again this morning. I will post back how it progresses.

 

[Dan_T]

Right i think I need some help understanding my MDAC installation. I have downloaded and run the MDAC component checker and the results don't seem to be perfect. It looks like I have some files namely

MSADCO.DLL
msado15.dll
MSADIMD.DLL
MSADOX.DLL
MSJRO.DLL
msxml3.dll

shown in the results colum titled "Mismatch fields" is the message "Expected: Version=2.81.1117.0;"

I have these files installed but the versions are different from those "Expected"

So is this a problem and if so how do I go about rectifying it?

many thanks,

Dan. Off to search the forum some more.

 

[Dan_T]

In addition further looking at the results from the MDAC component checker I have 20 registry settings not found and 8 Com objects not found...

I have searched around this forum and google and not found an answer as to whether this is an issue or what to do if it is. well none that I understand anyway.

Some inspiration where to go from here needed please.

Cheers,

Dan.

 

[Dan_T]

Ok its all sorted. The problem was with my MDAC. Took half the day to resolve but I found a useful site that offers some help on the subject. By following the instructions there I rectified the problems with MDAC I had outlined before.

For future reference the site that helped is.

http://www.macropool.com/en/download/repair_mdac.html

and in my case the instructions that helped me (XP SP2) were from this page

http://www.macropool.com/en/download/mdac_xp_sp2.html

So thanks again guys for your suggestions.

Dan T