[AccessD] Execute stored query using ADO

What were you setting the connection to before?  Are you saying that you
couldn't get the Cmd object code to work using CurrentProject.Connection
as the connection?

Charlotte Foust

-----Original Message-----
From: O'Connor, Patricia [mailto:Patricia.O'Connor at DFA.STATE.NY.US] 
Sent: Monday, March 22, 2004 12:27 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Execute stored query using ADO


Charlotte.


I had to switch the connection to the "current project" not use the
oracle connection

This worked
 
'***********************************************************************
**
         Set cnn = CurrentProject.Connection                '* SET to
Current Database
         cnn.Execute "q01aCreateNycFsTable", lngAffected,
adExecuteNoRecords



I was not able to get the other to work

Thanks
Patti

> -----Original Message-----
> From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
> Sent: Friday, March 19, 2004 05:05 PM
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] Execute stored query using ADO
> 
> 
> I've used that syntax to execute a saved query in Access before.  Can 
> you successfully execute the query using DAO?
> 
> Charlotte Foust
> 
> -----Original Message-----
> From: O'Connor, Patricia [mailto:Patricia.O'Connor at DFA.STATE.NY.US]
> Sent: Friday, March 19, 2004 1:46 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Execute stored query using ADO
> 
> 
> Hi all
> 
> I am attempting to set up a job stream in VBA. The first example works

> fine
> 
> 	            strSql = "INSERT INTO TBL_EXPFSDT (EXPFS_BASE_DT)"
> 	            strSql = strSql & " VALUES (TO_DATE('" & dtNew & "',

> 'MM/DD/YYYY'))"
> 	            cmd.CommandText = strSql
> 	            cmd.ActiveConnection = cnn
> 	            cmd.CommandType = adCmdText
> 	            cmd.Execute lngAffected, , adExecuteNoRecords
> 
> Then next step is to execute a stored passthru query named 
> q01aCreateNycFsTable the following does not work
> 	        Set cmd = New ADODB.Command
> 	        cmd.ActiveConnection = cnn
> 	        cmd.CommandText = "q01aCreateNycFsTable"
> 	        cmd.CommandType = adCmdStoredProc
> 	        cmd.Execute lngAffected, , adExecuteNoRecords
> 
> This is driving me nuts, I have tried several other
> combinations. I have
> other queries I wish to do the same thing but do not want to have to
> string out like in first example - TOO long.
> 
> How do I run an access query using ADO or do I have to use DAO.
> 
> Thanks
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com