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

Question

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?

score 3 · Answer 1 · answered Feb 08 '11 at 17:16

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
Next
''...

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
Next

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

1 Answers1