Application.SaveAsText Remotely?

Good Day All,
I am toying with a remote database that I point at my primary database, choose what objects I want to save as text files, then push GO. These I will compare using a 3rd party utility.
Using the SaveAsText method, what is supposed to happen is I open the Primary database in code and create myself a nice directory full of text files.
Once all the files are created, I release the Primary database and all is well.
The problem is, Application.SaveAsText doesn't seem to want to play that way.

Is the issue that because it is an Application function, I actually have to be in the application?

Any input would be great. Here's a snippet.

    Set DBApp = DBEngine.OpenDatabase(sPath, False, False, "MS Access")
    Set cnt = DBApp.Containers("Forms")
    Debug.Print "Documenting Forms:"
    For Each doc In cnt.Documents
        Debug.Print "  " & doc.Name
        Application.SaveAsText acForm, doc.Name, fPath & doc.Name & ".txt"
        DoEvents
    Next doc

It fails on the Application.SaveText line telling me that I've cancelled the previous operation.
J
LVL 34
jefftwilleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK ... so, I'm in a new blank 'local' mdb and I run this code from the Immediate window:

Public Function mTest() As Boolean
   
   Dim DBApp As DAO.Database
   Dim cnt As Container
   Dim doc As Document
   Set DBApp = DBEngine.OpenDatabase("C:\Documents and Settings\Administrator\Desktop\EE\aaEE2.mdb")
    Set cnt = DBApp.Containers("Forms")
    Debug.Print "Documenting Forms:"
    For Each doc In cnt.Documents
        Debug.Print "  " & doc.Name
        ''Application.SaveAsText acForm, doc.Name, fPath & doc.Name & ".txt"
        DoEvents
    Next doc

End Function

I get a list of Forms aaEE2.mdb.

What exactly do you want to happen with the SaveAsText ?

mx
0
jefftwilleyAuthor Commented:
Joe,
Well, uncomment the Application.SaveAsText line, Provide a File Path in the fPath variable, and give it another go.
Any/All forms in the database that you've opened should show up in that fPath directory as text files.
J
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Gee ... I didn't even look at the poster ... or I would have blown it off (ha ha ha) (kidding).


So I should see a bunch of text files in a directory I specify ?

mx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jefftwilleyAuthor Commented:
Nice!

No, actually with the code given, you'll see an error that I'm trying to get around.
Did you even read the question? lol

J
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... I see the error ... wasn't sure where the files were going ... standby:

mx
0
jefftwilleyAuthor Commented:
Standing by...when do the real experts get here?
<G>
J
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
All the other Experts are busy with more important questions :-)

OK ... I say it's because the actual 'objects' (are in the external mdb) ... so, they are not to be found in the local mdb.  I'm going to try something else ....

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Have you tried setting a Reference to the External mdb.  In that case, you can see the Class object of the forms (assuming they have modules)  ?  Or are you trying to avoid that ?

mx
0
Rey Obrero (Capricorn1)Commented:
jeff,

try this, change form1 with actual name of a form in the the other db..




dim acObj as object
set acObj-createobject("access.application")
     acObj.opencurrentdatabase(sPath)
     acobj.application.saveastext acForm, "form1", fPath &  "form1.txt"

     acObj.closecurrentdatabase
     set acObj = nothing



cheers ... i am on a drinking party.. ;-)


0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... this works:

Public Function mTest() As Boolean
   
   Dim DBApp As DAO.Database
   Dim cnt As Container
   Dim doc As Document
   Dim CDB As DAO.Database
   
   Set DBApp = DBEngine.OpenDatabase("C:\Documents and Settings\Administrator\Desktop\EE\aaEE2.mdb")
    Set cnt = DBApp.Containers("Forms")
    Debug.Print "Documenting Forms:"
   
    For Each doc In cnt.Documents
        Debug.Print "  " & doc.Name
        DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Documents and Settings\Administrator\Desktop\EE\aaEE2.mdb", acForm, doc.Name, doc.Name
        Application.SaveAsText acForm, doc.Name, "C:\Documents and Settings\Administrator\Desktop\EE\JtTest\" & doc.Name & ".txt"
        ' now delete the local copy here ...
        DoEvents
    Next doc

End Function

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Ignore the Dim CDB As DAO.Database line of code ...

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here is the complete gig:

Public Function mTest() As Boolean
   
   Dim DBApp As DAO.Database
   Dim cnt As Container
   Dim doc As Document
   
   Set DBApp = DBEngine.OpenDatabase("C:\Documents and Settings\Administrator\Desktop\EE\aaEE2.mdb")
    Set cnt = DBApp.Containers("Forms")
    Debug.Print "Documenting Forms:"
   
    For Each doc In cnt.Documents
        Debug.Print "  " & doc.Name
         
        DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Documents and Settings\Administrator\Desktop\EE\aaEE2.mdb", acForm, doc.Name, doc.Name
        Application.SaveAsText acForm, doc.Name, "C:\Documents and Settings\Administrator\Desktop\EE\JtTest\" & doc.Name & ".txt"
        ' now delete the local copy here ...
        DoCmd.DeleteObject acForm, doc.Name
        DoEvents
    Next doc

End Function

mx
0
jefftwilleyAuthor Commented:
Cap's method does work, but for one form only. I am going to figure out how to loop through the container objects now.

Joe, I'd rather not import the form into my tool before outputting to text.

J
0
datAdrenalineCommented:
The key is that .SaveAsText is an APPLICATION method, that is why caps method works, he creates an instance of Access and manipulates that instance.  Once you have that instance, you can use all the methods that are part of the Application object.

Something like this should give you a good base:

Dim obj As AccessObject
With New Access.Application
    .OpenCurrentDatabase sPath
    For Each obj In .CurrentProject.AllForms
        .Application.SaveAsText acForm, obj.Name, fPath & obj.Name & ".frm"
    Next obj
    .CloseCurrentDatabase
End With

Notice the dots to give method hierarchy back to the New Access Application.  Also, it is important that any startup code in the database you open with .OpenCurrentDatabase WILL be executed.

0
jefftwilleyAuthor Commented:
Brent,
That works things out nicely. It also allows me to use the currentProject object to access the other object types in the database.

I will have to come up with a way to stifle a particular event on the close of my application because it hinders it closing properly, but I'll work that out.

Thanks as always to Joe and Cap and to you Brent for throwing in the final touch.

I'll finish up my coding and paste in the full final solution here for others to use if they need to.

Enjoy the weekend!
J
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Also, Intellisense works with your approach, but not with the

dim acObj as object
set acObj=createobject("access.application")

approach
0
datAdrenalineCommented:
Sorry ... while the code I posted works, you really don't need the extra .Application in the call to .SaveAsText ....

Dim obj As AccessObject
With New Access.Application
    .OpenCurrentDatabase sPath
    For Each obj In .CurrentProject.AllForms
        .SaveAsText acForm, obj.Name, fPath & obj.Name & ".frm"
    Next obj
    .CloseCurrentDatabase
End With

Also ... if you wish to use the Containers collection, you can:

Dim doc As DAO.Document
Dim db As DAO.Database
With New Access.Application
    .OpenCurrentDatabase sPath
    Set db = .CurrentDb
    For Each doc In db.Containers("Forms").Documents
        .SaveAsText acForm, doc.Name, fPath & doc.Name & ".frm"
    Next doc
    .CloseCurrentDatabase
End With


If you get any errors, be sure that the db you open with .OpenCurrentDatabase does not have any object modifications that force a read only state when opened in a shared instance.

Hope that helps!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
datAdrenalineCommented:
I was composing whilst you all were posting! ... So ... Your Welcome! ... Always glad to be of assistance!
0
datAdrenalineCommented:
Jeff ...

>> I will have to come up with a way to stifle a particular event on the close of my application because it hinders it closing properly, but I'll work that out. <<

You will want to check out the UserControl property of the Applicaition object, if you start Access manually (ie: a dbl-click on an icon) UserControl will return a True.  If an Access instance was a spawned instance (like we are doing here) UserControl will return a False.

So, in your Close event, you can examine the UserControl property and execute the code if UserControl returns True, and NOT execute the code when UserControl is false.

Private Sub Form_Close()
    If UserControl = True Then 'Means the USER launched the instance of Access.
        'Do your code
    End If
End Sub
0
jefftwilleyAuthor Commented:
All,
Let me start by saying thanks to everyone. I always learn something new when I come out here and actually ask a question..

I will tell you that I've learned a lot just about the process of what I'm trying to do. I wanted to create a set of text files that I can load into VSS in order to track Versions of my app.

So I did what you would do, I googled, and I came here and finally pieced together a pretty nice app.

My final use for the above code ended up looking like the attachment.

I run this wonderful query in order to get my object list

    sFilter = CreateFilter
   
    Set qdf = CurrentDb.QueryDefs(sQuery)
    sSQL = "SELECT MSysObjects.Name AS [ObjectName], Switch([Type]=5,'Queries',[Type]=-32768,'Forms',"
    sSQL = sSQL & " [Type]=-32764,'Reports',[Type]=-32766,'Macros',[Type]=-32761,'Modules') AS [ObjectType], "
    sSQL = sSQL & " Switch([Type]=5,1,[Type]=-32768,2,[Type]=-32764,3,[Type]=-32766,4,[Type]=-32761,5) AS ObjType"
    sSQL = sSQL & " FROM MSysObjects IN '" & sPath & "'"
    sSQL = sSQL & " WHERE Left$([Name],1)<>'~' AND Left$([Name],1)<> CHR(34) " & sFilter
    sSQL = sSQL & " ORDER BY Switch([Type]=5,1,[Type]=-32768,2,[Type]=-32764,3,[Type]=-32766,4,[Type]=-32761,5), "
    sSQL = sSQL & " MSysObjects.Name"
    qdf.SQL = sSQL
    qdf.Close
    Set qdf = Nothing

Notice there's a sPath, and also sFilter so that it becomes portable.

The Object definitions are here:
    '1";"Queries";"2";"Forms";"3";"Reports";"4";"Macros";"5";"Modules"
    'Switch([Type]=5,1,[Type]=-32768,2,[Type]=-32764,3,[Type]=-32766,4,[Type]=-32761,5
I'm splitting the points for everyone taking time.

Special thanks to Brent.

J
With New Access.Application
    .OpenCurrentDatabase sDBPath, False
    Set db = .CurrentDb
    .Visible = False

    Set rs = db.OpenRecordset("Select MyVersionFunction() as Version from MyConfigTable", dbOpenSnapshot, dbSeeChanges)
    If lDBType = 1 Then
        MasterVersion = rs!Version
    Else
        DevVersion = rs!Version
    End If
    rs.Close
    Set rs = Nothing

    'I open the sTable and use that as the list of objects to save as text files.
    sSQL = "SELECT " & sTable & ".Object"
    sSQL = sSQL & " FROM " & sTable & " "
    sSQL = sSQL & " ORDER BY " & sTable & ".Object"
    
    Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot, dbSeeChanges)
    If rs.EOF Then Exit Function
    rs.MoveFirst
    Do Until rs.EOF
        lObject = GetObjectType(Left(rs!Object, 3))
        sOutputName = sFilePath & rs!Object & ".txt"
        sAccessName = Right(rs!Object, Len(rs!Object) - 4)
        If lObject <> 255 Then
            UpdateMeter j, i
            .SaveAsText lObject, sAccessName, sOutputName
            DoEvents
            i = i + 1
        End If
        
    rs.MoveNext
    Loop
    
    Set db = Nothing
    '.Visible = True
    .CloseCurrentDatabase
End With

Open in new window

0
jefftwilleyAuthor Commented:
Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.