Here is some code that can be usefu 9

[jw45]

Here is a function I wrote to test if a table exists and if so delete it.

Function DelExists(TableName)
On Error GoTo TableDoesntExist
Dim FindTable As DAO.Recordset

'*************************************************

Set FindTable = CurrentDb.OpenRecordset(TableName)
FindTable.Close
DoCmd.DeleteObject acTable, TableName

'*************************************************

TableDoesntExist:

End Function

 

[scking]

Just another way of getting the info. This returns a list of the queries that have the 'Find' string. I used it to fill a listbox which could be clicked and it would then fill a textbox with the SQL and highlight the 'Find' string. Of course this only works with .MDB (Jet) databases.


Public Function SearchQuery(Find As String)

Dim Hitlist As String
Dim intCnt
intCnt = CurrentDb.QueryDefs.Count
Dim qdf As QueryDef

For intCnt = 0 To CurrentDb.QueryDefs.Count - 1
Set qdf = CurrentDb.QueryDefs(intCnt)
If InStr(1, qdf.SQL, Find) > 0 Then
Hitlist = Hitlist & qdf.Name & ";"
End If
Next intCnt
SearchQuery = Hitlist

End Function

---------------------
scking@arinc.com
---------------------

 

[JerryKlmns]

scking,

It's shorter with no temp table! I like yours more. I 've copied already.....

Thanx and have a *

 

[scking]

One small correction to my earlier post. The semicolon delimiters provide the CR/LR type function for the listbox but you might also strip off the last semicolon after it comes out of the loop if the length is > 0.

If Len(Hitlist) > 0 Then
Hitlist = Left(Len(Hitlist) - 1)
End If

---------------------
scking@arinc.com
---------------------

 

[jw45]

scking... typo in your correction

should be

Hitlist = Left(Hitlist, Len(Hitlist) - 1)

 

[jw45]

scking.....I forget ....here's your *

 

[scking]

Thanks jw45. You knew what I meant to type.

---------------------
scking@arinc.com
---------------------

 

[CCRT]

Can this code be tweaked to list query to query relationships?

 

[scking]

Currently it simply finds a string in the SQL for all queries but you pose an interesting question. The code would need to scan the SQL and identify objects that it is searching for. The rules, and I'm not claiming that these are the best but just an example, could be to find all full words directly after the keywords FROM or JOIN and compare them with the objects in the system tables to determine what they are, tables or queries. Then the JOIN phrase could be analyzed to determine the exact relationships between the objects. Results could go in a temporary table or be returned as a string. It would be a significant enhancement, but, if you have a requirement for this type analysis it could save quite a bit of time.

---------------------
scking@arinc.com
---------------------

 

[CCRT]

It would be a valuable tool.

I do have the need for this, as a few of my database are heavily reliant on queries querying queries. Unfortunately my VBA skills are at the infancy stage in comparison to the contributors on this board. But what better way to enhance the learning curve than to take on the challenge.

Thanks

http://home.comcast.net/~ccrt-racing/ccrt.htm

 

[LonnieJohnson]

That's pretty interesting scking. Worth looking into.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[jrbarnett]

If you want to find out how to determine table relationships and referential integrity (plus a lot more information besides), take a look at the source code of MDB Doc.

Its something I've used for years, the first MDB Doc was a pale imitation of its current self, used to find out information about a messy system I inherited. As I discovered how to obtain more useful information, it grew.

John

 

[jsteph]

For free code, the above is usefull. For an extremely comprehensive tool for the above and more, I've always used SpeedFerret. It's very inexpensive and can find based on any of the common search filters, such as Match Case, Whole Word, Start of Word, End of, etc. etc.

It gives the option to find your string in the .SQL, any of the PropertyPage settings, a module, Form, Report, or any combination of objects, properties, etc.
--Jim

 

[jrbarnett]

For a free global search tool (no replace) for MS Access as well as a few other useful bits and pieces, try V Tools from

http://www.skrol29.com/us/developpements.php

John

 

[LonnieJohnson]

Cool tools jrbarnett

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!