Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

view all of my SQL statements

Discussion in 'Information Technology' started by ragtopcaddy via AccessMonster.com, Jul 28, 2006.

  1. ragtopcaddy via AccessMonster.com

    Guest

    Is there a function or sub that I can employ to download all of my SQL to a
    text file so I can search for and replace a certain string (a form name) in
    all of the code and then replace the existing sql with the edited sql?

    --
    Bill Reed

    "If you can't laugh at yourself, laugh at somebody else"

    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access/200605/1
     
  2. Loading...

    Similar Threads Forum Date
    Ashdoc's Movie Review---Force 2 Theatre, Movies & Cinema Nov 20, 2016
    Ashdoc's Movie Review---Rock On 2 Theatre, Movies & Cinema Nov 13, 2016
    Movies Alternate View - Review Of 'PROMETHEUS' Theatre, Movies & Cinema Nov 12, 2016
    Ashdoc's Movie Review---Mr Church Theatre, Movies & Cinema Nov 5, 2016
    Ashdoc's Movie Review---Ae Dil Hain Mushkil Theatre, Movies & Cinema Nov 1, 2016

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    If this is something you're going to have to do from time to time, consider
    purchasing a 3rd party product such as Speed Ferret from Black Moshannon
    Systems http://www.moshannon.com/ or Find and Replace from Rick Fisher
    Consulting http://www.rickworld.com/products.html

    You can always use the undocumented SaveAsText (and its inverse,
    LoadFromText). Arvin Meyer shows how to use SaveAsText at
    http://www.datastrat.com/Code/DocDatabase.txt: simply replace SaveAsText
    withg LoadFromText to get them back into your database.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "ragtopcaddy via AccessMonster.com" <u9289@uwe> wrote in message
    news:60c7b2ea2eab2@uwe...
    > Is there a function or sub that I can employ to download all of my SQL to

    a
    > text file so I can search for and replace a certain string (a form name)

    in
    > all of the code and then replace the existing sql with the edited sql?
    >
    > --
    > Bill Reed
    >
    > "If you can't laugh at yourself, laugh at somebody else"
    >
    > Message posted via AccessMonster.com
    > http://www.accessmonster.com/Uwe/Forums.aspx/access/200605/1
     
  4. kabaka

    kabaka
    Expand Collapse
    Guest

    For any sql you've got in VBA -- why not just open a module (any module) do a
    CTRL-F or CTRL-H and choose to search the "Current Database". Should be a
    lot simplier than getting all your SQL into notepad or word.

    For sql that you've just got in Queries -- you can use the documenter to get
    the sql into a report (and from there export it into Word to make your
    changes). Tools --> Anayze --> Documenter. Select all your queries and
    under "options" make sure SQL is selected. From the report click the
    "Publish it with Word" button. Make your changes in word & then copy the new
    sql over top of your old sql in access. Of course, if you've got a whole lot
    of queries to do this for, it might not be practical.

    For a slicker solution, I'm sure that Douglas's advice is better.




    "ragtopcaddy via AccessMonster.com" wrote:

    > Is there a function or sub that I can employ to download all of my SQL to a
    > text file so I can search for and replace a certain string (a form name) in
    > all of the code and then replace the existing sql with the edited sql?
    >
    > --
    > Bill Reed
    >
    > "If you can't laugh at yourself, laugh at somebody else"
    >
    > Message posted via AccessMonster.com
    > http://www.accessmonster.com/Uwe/Forums.aspx/access/200605/1
    >
     
  5. ragtopcaddy via AccessMonster.com

    Guest

    Thanks to you both for your excellent suggestions.

    I modified Alvin's code to create the following function:

    Public Function ExpAsTxt(strDesktop As String, strContainer As String,
    Optional blLoad As Boolean)
    Dim cnt As DAO.Container
    Dim doc As DAO.Document
    Dim i As Integer

    On Error GoTo Err_DocDatabase

    If strContainer = "Queries" Then
    For i = 0 To dbLocal.QueryDefs.Count - 1
    If Not blLoad Then
    Application.SaveAsText acQuery, dbLocal.QueryDefs(i).Name,
    strDesktop & "DBDocs" & dbLocal.QueryDefs(i).Name & ".txt"
    Else
    Application.LoadFromText acQuery, dbLocal.QueryDefs(i).Name,
    strDesktop & "DBDocs" & dbLocal.QueryDefs(i).Name & ".txt"
    End If
    Next i
    Else
    Select Case strContainer
    Case "Forms"
    i = 2
    Case "Reports"
    i = 3
    Case "Scripts"
    i = 4
    Case "Modules"
    i = 5
    End Select
    Set cnt = dbLocal.Containers(strContainer)
    For Each doc In cnt.Documents
    Application.SaveAsText i, doc.Name, strDesktop & "DBDocs" & doc.Name
    & ".txt"
    Next doc
    End If

    Set doc = Nothing
    Set cnt = Nothing
    'Set dbs = Nothing

    Exit_DocDatabase:
    Exit Function


    Err_DocDatabase:
    Select Case Err

    Case Else
    MsgBox Err.Description
    Resume Exit_DocDatabase
    End Select

    End Function

    Bill R

    --
    Bill Reed

    "If you can't laugh at yourself, laugh at somebody else"

    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access/200605/1
     
  6. ragtopcaddy via AccessMonster.com

    Guest

    OOPS! That code needs to account for "blLoad" in the non-query containers:

    Public Function ExpAsTxt(strDesktop As String, strContainer As String,
    Optional blLoad As Boolean)
    Dim cnt As DAO.Container
    Dim doc As DAO.Document
    Dim i As Integer

    On Error GoTo Err_DocDatabase

    If strContainer = "Queries" Then
    For i = 0 To dbLocal.QueryDefs.Count - 1
    If Not blLoad Then
    Application.SaveAsText acQuery, dbLocal.QueryDefs(i).Name,
    strDesktop & "DBDocs" & dbLocal.QueryDefs(i).Name & ".txt"
    Else
    Application.LoadFromText acQuery, dbLocal.QueryDefs(i).Name,
    strDesktop & "DBDocs" & dbLocal.QueryDefs(i).Name & ".txt"
    End If
    Next i
    Else
    Select Case strContainer
    Case "Forms"
    i = 2
    Case "Reports"
    i = 3
    Case "Scripts"
    i = 4
    Case "Modules"
    i = 5
    End Select
    Set cnt = dbLocal.Containers(strContainer)
    For Each doc In cnt.Documents
    If Not blLoad Then
    Application.SaveAsText i, doc.Name, strDesktop & "DBDocs" & doc.
    Name & ".txt"
    Else
    Application.LoadFromText i, doc.Name, strDesktop & "DBDocs" & doc.
    Name & ".txt"
    End If
    Next doc
    End If

    Set doc = Nothing
    Set cnt = Nothing

    Exit_DocDatabase:
    Exit Function


    Err_DocDatabase:
    Select Case Err

    Case Else
    MsgBox Err.Description
    Resume Exit_DocDatabase
    End Select

    End Function

    --
    Bill Reed

    "If you can't laugh at yourself, laugh at somebody else"

    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access/200605/1
     

Share This Page