view all of my SQL statements

  • Thread starter Thread starter ragtopcaddy via AccessMonster.com
  • Start date Start date
R

ragtopcaddy via AccessMonster.com

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update local table with Sybase ODBC data in update query 4
funky decompile behavior 2
Code won't run when I hit F8 or F5 3
Decompile a secured mdb 9
No dates in 1st 5 records of excel spreadsheet 4
Trying to use a form tab control's page name as a query criteria 2
SQL "VIEW" Statement 5
scroll wheel behavior in combo box 2

Back
Top