Martin Reid
mwp.reid at qub.ac.uk
Thu Jan 22 12:58:08 CST 2009
John I missed most of this thread as I am working on something else Basically what you want is to Execute a stored procedure passing in the name of the procedure to execute and other associated parameters which will be basically table names? You will also need to change the database name in the connection string using a parameter? That about it? I haven't seen the Function you are referring to. Martin Pass in the tablenames Martin WP Reid Information Services Queen's University Riddel Hall 185 Stranmillis Road Belfast BT9 5EE Tel : 02890974465 Email : mwp.reid at qub.ac.uk ________________________________________ From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby [jwcolby at colbyconsulting.com] Sent: 22 January 2009 18:41 To: Access Developers discussion and problem solving Subject: Re: [AccessD] Stored procedures from Access Martin, Thanks for the links. In fact I found the first, and it will no doubt be useful to me someday however... I have pretty complex (to me at least) set of stored procedures created out in SQL Server. For example I have a stored procedure that does a BCP out to export data from a table for processing by a third party called Accuzip (address validation). The data in the table is name / address information that has to be exported to files in a specific directory, on a specific server (actually a virtual machine here in my office). The virtual machine is running Accuzip, which is is watching that folder. As I place files into the watched directory, Accuzip starts processing them, and placing processed files into an "output" directory. I then have to get the files back into SQL Server. I have a matching set of stored procedures that BCP the data back in to a table in SQL Server. So what I am trying to accomplish is simply tap SQL Server on the shoulder and say "hey, execute SPXXX in database YYYY, with these parameters". This is not about getting a result set to bind to a form or control, this is about telling SQL Server to do some specific stored procedure, to perform some process. Once Accuzip processes the input files and creates the output files, I then need to tap SQL Server on the shoulder and say "hey, execute this Stored Procedure in database YYY with these parameters. The end results will be to import the files back into SQL Server. I have all of the stored procedures created, and have used them for quite some time, however it still takes significant manual labor to manually tap SQL Server on the shoulder. I want to tie the pieces together from Access. This is a very well defined process, but the database name, stored procedure names and table names can change from run to run. I kind of envisioned something like Charlotte's function that would do exactly this... tap SQL on the shoulder and tell it to do something, passing in parameters as required. Unless it is somehow required in order to accomplish this task, I have no need for pass through queries in Access, no need for linked tables to SQL Server, no need for loading data into forms or combos etc. I might someday, but not for this specific task. John W. Colby www.ColbyConsulting.com Martin Reid wrote: > John > > A bit dated but will get you up and running and I rate this guy highly. > > http://www.databasejournal.com/features/msaccess/article.php/3407531/How-to-Execute-SQL-Stored-Procedures-from-Microsoft-Access.htm > > For information on the strings to use > > www.connectionstrings.com > > On the connection > > http://support.microsoft.com/kb/281784 > > > > > Martin > > > > Martin WP Reid > Information Services > Queen's University > Riddel Hall > 185 Stranmillis Road > Belfast > BT9 5EE > Tel : 02890974465 > Email : mwp.reid at qub.ac.uk > ________________________________________ > From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby [jwcolby at colbyconsulting.com] > Sent: 22 January 2009 17:59 > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Stored procedures from Access > > And how many many many times have I written a step by step, > click this, do this kind of instruction when requested? > > John W. Colby > www.ColbyConsulting.com > > > William Hindman wrote: >> "they all seem to start with "you know how to do this and here are some >> tricks" rather than "since you know nothing, >> here is what you are doing and why" >> >> ...lol ...how many, many, many times, over a long span of years, have I >> pushed your buttons for doing the very same thing to we poor AccessD >> illiterates ...you know, you assume we know, but we don't know :) >> >> William >> >> -------------------------------------------------- >> From: "jwcolby" <jwcolby at colbyconsulting.com> >> Sent: Thursday, January 22, 2009 11:59 AM >> To: "Access Developers discussion and problem solving" >> <accessd at databaseadvisors.com> >> Subject: [AccessD] Stored procedures from Access >> >>> OK, I have built my first pass through query, which runs >>> just fine. >>> >>> Basically I am trying to run stored procedures in SQL which >>> have parameters. >>> >>> I built the pass through query as a simple >>> >>> "exec MyProcName, Param1, Param2" >>> >>> Where param1 and param2 are hard coded. I do not see a way >>> to place parameters in a collection like you can do with >>> regular queries. Did I miss something there? >>> >>> Now, I think it was Charlotte (I deleted the email after >>> copying the code) that provided a CallADOStoredProc, which I >>> am trying to use. However I am getting an "odbc call >>> failed" error, which I am pretty certain is because it uses >>> the standard currentproject.connection. That connection >>> does not reference the server or the database, so how can it >>> know where to send the odbc call? >>> >>> Questions: >>> >>> 1) Is the parameter collection that is being filled in >>> CallADOStoredProc replace the hard coded parameters in my >>> pass through query? I.e. can I just remove the hard coded >>> parameters and place them in the call to CallADOStoredProc? >>> >>> 2) Is CallADOStoredProc looking for a local pass through >>> query at all, or is it looking for the name of a stopred >>> procedure out in SQL Server? >>> >>> 3) Do I need to find a "standard" connection string which >>> will reference the SQL Server? >>> >>> I am now using tons of Stored Procedures out in SQL Server >>> but I have absolutely zero experience causing them to >>> execute from Access. I need some very basic instructions on >>> how to do this. I have found things on the internet but >>> they all seem to start with "you know how to do this and >>> here are some tricks" rather than "since you know nothing, >>> here is what you are doing and why". >>> >>> Can anyone here provide a "since you know nothing" approach? >>> >>> TIA, >>> >>> -- >>> John W. Colby >>> www.ColbyConsulting.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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com