How to use MS Access SaveAsText with Queries (specifically Stored Procedures)

I have read every question on this site having to do with SaveAsText (and other version-control-with-Access related questions).

The most helpful one so far was this one. I am using a slightly modified version of the VBScript code posted in Oliver's answer to save my objects as text files. My application is an .adp, and the database (which obviously includes the Stored Procedures) is SQL Server 2005.

I have attempted to add this code to my VBScript, but I consistently get an error that says "You have cancelled the previous operation" when the SaveAsText line is hit.

For Each myObj In oApplication.CurrentData.AllStoredProcedures WScript.Echo " " & myObj.fullname oApplication.SaveAsText acQuery, _ myObj.fullname, _ sExportpath & "\" & myObj.fullname & ".sql" dctDelete.Add "RE" & myObj.fullname, acQuery Next

Based on the response in this question, I made sure to add

Const acQuery = 1

to the top of the file.

Also, this code

For i = 0 To oApplication.CurrentDatabase.QueryDefs.Count - 1 oApplication.SaveAsText acQuery, _ oApplication.CurrentDatabase.QueryDefs(i).Name, _ sExportpath & "\" & db.QueryDefs(i).Name & ".sql" Next i

did not work, but I believe that was intended for a .mdb anyway, not an .adp. Is there any solution for exporting StoredProcedures (and Views or Table definitions, while we're at it) to text files?

-------------Problems Reply------------

It probably doesn't have anything to do with mdb vs adp. When I try the following in an mdb, I get the same error message.

MsgBox Application.CurrentData.AllStoredProcedures.Count

I suspect that it is doing that because MS Access doesn't know the underlying structure of the SQL database is. And really, why should it care, and why would the DB tell your app what it looks like under the covers? The stored procedures run entirely on the DB side and knows nothing about the MS Access app, and the views are just that a view (or presentation of) the data. As far as I know, you can't manipulate either from within MS Access.

What I think you are trying to do is automate an export of the sql scripts necessary to recreate your database, and store them in some sort of version control system. The folks on Serverfault might have a convenient solution for that.

As for SaveAsText, unfortunately it is an undocumented method. I've used the following for exporting Queries

Dim db As Database
Set db = CurrentDb()

Dim Qry As QueryDef
For Each Qry In db.QueryDefs
strObjectName = Qry.Name

If Not (strObjectName Like "~sq_*") Then
Debug.Print " - " & strObjectName

Application.SaveAsText acQuery, strObjectName, _
MY_PATH & "queries_" & strObjectName & ".txt"
End If

TableDef's were a little different (couldn't get SaveAsText/LoadAsText to work properly)

For Each Tbl In db.TableDefs
If Tbl.Attributes = 0 Then 'Ignores System Tables
strObjectName = Tbl.Name

Debug.Print " - " & strObjectName

Application.ExportXML acExportTable, strObjectName, , _
MY_PATH & "tables_" & strObjectName & ".xsd"

End If

Category:ms access Views:2 Time:2011-02-01

Related post

  • Which is better: Ad hoc queries, or stored procedures? 2008-08-22

    Assuming you can't use Linq for whatever reason, is it a better practice to place your queries in stored procedures, or is it just as good a practice to execute ad hoc queries against the database (say, Sql Server for argument's sake)? --------------

  • Ad hoc queries vs stored procedures vs Dynamic SQL 2010-05-29

    Ad hoc queries vs stored procedures vs Dynamic SQL. Can anyone say pros and cons? --------------Solutions------------- Stored Procedures Pro: Good for short, simple queries (aka OLTP--i.e. add, update, delete, view records) Pro: Keeps database logic

  • Native Queries and Stored Procedures vs ORMs 2011-10-19

    As the popularity of ORMs like LINQ to SQL and Entity Framework increases, it makes me question using native queries and stored procedures. I am naturally geared towards SQL and the direct contact with the RDBMS. I enjoy the pure control over what ha

  • Parameterized queries WITHOUT stored procedures? 2009-07-27

    Every sample I've seen uses stored procedures. I've adopted an old application written in Classic ASP that uses inline SQL. This is an obvious issue, so I need to convert it to safer code. The client does not wish that I use stored procedures in this

  • Access external lib from oracle stored procedures 2011-09-08

    How do we refer to external libraries in oracle stored procedure? I did this. loadjava -jarasresource -user apps/[email protected] sqljdbc.jar (I am loading the lib required for jdbc connection to sql server 2000,) now, in my oracle stored procedure I wan

  • LINQ queries vs Stored procedures 2011-01-14

    What are the pros and cons of using linq queries(along with an ORM like EF or linq2sql) VS. Stored Procedures(SQL server 2008) to query and update a data model? Performance? Speed? Etc... --------------Solutions------------- Linq is definitely more r

  • Access second result set of stored procedure with SQL or other work-around? Python\pyodbc 2008-11-07

    I'm using python\pyodbc and would like to access the second result set of a stored procedure. As near as I can tell, pyodbc does not support multiple result sets. Additionally, I can't modify the stored procedure. Are there any options to access the

  • Access Web service from Oracle stored procedure 2008-11-18

    Is there anybody who has successfully accessed a Web service from an Oracle stored procedure? If so, was it a Java stored procedure? A PL/SQL stored procedure? Is there any reason why I should not be trying to access a WS from a stored proc? Here are

  • Which is faster for multiple SQL calls - parameterized queries or stored procedures? 2009-04-06

    I'm using SQL Server 2005. I'm looking at opening a SQL connection, looping though a collection and running an update query (stored procedure or parameterized query) with the data from the collection item, then closing the connection. Which is going

  • Accessing text fields in a stored procedure and insert to another table 2009-10-25

    I am trying to access a “text” type and inserting that value into another table viw a stored procedure. I’ve tried to cast it, convert it, but nothing works. My code looks somethings like this: Declare @Critique varchar(max), @Feedback varchar(max) …

  • Changing Database Names and Cross Database Queries In Stored Procedures 2010-05-26

    I have a number of related databases that are version-ed. Instance of different versions may run side by side, identified by their different versions, i.e. [NorhwindV1.1] and [NorhwindV1.2] may be on the same server, along with [SouthwindV1.1] and [S

  • Storing variables from queries in stored procedures 2010-06-30

    I have a stored procedure with a username parameter. I want to use one query to grab a userid, then use this variable in further queries. Here's what I have so far. It compiles OK, but on execution I get an error "Error converting data type varchar t

  • Multiple queries in stored procedure 2010-10-31

    Possible Duplicate: SQL CASE statement Hi Frdz, i have created a stored proc and i put three queries in stored proc with inputvariable @EtlLoadId = 0 and i get three different windows for each query under result tab. is there any chance i can get res

  • How to access a SQL Server 2008 stored procedure with a table valued parameter in Python 2011-04-20

    I’m looking for a way to take a result set and use it to find records in a table that resides in SQL Server 2008 – without spinning through the records one at a time. The result sets that will be used to find the records could number in the hundreds

  • Suggest data access design for Entity Framework (stored procedure less) 2012-02-11

    The plan is to use Entity Framework for data access. We are in a dilemma in deciding whether to use stored procedures or not. The main idea behind avoiding stored procedures: we don't want any one tempted in writing business logic at the database lev

  • concurrent access to MySQL database using stored procedure 2011-09-22

    I have a stored procedure that will read and then increment a value in the database. This particular procedure is used by many programs at the same time. I am concerned about the concurrency issues, in particular the reader-writer problem. Can anybod

  • Stored procedures or inline queries? 2008-10-31

    First of all there is a partial question regarding this, but it is not exactly what I'm asking, so, bear with me and go for it. My question is, after looking at what SubSonic does and the excellent videos from Rob Connery I need to ask: Shall we use

  • Hibernate for stored procedure access 2009-07-06

    We have business restriction in accessing the database only through stored procedure calls. Caching is also not allowed. Is there value in using Hibernate framework where in you are not using the features like building object relationship based compl

  • can make stored procedure or function in Access 2003? 2011-04-15

    In Access 2003, can we create stored procedure or function? --------------Solutions------------- For Access 2003, the answer is no. Access 2010 does have table triggers and store procedures. These are true engine level routines that run as a result o

Copyright (C), All Rights Reserved.

processed in 0.135 (s). 11 q(s)