Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A Backup of sorts

    Does anyone have/know of code already written to do this kind of mock backup:

    * Export all tables as delimited text files
    * Save each query's SQL statement as a text file (not the results but the "questions" themselves.)
    * Save each form's properties as a text file
    * Save any form code as text files
    * Save each report's properties as a text file
    * Save each macro's Properties as a text file
    * Save any modules as text files

    Thank you kindly in advance

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A Backup of sorts

    It sounds like you're trying to create a more powerful version of the Documenter included in Access. If you run that, you can use the file menu to export the reports to a file format like doc or rtf, if that's what you're looking for. If you go to <A target="_blank" HREF=http://www.fmsinc.com>http://www.fmsinc.com</A> and take a look at the tools they offer, you'll find that Total Access Analyzer provides additional functionality. If you're looking for the equivalent of a SQL Server script to recreate a database, maybe someone else knows of one.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2001
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Backup of sorts

    Thank you Charlotte. I'm looking for already written code that I can use to do what I mentioned above and modify it to suit my needs. Hopefully something I don't have to purchase. I'm looking to store most everything in ASCII text files, definitely not Word format(doc) or Rich Text(rtf.) I'm not simply looking for reports or a "documentor", I'm looking to backup my databases in pieces in text file format. I'll probably end up writing my own code, but was hoping someone on the lounge had already done this.

    Thank you.

  4. #4
    coreman
    Guest

    Re: A Backup of sorts

    Hmm... saving your table data to ASCII isn't too hard.

    public sub SaveTableToASCII
    Dim db as database, t as tabledef
    const HasFieldNames as boolean = -1

    set db = currentdb
    for each t in db.tabledefs
    'don't dump the system tables!
    if left(t.name,4) <> "MSys" then
    DoCmd.TransferText acExportDelim,,t.name,"C:" & t.name & ".txt
    end if
    next t
    end sub

    The second is also pretty easy:
    Public sub DumpQuerySQL
    dim db as database, q as querydef
    Dim hndFile as integer
    set db = currentdb

    for each q in db.querydefs
    hndFile = FreeFile
    open "C:" & q.name & ".sql" for output access write as hndFile
    print #hndFile q.sql
    close hndFile
    next q


    It is on one hand equally simple, yet more difficult, to dump your database schema to a text file that you use some other procedure to get the SQL pieces from that file.
    It's all a matter of stepping through the TableDefs,Indexes, Fields, and Querydefs collections and the Relationship object. But part of it is getting the order of how things are applied right, etc. The other part is converting the enumerated values, say, for field types, to the right string value to use within AccessSQL (best to write a function to do it).

    Some times it's better to buy a tool that can do it for you instead (FMC Software's Total Access apps come to mind).

  5. #5
    Lounger
    Join Date
    Aug 2001
    Location
    Lanham, Maryland
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Backup of sorts

    Until recently, I was using Access 97 and used the code below to output module text to a text file by clicking on a custom toolbar button which called this code. Now that I have been upgraded to Access 2000, I find that I can no longer use this approach. My custom toolbar no longer displays when I am viewing my code in the VB editor.

    I would like to continue to use this approach because it used the database filename as part of the output filename and I reuse code over several different implementations.

    Does anyone have a suggestion on how I adapt this to Access 2000?

    Thanks,

    Dick

    '------------------------------------------------------------
    ' Created by Dick White on 9/12/2001
    ' Purpose: output active screen object to a TXT file and view in editor
    '------------------------------------------------------------
    Function fSendObjToTXT()
    Dim strFileName As String
    Dim iObjType As String
    Dim strObjName As String
    Dim intObjType As Integer
    Dim intObjState As Integer
    Dim strDate As String

    Call fGetScrnObjInfo

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A Backup of sorts

    It should work as is except fpr correcting the format argument . This works in 2k on my machine:

    DoCmd.OutputTo acOutputModule , "basGeneric", acformattext, "C:basGeneric.txt"
    Charlotte

  7. #7
    Lounger
    Join Date
    Aug 2001
    Location
    Lanham, Maryland
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Backup of sorts

    Charlotte,

    Thanks for your reply. I tried your suggestion, but received the following error: Compile error: Variable not defined. The word acformattext was highlighted. I looked into Microsoft Visual Basic Help for OutputTo and found the following:

    outputformat One of the following intrinsic constants:
    acFormatASP
    acFormatDAP
    acFormatHTML
    acFormatIIS
    acFormatRTF
    acFormatSNP
    acFormatTXT
    acFormatXLS

    I have been getting the following error when I attempt to run the code from the VB Macro drop down list:
    Run-time error '2487': The Object Type argument for the action or method is blank or invalid.

    Dick

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A Backup of sorts

    Access 2000 uses acFormatTxt ... at least on my machine. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I typed it wrong!
    Charlotte

  9. #9
    Lounger
    Join Date
    Aug 2001
    Location
    Lanham, Maryland
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Backup of sorts

    Yes. That is what my statement is too. I just wish I knew why I was getting this error message when I run the code: Run-time error '2487': The Object Type argument for the action or method is blank or invalid. As you can see, I specified objecttype:=acOutputModule, so the object type is not blank, and per the help file, acOutputModule is a valid type. I was unable to find anything in the Microsoft Knowledgebase either. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: A Backup of sorts

    I don't know if this is any help, here is sub I use to export all standard modules to text files in same folder where database is located. If file already exists it is overwritten:
    <pre>Public Sub ExportModules()

    Dim strDbName As String
    Dim strDbPath As String
    Dim strModName As String
    Dim strFilename As String
    Dim db As DAO.Database
    Dim doc As DAO.Document
    Dim n As Integer
    Dim intCount As Integer

    Set db = CurrentDb

    'Assumes database filename = *.mdb
    strDbPath = Application.CurrentProject.path & ""
    strDbName = Application.CurrentProject.Name
    strDbName = Left(strDbName, Len(strDbName) - 4)
    intCount = db.Containers("Modules").Documents.Count

    For n = 0 To intCount - 1
    Set doc = db.Containers("Modules").Documents(n)
    strModName = doc.Name
    strFilename = strDbName & "_" & strModName & ".txt"
    DoCmd.OutputTo acOutputModule, strModName, acFormatTXT, strDbPath & strFilename
    Next n

    Set db = Nothing
    Set doc = Nothing

    End Sub</pre>

    As for error, problem is you're omitting ObjectName argument (I know, it's optional) but apparently is necessary (may depend on where you are calling code from, supposedly if argument left blank the active object will be output). When I tried to run code in module directly w/o object name got same error. This example will work:
    <pre>DoCmd.OutputTo ObjectType:=acOutputModule, ObjectName:="ModuleName", _
    OutputFormat:=acFormatTXT, OutputFile:="C:ACCESSMODULENAME.TXT", _
    AutoStart:=True</pre>


    HTH

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Backup Code Modules

    In further reply, here is some code that can be used to export a FORM's code module to a text file (OutputTo only works with standard code modules):
    <pre>Public Function ExportFormModuleText(strForm As String)

    Dim mdl As Access.Module
    Dim strPath As String
    Dim strDb As String
    Dim strFileName As String
    Dim n As Integer

    strPath = Access.Application.CurrentProject.Path & ""
    strDb = Access.Application.CurrentDb.NAME

    DoCmd.OpenForm strForm

    Set mdl = Forms(strForm).Module
    n = mdl.CountOfLines
    strFileName = strPath & strForm & "_Module.txt"

    Open strFileName For Output As #1
    'Write # statement inserts delimiters (double-quotes, etc)!!
    Print #1, "Database Name: " & strDb
    Print #1, "Form Name: " & strForm
    Print #1, "Code Module Text:"
    Print #1, String(60, "-")
    Print #1, mdl.Lines(1, n)
    Close #1

    ' Not needed if called from open form:
    ' DoCmd.Close acForm, strForm
    Set mdl = Nothing

    End Function</pre>

    To call function from current open form, create custom toolbar command with following On Action property:
    <pre>=ExportFormModuleText(Screen.ActiveForm.Name) </pre>

    The text file is saved in same directory as the current database. File is named [Name of Form]_Module.txt (this can be modified). Report code modules can be exported using same technique. Of course, you can export form or report code module to text file "manually" by selecting form/report in VB Editor Project Explorer window, right-clicking, an selecting "Export File..." The form/report module will be exported to a *.CLS file ("Class file"), basically a text file with some metadata that can be imported into another project. This function can be modified to export standard modules as alternative to OutputTo method.

    HTH

  12. #12
    Lounger
    Join Date
    Aug 2001
    Location
    Lanham, Maryland
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup Code Modules

    Mark,

    Thanks for your suggestions. Does anyone know why Microsoft messed around with the LastUpdate property? It was nice to be able to output only those modules which had changed on a particular date in Access 97>

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Backup Code Modules

    It had to do with the switch to true VBA in 2000 - and I agree - it is a pain in the neck. In additon, if you compact a database it resets all forms and reports to the compact date.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •