January 10, 2005 at 1:28 pm
I am trying to use the sp_OA extended procedures in a Stored Procedure to manipulate an MS Access 2000 database (".mdb") and/or project (".adp"). I do not have any problems connecting to the project or setting and getting properties from the database objects. However, I want to do some things using an ADODB "command" object. I can create the object without problems but CANNOT get it to connect with the "Connection" object. Here's some sample code:
DECLARE @cnnAccess int, @cmd int, @ConnectionString varchar(100)
SET
@ConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=c:\MyAccessDb.mdb;'
EXEC
@hr = sp_OACreate 'ADODB.Connection', @cnnAccess OUT
EXEC @hr = sp_OASetProperty @cnnAccess, 'ConnectionString', @ConnectionString
EXEC @hr = sp_OAMethod @cnnAccess, 'Open'
EXEC @hr = sp_OACreate 'ADODB.Command', @cmd OUT
EXEC @hr = sp_OASetProperty @cmd, 'ActiveConnection', @cnnAccess
All of these statements work except for the last one that tries to connect the "command" object to the "ActiveConnection". When this statement executes, it always returns this error:
Source: ADODB.Command
Description: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
It seems like the command.ActiveConnection property does not take the integer associated with the @cnnAccess connection. Needless-to-say, if I convert @cnnAccess to a "text" value, that doesn't work either but I get different errors. Does anymore have a answer/solution to this problem? Any help would be greatly appreciated. This is driving me "nuts". Thanks.
Denis
Denis W. Repke
January 10, 2005 at 2:07 pm
Instead of giving the @cnnAccess try giving @ConnectionString
EXEC @hr = sp_OASetProperty @cmd, 'ActiveConnection', @ConnectionString
Thanks,
Ganesh
January 10, 2005 at 2:36 pm
January 13, 2005 at 9:36 am
That did the trick. Thank you.
Denis W. Repke
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply