what should a novice learn first

[molly00mayhem]

hi everyone

i seem to be in an msaccess mood this wkend. i turned down doing summer school this year to devote more time to databases but we could sure have used the money, guess who reminded me of that and who is espessoed out again. btw to those kicking the stocks and dividends thing around, put some bucks into any espresso mfr. we must be financing half the dividends right from our own little abode!

i get a kick out of reading the different posts and i see sql popping up as often as dandelions in june. so as a novice i begin to think maybe i should investigate sql in addition to basic table/form design and then i think well maybe i should introduce sql to my students next year if i teach the programming class which is just about clinched but that could possibly confuse them with advanced concepts they cant really use. the class will be 6th graders.

so, i think i need some help getting a direction for study here. theres so much to learn, how should i decide what should be now and what should be later?

also btw it seems there is a core group of pros who are the workhorses here and who ive been interacting with from the getgo. i feel lucky to have landed here, especially since this is the first forum ive ever landed in.

cheers!

[twinnyfo]

Hi Molly,

From a very, very basic perspective, what you should learn first is proper table design. There are a plethora of articles (here is one on Bytes) that talk about Database Normalization. It can truly be a foreign concept, but understanding it well can save you thousands of hours in headaches and rework—I speak as one who learned the long, hard way how NOT to design a database. PhilOfWalton also has a good article (Word Doc) that I know he is willing to share with you.

Oddly enough, the second place I would go for learning is proper naming conventions (any of the other experts are free to disagree with me here). However, when designing a database, you want to name your objects something that makes sense, is not too long, avoids key words or restricted words AND is something the DB can use easily. Do a Google search for database naming conventions and you will gets some good ideas. There are no “right” naming plans, but just choose one and stick with it. For example, in your original post, my assumption was that you would not have text box named the same as one of your fields, but rather “txtFieldName”. That was an oversight on my part, because it is second nature for me to add a control to a form/report and assign it a proper name (including all my labels). Again, doing this will save you headaches in the long run.

Third, you will want to get a basic understanding of what queries are, how they work, how tables can be JOINED, how to use criteria in queries and a thorough understanding of Boolean logic (which is how queries determine their criteria. You do not need to be an expert in queries (SQL) at first, but you need to understand the basics. One of the smartest dudes on this forum in SQL is Rabbit. Keep an eye out for his responses and absorb them carefully. He is brief in his responses, but really understands how to build a query. I’ve learned a lot from him.

Fourth, avoid macros; learn the basics of VBA. Macros are incredibly limited and are NOT programming, in my opinion. As an added guideline, add error handling to every single procedure you create (you can search for the basics and I will post a quick and easy sub you can use when I get to work).

My best advice is to think about what you want to do (e.g., “I want to change the value of a text box based upon the value of a another text box.”) and work through the baby steps of how you would do that:

1. I need the value of the first text box
2. I need to check that this is a valid value
3. I need to manipulate that value
4. I need to make sure the result is valid
5. I need to assign that value to the new text box

Over time the baby steps will come naturally, then you will expand your understanding.

Always keep this in mind: NO ONE on this site became experts overnight. I encourage you to go to my profile and look at my oldest posts. I would have considered myself a noob, even though I was doing this for a living. NeoPa, zmbd, Rabbit, PhilOfWalton and others have taught me much. Be patient with yourselves, but be committed to learning and trusting what the experts are trying to teach you. If you don’t understand a concept, ask. I’ve asked for clarification many times, and the answer always helps me understand my craft more fully.

I am sure there is more to follow.

You offered a penny for my thoughts, and I threw my two cents in.

Hope this hepps!

[PhilOfWalton]

Hi Molly

I was going to reply in exactly the same way as Twinnyfo.

The only addition I would add is that together with correct table design, you need to set up the relationships between tables.
You must get this right as this is the foundation for a database. You can't build a house on dodgy foundations and expect it to last.

I am sure if you have looked at various questions on Bytes, so many of them are caused by incorrect table structures.

The other addition to twinnyfo's reply is to use the correct description of controls on a form or report. Recently we have had Command Buttons called Tabs and Pages called Tabs or Tab Controls. Post 18 onhttps://bytes.com/topic/access/answe...-certain-pages might help.

Here is my pathetic effort on normalisation. See post no 9 on

https://bytes.com/topic/access/answe...ent-field-null

Phil

[twinnyfo]

As mentioned earlier, in a new standalone VBA module put the following and save it:

Option Compare Database
Option Explicit
 
Public Function ErrorSub()
'Just useful and quick way to add Error Handling to Code
 
    Debug.Print "On Error GoTo EH" & vbCrLf
    Debug.Print "    Exit Sub"
    Debug.Print "EH:"
    Debug.Print "    MsgBox " & Chr(34) & _
        "There was an error ....!" & Chr(34) & " & vbCrLf & vbCrLf & _"
    Debug.Print "        " & Chr(34) & "Error: " & Chr(34) & _
        " & Err.Number & vbCrLf & _"
    Debug.Print "        " & Chr(34) & "Description: " & Chr(34) & _
        " & Err.Description & vbCrLf & vbCrLf & _"
    Debug.Print "        " & Chr(34) & _
        "Please contact your Database Administrator." & _
        Chr(34) & ", vbCritical, " & Chr(34) & "WARNING!" & Chr(34)
    Debug.Print "    Exit Sub"
 
End Function

Then, in your immediate window, just type ErrorSub, and hit enter. Copy and paste the results into your new procedures. All your code goes between On Error GoTo EH and the first Exit Sub. Replace the "..." with what your procedure is doing. If you use this with a Function instead of a Sub, just replace all the Subs with Function.

This has been helpful for me because my database is used by others, and I often don't see the errors when they pop up. This allows the user to tell me what went wrong.

There are tons of very involved and complicated methods for handling errors, but this is the simplest and most straightforward.

Hope this hepps!

[PhilOfWalton]

@Twinnyfo

Although this error routine is far more complex, it does give a "written" record of unhandled errors by creating a text file. Useful when other people are using your Db.

Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription As String, strCallingProc As String, _
    Optional lngLineNo, Optional vParameters, Optional bShowUser As Boolean = True) As Boolean
 
    ' Logs errors to external file "ErrorLog.Txt".
    ' Arguments: lngErrNumber - value of Err.Number
    ' strErrDescription - value of Err.Description
    ' lngLineNo - Value if Erl is provided
    ' strCallingProc - name of sub|function that generated the error.
    ' vParameters - optional string: List of parameters to record.
    ' bShowUser - optional boolean: If False, suppresses display.
    ' Author: Allen Browne, [e-mail]
 
    Dim DbT_Message As String      ' String for display in MsgBox           ' ** Ignore **
    Dim StrFile As String
    Dim FileNumber            As Integer
    Dim StrErr As String
 
    On Error GoTo Err_LogError
 
    Select Case lngErrNumber
    Case 0, 2501, 3314, 2101, 2115
        ' Do Nothing
    Case Else
        StrFile = CurrentProject.Path & "\ErrorLog.txt"
        FileNumber = FreeFile                           'Get unused file number.
        Open StrFile For Append As #FileNumber          'Create file name.
 
        Print #FileNumber, "Database: " & Application.CurrentProject.Name & "    Calling Process: " & strCallingProc
        StrErr = "Error No: " & lngErrNumber & "  " & strErrDescription
 
        If Not IsMissing(lngLineNo) Then
            If Nz(lngLineNo) > 0 Then
                StrErr = StrErr & "  On Line No: " & lngLineNo
            End If
        End If
        Print #FileNumber, StrErr
        Print #FileNumber, "Date Time: " & Now & "  User: "; Nz(fOSUserName)
        If Not IsMissing(vParameters) Then
            Print #FileNumber, "Parameters: " & vParameters
        End If
        Print #FileNumber,
        Print #FileNumber,
        LogError = True
    End Select
 
    Select Case lngErrNumber
    Case 0
        If TempVars!ConDebug = 15 Then Debug.Print strCallingProc & " called error 0."
    Case 2501                ' Cancelled
        'Do nothing.
    Case 3314, 2101, 2115    ' Can't save.
        If bShowUser Then
            DbT_Message = "Record cannot be saved at this time." & vbCrLf & _
                "Complete the entry, or press <Esc> to undo."
            Msgbox DbT_Message, vbExclamation, strCallingProc
        End If
    Case Else
        If bShowUser Then
            DbT_Message = "Error " & lngErrNumber & ": " & strErrDescription
            If Not IsMissing(lngLineNo) Then
                If Nz(lngLineNo) > 0 Then
                    DbT_Message = DbT_Message & "   Line No: " & lngLineNo
                End If
            End If
            Msgbox DbT_Message, vbExclamation, strCallingProc
        End If
    End Select
 
Exit_LogError:
    Close #FileNumber                               'Close file.
    Exit Function
 
Err_LogError:
    DbT_Message = "An unexpected situation arose in your program." & vbCrLf & _
        "Please write down the following details:" & vbCrLf & vbCrLf & _
        "Calling Proc: " & strCallingProc & vbCrLf & _
        "Error Number " & lngErrNumber & vbCrLf & strErrDescription & vbCrLf & vbCrLf & _
        "Unable to record because Error " & Err.Number & vbCrLf & Err.Description
    Msgbox DbT_Message, vbCritical, "LogError()"
 
    Resume Exit_LogError
 
End Function
 

It is called using something like

Function Translate(InputStr As String, ControlTypeID As Long, FieldNames As String, ColCount As Integer, _
    OutLanguage As String, Optional InLanguage As String, Optional ShowIE As Boolean = 0) As String
 
    On Error GoTo Translate_Err
 
    ' Blah, Blah Blah
 
FinalTranslate_Exit:
    Exit Function
 
Translate_Err:
    If Err = 3022 Then                          ' Duplicate
        Resume FinalTranslate_Exit
    ElseIf Err = -2147467259 Then               ' Automation error Unspecified error (Internet Explorer running multiple times)
        Resume FinalTranslate_Exit              ' Exit
    ElseIf Err = 424 Then                       ' Object required
        Resume FinalTranslate_Exit
    Else
        LogError Err, Err.Description, "Translate", Erl, Marker
    End If
 
End Function
 

This is part of the text file produced

Database: DB Phil.accdb    Calling Process: CheckTranslation
Error No: 91  Object variable or With block variable not set
Date Time: 30/04/2018 13:33:39  User: Philip
Parameters: 0
 
Database: DB Phil.accdb    Calling Process: CheckTranslation
Error No: 91  Object variable or With block variable not set
Date Time: 30/04/2018 13:33:40  User: Philip
Parameters: 0
 
The Language Pack for: French is not installed
Date Time: 30/04/2018 14:41:39  User: Philip
 
The Language Pack for: German is not installed
Date Time: 30/04/2018 15:07:07  User: Philip
 
The Language Pack for: Spanish is not installed
Date Time: 30/04/2018 15:49:06  User: Philip
 
Database: DB Phil.accdb    Calling Process: TranslateMsgBox
Error No: 3021  No current record.
Date Time: 30/04/2018 15:49:08  User: Philip
Parameters: 0
 
Database: Translate 17 May 2018.accdb    Calling Process: TranslateMsgBox
Error No: 3021  No current record.
Date Time: 17/05/2018 16:48:47  User: Philip
Parameters: 0
 

It's another way, but as I said, for client's databases, it can help

Phil

[molly00mayhem]

hi twinnyfo

thank you for the post. you know, ive been sitting here reading these new posts with all the hard earned advice of you and philofwalton and everyone, and having read the recommended articles, and i think im just your basic slightly chunky down home country girl who decided to try her hand in the big city. the men in my family are laborers and the women are homemakers and i grew up being sandwiched between the two. i can plow a field on a tractor and bake a pretty good cornbread but i never would have survived my first years of teaching if i hadnt absorbed the experience of generations behind me. you all can believe i know what your help is worth.

well, dry my eyes and down to business. yes, table design first and good naming conventions (im going to use neopas method on that). so, first question about tables. i know that primary keys are important and that everyone in the known universe recommends the autoincremental field and i know i should do it, but it seems so abstract to me and just another field to keep track of. ive been using just my kids names as a pk since they wont change so i still have a unique row or record. so should the default pk always be a number value?

and yes, no to macaronis (macros) haha. i tried them a few times and decided they were just too many knobs on the crockpot.

as my kids would say "hab a good 'un!"

[twinnyfo]

In 99 44/100% of all cases, each Table should have a numeric PK. THe PK is simply a unique identifier for each record. In most cases, you can ignore the number, because it is meaningless to you. However, to the computer, the PK means everything (if you create your tables properly).

So, you said, you use your kids names as a PK in a table "because they never change". But that's not the purpose of the PK. Again, the PK is there to identify records.

Let's say one of your children's names is "John Jacob Jingleheimerschmidt". Now, that's not going to change, and it is a truly unique identifier. However, in your database, you tell it to pull up the record for "John Jacob Jingleheimerschmidt". It will then find the record associated with "John Jacob Jingleheimerschmidt". Now, you want to count how many classes "John Jacob Jingleheimerschmidt" has taken. So you query the table tblClasses for all classes taken by "John Jacob Jingleheimerschmidt". After a few queries, your fingers are worn to the bone from typing "John Jacob Jingleheimerschmidt". Imagine your daughter being named "Luisa Maria Teresa Sally Jesse Raphaela Gabriella Sarsasparilla Rojelio Rodriguez Montoyo Sanchez"!!!!

But, if your son's record is identified as 11. Then, in your database, you tell it to pull up the record for 11. It will then find the record associated with "John Jacob Jingleheimerschmidt", based on the PK. Now, you want to count how many classes "John Jacob Jingleheimerschmidt" has taken. So you query the table tblClasses for all classes taken by 11. Much easier on the fingers.

Also, if you use a long integer Autonumber, the size of the field for your PK is only 4 bytes. However, once you set a text field for your PKs, the size increases dramatically. So, over time, as your database grows, you are saving space. Additiaonlly, every time you have a relationship to another table, you use the tables PK, so you save space there, too. Imagine if Son 11 took 150 classes. Then "John Jacob Jingleheimerschmidt" would be in your classes table 150 times! That's a lot of wasted (unecessary space).

BTW, I don't use the autonumber for all my PKs. If I have a table that is relatively stable, I use an integer PK. This uses only 2 bytes for its PK. However, if I only have 8 items, and I'm not constantly adding records, I can manage that table's PK's manually (or programatically).

I know Phil has lots of advice on this stuff too. He's been doing it a couple years....

Hope this hepps!

[molly00mayhem]

hi philofwalton

thanx for the post. ive read your articles and yes, it will take me awhile to wrap my head around normalization theory but i can dimly at this point see how normalizaton begets good table design which begets good relationship design which begets good query design and they are all necessities. i must admit before joining this group i wouldnt have even considered working toward such a good ground level rooting and a year from now i would have been flopping around in an empty fishbowl <lol>. but i have the summer to lounge and learn and you wont see any dust on my sneakers.

also, i too have noticed the different ways people refer to controls but if im gathering your posts correctly, then a subform <control> contains another form and a tab <control> contains pages and by extension listbox and combobox <controls> contain items. are these the correct terminologies to use for future reference?

btw, your "pathetic effort on normalisation" is anything but. methinks you are sometimes too modest :)

cheers!

[NeoPa]

Molly:
it seems there is a core group of pros who are the workhorses here and who ive been interacting with from the getgo.

That's about the size of it Molly. They change over time, but we've been very lucky in the Access forum over the years with good, solid and very reliable experts who've posted a great deal of answers for a large number of members. Mostly less pleasurable to deal with than you, but you set a high bar.

I mentioned your attitude earlier. This question is another good example of that. Well done.

I can't do a better job of introducing the subject of Access and answering your question, but I do feel you'll appreciate reading up a little on the whys and wherefores of indexing. Access is a type of ISAM (Indexed Sequential Access Method) database. The amount of data in an index is important because the indices (indexes) are stored together on disk and each disk read grabs as many as will fit in a single read operation. The larger they are, the fewer items can be read in one go.

I won't go into detail about B-Trees but I will say they a very quick and easy to traverse without having to load in all the data from all the records.

You may find SQL Performance Explained (preview) interesting. If you read the first chapter you should get answers to a lot of those questions about how the heck it manages to do what it does so quickly. I suspect you'll enjoy the read. You may also find yourself understanding some of the issues better than many around you.

[molly00mayhem]

hi twinnyfo

heehee i like the way you explained the pk thing. a bit of humor and a bit of fact. you would probably make a good el ed teacher. so, it seems clear now that an autonumber field is mandatory for "99 44/100% of all cases". ill just call and raise you to 100%. if its that important why not go all the way? i also take it that when designing table relationships these autonumbers are the preferred links, right?

btw, if i ever have a daughter i might name her "Luisa Maria Teresa Sally Jesse Raphaela Gabriella Sarsasparilla Rojelio Rodriguez Montoyo Sanchez" if ive had a few espressos with giamber, but where i come from there are a lot of saras and sallys and amys running around. in the end, i would probably just call her lou and hope she grows up just as happy in jeans as in dresses :)

cheers!

[twinnyfo]

Note to Mods:

All new OPs must first register to take "Interactive Online Relationships 101" classes from molly00mayhem.

Note to Molly: thanks for making the "everyday" more "enjoyable"!

[molly00mayhem]

hi neopa

thanx for the pat on the back and once again for the tips. positive reinforcement goes a long way with me. as grampa says you can catch more flies with sugar than vinegar although he wasnt necessarily looking at gramma when he said it, oops! well, ive always been a high achiever, or more accurately, strived to do my best. i hope im instilling that in my kids as well.

if youre talking about the pdf by winand, i just downloaded it. i feel like im back in college again. about all i know about indexes right now is that you can edit the value of the Indexed field to yes in the page on the tab control at the bottom of the table design form. hows that for slinging the lingo?

btw, you mentioned disk reads and i know just enough about hard drives and ram etc. to make me dangerous. this may be a little off topic but should i be concerned about optimizing my hardware in any way to better accommodate db work? i have a 5 yr old g780 lenovo laptop with a terrabyte hd, 4g ram and a dual core intel processor rated at 2020m up to 2.4hz whatever that means running windows 10 and msaccess 2016 off a 365 home subscription from ms.

well, i should be getting supper ready and should have had the spaghetti sauce simmering since 6am, but 4 hours ought to do well enough before giamber comes trotting in with a loaf of italian bread :)

cheers!

[molly00mayhem]

hi twinnyfo

right back at ya! couldnt ask for a more funly group of midnight poker players.

cheers!

[zmbd]

molly00mayhem
I'll PM you a little boilerplate that has a few links here and there for some tutorials, reference materials, etc...
Check your Bytes.com Inbox

[NeoPa]

Molly:
should i be concerned about optimizing my hardware in any way to better accommodate db work?

No. You should be concerned only to understand what's going on better so that whatever hardware you have isn't wasted unnecessarily.

Frankly, databases are so clever and forgiving with modern hardware, that you won't even notice most improvements until you start dealing with large amounts of data. Of course, the more the size and requirements grow, the more important will be the understanding. Get in there now and don't get caught out when you get asked to work with grown-up databases.

You won't be surprised to learn that I bought that book on the recommendation of Rabbit. I've never regretted it.

[molly00mayhem]

hi zmbd

well, its 7am and giamber is getting ready for work, slurping his espresso slushy and grousing because he has to bring home the bacon and i get to lie around all day. so ok ill fix him some oatmeal and fruit and yogurt and juice and toast the rest of last nights bread, pat his butt and send him on his way.

so yes today im headed for the links. not golf mind you but all those articles and references you sent me. whooooooeeeee! is this <ALL> i have to do to get started <rotfl>? i can see that i wont be reading another magazine or newspaper for the next, oh say, 2 centuries. i figure just opening them up will take half a day haha. my plan is to open them all up and just run through them to get a taste and then go back, pick out some and really start to munch on them.

you know, in the ads, ms makes it sound like all you need to do is pop a bonbon into your mouth, do your nails and click your mouse a few times and presto whizzo theres a database! no sweat msaccess does it all for you. well ms, i can give you a country girl earful about that.

seriously, thank you so much for taking the time to do this. you all are so good. also, for the posting guidelines. i should have looked them up before even my first post but im new to forums so ill plead dumb noobie on this one. i downloaded all the links you provided except i got a 404 3 times on
Fundamentals of Relational Database Design.

oh btw, didnt know i had a mailbox too. this forum thinks of everything.

so, let me get started. be prepared for a few questions im sure :)

hab a good 'un!

[molly00mayhem]

hi neopa

oh good because while i can change a flat tire id probably fry the motherboard swapping a hard drive. and "grown-up databases" you say? mine are still wiggling their baby toes heehee. later

cheers!

[twinnyfo]

Hi Molly (and others who can benefit from this offering),

I am sharing with you what, perhaps, has been the most useful set of Procedures in my possession.

Have you ever been working on a Database Project (and it keeps growing and expanding and getting larger and larger) and things change over time and you redefine queries or change certain attributes or this or that or other? Well, let's say you have a particular Query that is referenced throughout your project--and then you change the name of the Query or redefine it (out of necessity or improvements [or stupidity]) and now a bunch of your stuff doesn't work becuase you were a moron!!!!

How on earth are you gonig to find all the references to that one query when your DB has hundreds of objects in it?

Simple--search your project. The following procedures can search your TableDefs, QueryDefs, Reports, Forms and Modules (separately) or you can search the entire project for whatever you want!!!

I do not claim ownership of this code, I am certain I got it somewhere here on Bytes and I've made a few minor tweaks to serve my needs, but it has been very useful as I continue to expand my DB.

Create a stand alone module and name it modSearchProject. You can run any of these procedures from your Immediate Window:

Option Compare Database
Option Explicit
 
Public Sub SearchTableDefs(strSearch As String)
On Error GoTo EH
    Dim tdf         As DAO.TableDef
    Dim fld         As DAO.Field
    Dim prp         As DAO.Property
    Dim intColumns  As Integer
    Dim intFields   As Integer
    Dim strSource   As String
    Dim strTDFList  As String
    Dim strMsg      As String
 
    strTDFList = vbNullString
    For Each tdf In CurrentDb.TableDefs
        intColumns = tdf.Fields.Count - 1
        For intFields = 0 To intColumns
            For Each prp In tdf.Fields(intFields).Properties
                If prp.Name = "RowSource" Then
                    strSource = prp.Value
                    If InStr(1, strSource, strSearch, vbTextCompare) > 0 Then
                        strTDFList = strTDFList & Chr(9) & _
                            tdf.Name & _
                            " - " & tdf.Connect & _
                            " - " & tdf.Fields(intFields).Name & _
                            " - " & tdf.Fields(intFields).Type & _
                            " - " & prp.Value & vbCrLf
                    End If
                End If
            Next prp
        Next intFields
    Next tdf
    strMsg = "Text '" & strSearch & "' found in "
 
    If Len(strTDFList) > 0 Then
        strMsg = strMsg & "Table Defs:" & vbCrLf & strTDFList
    Else
        strMsg = strMsg & "No Table Defs"
    End If
 
    Debug.Print strMsg
 
    Exit Sub
EH:
    Resume Next
End Sub
 
Public Sub SearchQueryDefs(strSearch As String)
On Error GoTo EH
    Dim qDf         As DAO.QueryDef
    Dim strQDFList  As String
    Dim strMsg      As String
 
    strQDFList = vbNullString
    For Each qDf In CurrentDb.QueryDefs
        gstrSQL = qDf.sql
        If InStr(1, gstrSQL, strSearch, vbTextCompare) > 0 Then
            strQDFList = strQDFList & ";" & vbCrLf & Chr(9) & qDf.Name
        End If
    Next
 
    strMsg = "Text '" & strSearch & "' found in "
 
    If Len(strQDFList) > 0 Then
        strMsg = strMsg & "Query Defs:" & vbCrLf & Mid(strQDFList, 4)
    Else
        strMsg = strMsg & "No Query Defs"
    End If
 
    Debug.Print strMsg
 
    Exit Sub
EH:
    Resume Next
End Sub
 
Public Sub SearchForms(strSearch As String)
On Error GoTo EH
    Dim dbs     As Object
    Dim oAO     As AccessObject
    Dim frm     As Form
    Dim ctrl    As Object
 
    Set dbs = Application.CurrentProject
 
    For Each oAO In dbs.AllForms
        DoCmd.OpenForm oAO.Name, acDesign
        Set frm = Forms(oAO.Name)
        For Each ctrl In frm.Controls
            Select Case ctrl.ControlType
                Case acTextBox, acComboBox, acListBox, acCheckBox
                    If InStr(1, ctrl.ControlSource & "", strSearch) Then
                        Debug.Print "Form: " & frm.Name & ": " & ctrl.Name
                    End If
            End Select
        Next
        DoCmd.Close acForm, oAO.Name, acSaveNo
    Next
 
    Set ctrl = Nothing
    Set frm = Nothing
    Set oAO = Nothing
    Set dbs = Nothing
 
    Exit Sub
EH:
    Resume Next
End Sub
 
Public Sub SearchReports(strSearch As String)
On Error GoTo EH
    Dim dbs     As Object
    Dim oAO     As AccessObject
    Dim rpt     As Report
    Dim ctrl    As Object
 
    Set dbs = Application.CurrentProject
 
    For Each oAO In dbs.AllReports
        DoCmd.OpenReport oAO.Name, acDesign
        Set rpt = Reports(oAO.Name)
        For Each ctrl In rpt.Controls
            Select Case ctrl.ControlType
                Case acTextBox, acComboBox, acCheckBox
                    If InStr(1, ctrl.ControlSource & "", strSearch) Then
                        Debug.Print "Report:" & rpt.Name & ": " & ctrl.Name
                    End If
            End Select
        Next
        DoCmd.Close acReport, oAO.Name, acSaveNo
    Next
 
    Set ctrl = Nothing
    Set rpt = Nothing
    Set oAO = Nothing
    Set dbs = Nothing
 
    Exit Sub
EH:
    Resume Next
End Sub
 
Public Sub SearchModules(ByVal strSearch As String)
On Error GoTo EH
    Dim objComp     As Object
    Dim strMsg      As String
    Dim strModList  As String
 
    strModList = vbNullString
 
    For Each objComp In Application.VBE.ActiveVBProject.VBComponents
        If objComp.CodeModule.Find(strSearch, 1, 1, -1, -1) = True Then
            strModList = strModList & ";" & vbCrLf & Chr(9) & objComp.Name
        End If
    Next objComp
 
    strMsg = "Text '" & strSearch & "' found in "
    If Len(strModList) > 0 Then
        strMsg = strMsg & "modules:" & vbCrLf & Mid(strModList, 4)
    Else
        strMsg = strMsg & "no modules"
    End If
 
    Debug.Print strMsg
 
    Exit Sub
EH:
    Resume Next
End Sub
 
Public Sub SearchProject(strSearchWord As String)
On Error GoTo EH
 
     SearchTableDefs strSearchWord
     SearchQueryDefs strSearchWord
     SearchForms strSearchWord
     SearchReports strSearchWord
     SearchModules strSearchWord
 
    Exit Sub
EH:
    Resume Next
End Sub

Hope this hepps you (and others).

[NeoPa]

Hi Twinny.

You, and possibly others, may find Log Code and Property References helpful. It does a similar job - possibly more comprehensively. I still use it quite heavily. It's invaluable for projects you've inherited and ones that have spread over time.

[twinnyfo]

@NeoPa:

I remember using your RefLog DB in the past--when my DB was much more smaller. I remember it took quite a while to run (and right now I am at a work stand-still, because it is pushing an hour.....). I remember that this DB would be great for documenting my DB, and I still think that is true.

However, the code I listed above is more useful "in the moment," in that it takes about 5-10 minutes to run through my entire DB and locates all occurrences of a particular text string and then tells me where they are.

For example, our boss recently changed his designation from "ACC/CV" (Vice Commander) to "ACC/CD" (Deputy Commander). Because my DB has a lot of pre-scripted e-mails that it generates, I searched for all occurrences of "ACC/CV" and a few minutes later I had a list of Modules, Forms and Reports (and even some QueryDefs) that used that text string. A few moments later, my changes were complete.

However, because I know you probably have some tricks up your sleeve, to which I am oblivious, there is probably a three-line piece of code that will generate what takes me nearly 200 lines.



:-P



P.S. - As long as we are talking about documenting (which is on topic, because all beginners should know to document and start learning how to document), seeing your RefLog DB, my mind is swirling into thinking that it might be possible to create a DB documenter which creates a Word document with all the informaiton that you want/need. For example, the DBA chooses what types of Objects they want to document, what properties to display, or criteria for display (e.g., only document the .Visible property of a Text Box if its value is False). Just another thing to keep me awake at night....

[NeoPa]

Hi Twinny.

All valid points I expect :-) Once the data's been loaded the once though, finding all sorts of references can be done in seconds.

If you want a documenter - you may find Crystal Long's site of enormous help. Also very good for producing documentation that can be provided to a client. Easy HTML format. The links to the whole site as there are probably various other items there you may find of interest.

BTW, she's also a member here Strive4Peace even if not massively active at this stage.

[molly00mayhem]

hi twinnyfo

well its 7am again and while giamberto wolfs down his waffles im taking a bite out of the forum, oh thats bad ha. so thank you for the code. very thoughtful of you to anticipate what id need since ive already run into this problem with both of my dbs and all 11 of my tables. but good that this has come up now, just more incentive for me to get it right from the start. it goes into my new module named mod_Utilities. thanx again

cheers!

[molly00mayhem]

hi neopa

downloaded and added to my never diminishing list of things to read. that gets plopped into mod_Utilities as well. thanx.

hab a good 'un!

[molly00mayhem]

hi neopa

yeah, hers was one of the videos from one of the sites from one of the articles one of you guys gave me. dreamy music if i remember right. her website looks yummy too :)

cheers!

[NeoPa]

Go and tell her that then Molly. I expect she'll be thrilled.

Music is certainly one of her things, and she's very approachable.

[PhilOfWalton]

@Twinnyfo

I thought this might me of interest if you want a more graphical method of ananlysing a database.

The advantage of this Db is that if you double click on any item on the form, that becomes "the centre of attention"

The Yellow panel shows the RowSource of the Combo & List Boxes and the RecordSource of the 'Master Object'.

The Green panel shows where the 'Master Object' is used.

Here are some illustrations

So we have selected the database we wish to analyse, pressed the "Analyse Database" button, and after about 10 minutes the form appears as as above.

We are interested in a Subform called "SubSuppliers", and see that it has a Record Source SQL 1046. (This is an arbitrary name because the RecordSource is an SQL statement rather than a saved Query.
Similarly there is a Combo box, (ill-named) as AccIDRelay together with it's SQL RowSource.

On the Green panel we see that SubSuppliers is used as a subform of Form "Suppliers"

So now we double click on "Suppliers" and get

Here is a more complex example

By Double clicking on SQL 1046 in image AnalyseJul1 in the yellow pane, that becomes the "Object of Interest"

SQL 1046 then appears in the Dark Blue panel on the left (in place of "SubSuppliers") and by Double Clicking on that we get

Which is SQL 1046 in Design View.

Hope this might be of interest

Phil

[twinnyfo]

Hey Phil!

That is very much of interest! It has some useful features that I can see would be invaluable for someone inheriting a DB from someone else.

Are you willing to share?

I am working on DB documentation (along the lines of a technical manual) right now, but your DB would have a plethora of uses.

[PhilOfWalton]

Always my pleasure

Let me know when it all goes (in view of this being Molly's question), i'd better say awry.

Cherrs,

Phil

[twinnyfo]

Phil,

Methinks your Analyzer does not like UNION Queries. It hung when it reached one and I had to kill it. I may try again later.

[PhilOfWalton]

Could be.

What exactly were yoy doing when it crashed, so that can have a look.

If you can spot where the code wrong please feel free to alter it, but keep me updated so that a corrected version can be published.

Phil

[molly00mayhem]

hi neopa

i sure will. lets see thats number 6.02 X 10^23 on my list of things to do which oddly enough i keep in a table called tbl_ListOfThingsToDo!

cheers!

[molly00mayhem]

hi philofwalton

wow this is so neat! no more opening every closet door and rummaging for hours trying to find a left sock. can i download it too? way over my head right now but something i would definitely like to grow into :)

cheers!

[molly00mayhem]

hi twinnyfo

well here i am right back where i started from haha. didnt know exactly where to post this, but this is where it all began so i figured id post this here since it seems relevant to the comments and everyones been so <amazingly> helpful and i know they would see it here.

well, giambertos planning a poker party for fri nite and i said oh goody hunting around for my marked deck and he said no its just for the boys and i said oh non goody. i am however permitted to remain if i put on an apron and cap and make and serve munchies and refreshments to that motley crue. down home that might go over, but were in the big city. whatever happened to womens lib?

anyway, i continue on my journey through the stuff you guys sent and decided id concentrate first on table design as recommended and that takes me into normalization which ive really given a good going over. when i look at the tables ive been using i now see nonatomic values, duplicated rows, lots of null values, transitive dependencies, insertion and deletion anomalies and everything else you can think of up the ying yang.

so, i took one big one apart trying to get it into 3NF and now i have a gazillion little tables squeaking all over the place. do developers really do this? am i on the right track here or just overreacting?

oops theres another little squeaker hidden under my mouse :)


cheers!

[twinnyfo]

Molly,

Yes! Good normalization always results in many tables, and many of them will be small (few fields, but could have many records).

It can get complicated, but it really is the best way to go about things.

I also have a lot of queries. If I have specific uses for data that I often repeat, I make a query that I can just run anytime I want. At other times I take that query and use Form/Report Filters (or just use the table). Over time, you will learn the best methodsd that will work for your particular situations. However, it is moreimportant that you understand multiple ways to do things, sometimes, as one way may allow more flexibility than another.

Best of luck serving tea and crumpets!

[NeoPa]

Molly:
i sure will. lets see thats number 6.02 X 10^23 on my list of things to do which oddly enough i keep in a table called tbl_ListOfThingsToDo!

Chop chop. I assume that's done then.

Tea and crumpets?
I suspect that may not be quite the refreshments they're after ;-)

What could be more fun than digging through databases and normalisation while they're doing all that boring poker nonsense? Or not.

Blokes eh? Who'd 'ave 'em?

[molly00mayhem]

hi twinnyfo

crumpets!? dont give those clowns any fancy ideas heehee. gotta go shopping for tomorrow nite. later

hab a good 'un!

[molly00mayhem]

hi neopa

yeah <lol> ill probably find one of them under my rose bush the morning after. my secret plan is to put on my sleaziest dress, load up with a ton of makeup and make weird faces when i peek at their cards. blokes yeah, blimey blokes id call them, definitely not ready for prime time:) later

cheers!

[zmbd]

I'm in for Tea and Crumpets - so wonderful of you to offer, I might be late if travel out of the US is required... passports and all that jazz - :)

As other's have noted, normalization often does result in a lot of tables. One note of caution, sometimes taking a database all of the way down to 3NF, while desirable, is not always practical. Take a basic address book, theoretically, one would need tables for all the states/province, countries, zip-codes, streets, addresses.... adnauseam; however, for things that are static one may not really need to go 3NF when 2NF would suffice - I do have a few databases where I've not completely pulled stuff to 3NF; however, those are very early databases and not my best work.

Anyway, a very simple explanation and example to add to your quest on normalization - IMHO next to SQL it's the most important concept to really get a good understanding - and one of the most difficult, sort of like Chess, easy to learn, hard to master, and once you get the general idea will become alomost second nature (did I just contradict myself :) ): https://support.microsoft.com/en-us/...ization-basics

[PhilOfWalton]

Further to zmbd's remarks about addresses, as a guideline, if a piece of information (say an address line) occurs only a few times in a table (remember what size the table may grow to) that is perfectly OK as far as I am concerned.

However something like States and probably towns/cities are likely to appear many times, so I use separate tables.

Phil

[NeoPa]

Hi Molly.

You realise I was just talking about booze yeah?

[molly00mayhem]

hi zmbd

be careful, first bite and youd never know if i baked em that day or 6 mos ago!

im glad you said that about the addresses. im a detail person and i can get carried away. so a little perspective here would do me good. its fun though, sneaking around my db, coming up behind a transitive dependency and catching it in the act :)

thanx for the url

cheers!

[molly00mayhem]

hi neopa

huh? oh, hahahah yeah, what else!?

cheers!

[molly00mayhem]

hi philofwalton

agreed. ill whittle most of them down to 3nf for the practice and because i like doing things right and because ive invested so much time learning this stuff i might as well use it, but practically some can stay in 2nf if necessary i can always come back at some later date and whittle off a little more.

cheers!

[molly00mayhem]

hi neopa

well the poker party went great. my girlfriend and i whipped up some homemade pizzas in my iron skillets, filled a cooler and fed and watered the hounds til they could live on their own :) thats the only refreshments they got too! heehee then izzie (not her real name) and i settled down to catch up. i think i have found a project to put my new finding db skills to work. shes been running her churchs outreach program for low income individuals and would like a way to keep track of resources besides word docs and spreadsheets which she knows less about than i do which is essentially nothing.

i convinced her that an msaccess db was the way to go and now were all excited. wondering if it would be ok to keep picking up some advice from you guys along the way. just dont want to impose or detract from any primary mission tho. were going down to check out her place right now, so later.

btw, saw/heard your videos. i like i like. must be nice to have your own show!

cheers!

[NeoPa]

Hi again Molly.

We are good with as many questions as you can throw at us. The only restriction we place on that is that they are considered and properly presented (single) questions. If a question is too broad to deal with then just break it down into smaller parts and ask them one at a time. Questions are grist to our mill.

You have a boyfriend AND a girlfriend? Wow. That doesn't sound like middle America to me (rofl). I'm kidding of course. I do really understand that people nowadays refer to g/fs without any romantic association. It's just how the language has evolved. BTW That all sounds fun.

Thanks for the comments on the videos. I don't have a show. I just knock up a video every now and again. The Channel9 one was just being invited by Microsoft to sit down with Crystal and record a quick session chatting about some tools we'd developed. You can probably tell by the fact it was so clearly unrehearsed. We muddled through though - hopefully with not too bad a result. I'm quite pleased with it myself.

We also have a number of members who post about stuff they're doing or have done for their local church. A couple were even MVPs (I won't mention any names as I can't be sure who wishes to share what but I suspect they're all happy to be known as such). So, get 'er done!

All the best -Ade.

[molly00mayhem]

hi neopa

oh good! i value my db lifeline. you know, down home we girls call good female friends girlfriends. the guys have buds or use nicknames like slim or big john, or hardhat if they work in the mines, or dirt or sodbuster if theyre farmers, or slick or ace if theyre goofballs. them hillbillies are funnier than the 3 stooges. haha

yeah, izzies a good heart but struggles with a klunky method of keeping track of things, mostly word docs and an occasional spreadsheet. so its a little building on the church grounds and they have a food bank which provides free food like fruits, veggies, bread, and canned/boxed goods as well as free/low cost clothes and furniture and sheets and stuff like that. she would like to create a database to track organizations in the county which provide a range of similar and other resources to individuals with low incomes. she would then be able to steer people in these directions. i said izzie this is going to take work, are you giving up your bowling nite?!

so, i have our initial prototype db worked out, but im not sure how to present it. can i just upload the .accdb file? if so, how? writing all this stuff out would take a cntury and thered be more tpos than a stick keyboard. heehee

btw, thats what made the videos fun, good ole fashioned winging it! gotta be good to do that.

cheers!

[molly00mayhem]

hi philofwalton

yes. i have incorporated your and zmbds comments about addresses into a new db im putting together for a friend. are these whats called look up tables, just tables with a single often used value?

cheers!

[zmbd]

You'll have to put your prototype database in "zip file"
Once you have that, below the "Post your Reply" is the [Advanced] button which will take you to the full-editor.
In the full-editor is the manage-attachments feature that you will use to upload the file. Click the button, use the upper section to browse to your file, select, and then click on the [upload...] button.
>Most likely you should start a new thread for your database
>Don't forget to give a short explanation about what it is you're doing, what the database is doing, what you want it to do, and what (if anything) is wrong.
;-)
-Z

[molly00mayhem]

hi zmbd
sounds like a plan. here goes!

hab a good 'un!