424,045 Members | 1,243 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,045 IT Pros & Developers. It's quick & easy.

what should a novice learn first

P: 47
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.

Jul 16 '18 #1
Share this Question
Share on Google+
48 Replies

Expert Mod 2.5K+
P: 2,780
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!
Jul 16 '18 #2

Expert 100+
P: 1,382
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


Jul 16 '18 #3

Expert Mod 2.5K+
P: 2,780
As mentioned earlier, in a new standalone VBA module put the following and save it:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Public Function ErrorSub()
  5. 'Just useful and quick way to add Error Handling to Code
  7.     Debug.Print "On Error GoTo EH" & vbCrLf
  8.     Debug.Print "    Exit Sub"
  9.     Debug.Print "EH:"
  10.     Debug.Print "    MsgBox " & Chr(34) & _
  11.         "There was an error ....!" & Chr(34) & " & vbCrLf & vbCrLf & _"
  12.     Debug.Print "        " & Chr(34) & "Error: " & Chr(34) & _
  13.         " & Err.Number & vbCrLf & _"
  14.     Debug.Print "        " & Chr(34) & "Description: " & Chr(34) & _
  15.         " & Err.Description & vbCrLf & vbCrLf & _"
  16.     Debug.Print "        " & Chr(34) & _
  17.         "Please contact your Database Administrator." & _
  18.         Chr(34) & ", vbCritical, " & Chr(34) & "WARNING!" & Chr(34)
  19.     Debug.Print "    Exit Sub"
  21. 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!
Jul 16 '18 #4

Expert 100+
P: 1,382

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.

Expand|Select|Wrap|Line Numbers
  1. Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription As String, strCallingProc As String, _
  2.     Optional lngLineNo, Optional vParameters, Optional bShowUser As Boolean = True) As Boolean
  4.     ' Logs errors to external file "ErrorLog.Txt".
  5.     ' Arguments: lngErrNumber - value of Err.Number
  6.     ' strErrDescription - value of Err.Description
  7.     ' lngLineNo - Value if Erl is provided
  8.     ' strCallingProc - name of sub|function that generated the error.
  9.     ' vParameters - optional string: List of parameters to record.
  10.     ' bShowUser - optional boolean: If False, suppresses display.
  11.     ' Author: Allen Browne, allen@allenbrowne.com
  13.     Dim DbT_Message As String      ' String for display in MsgBox           ' ** Ignore **
  14.     Dim StrFile As String
  15.     Dim FileNumber            As Integer
  16.     Dim StrErr As String
  18.     On Error GoTo Err_LogError
  20.     Select Case lngErrNumber
  21.     Case 0, 2501, 3314, 2101, 2115
  22.         ' Do Nothing
  23.     Case Else
  24.         StrFile = CurrentProject.Path & "\ErrorLog.txt"
  25.         FileNumber = FreeFile                           'Get unused file number.
  26.         Open StrFile For Append As #FileNumber          'Create file name.
  28.         Print #FileNumber, "Database: " & Application.CurrentProject.Name & "    Calling Process: " & strCallingProc
  29.         StrErr = "Error No: " & lngErrNumber & "  " & strErrDescription
  31.         If Not IsMissing(lngLineNo) Then
  32.             If Nz(lngLineNo) > 0 Then
  33.                 StrErr = StrErr & "  On Line No: " & lngLineNo
  34.             End If
  35.         End If
  36.         Print #FileNumber, StrErr
  37.         Print #FileNumber, "Date Time: " & Now & "  User: "; Nz(fOSUserName)
  38.         If Not IsMissing(vParameters) Then
  39.             Print #FileNumber, "Parameters: " & vParameters
  40.         End If
  41.         Print #FileNumber,
  42.         Print #FileNumber,
  43.         LogError = True
  44.     End Select
  46.     Select Case lngErrNumber
  47.     Case 0
  48.         If TempVars!ConDebug = 15 Then Debug.Print strCallingProc & " called error 0."
  49.     Case 2501                ' Cancelled
  50.         'Do nothing.
  51.     Case 3314, 2101, 2115    ' Can't save.
  52.         If bShowUser Then
  53.             DbT_Message = "Record cannot be saved at this time." & vbCrLf & _
  54.                 "Complete the entry, or press <Esc> to undo."
  55.             Msgbox DbT_Message, vbExclamation, strCallingProc
  56.         End If
  57.     Case Else
  58.         If bShowUser Then
  59.             DbT_Message = "Error " & lngErrNumber & ": " & strErrDescription
  60.             If Not IsMissing(lngLineNo) Then
  61.                 If Nz(lngLineNo) > 0 Then
  62.                     DbT_Message = DbT_Message & "   Line No: " & lngLineNo
  63.                 End If
  64.             End If
  65.             Msgbox DbT_Message, vbExclamation, strCallingProc
  66.         End If
  67.     End Select
  69. Exit_LogError:
  70.     Close #FileNumber                               'Close file.
  71.     Exit Function
  73. Err_LogError:
  74.     DbT_Message = "An unexpected situation arose in your program." & vbCrLf & _
  75.         "Please write down the following details:" & vbCrLf & vbCrLf & _
  76.         "Calling Proc: " & strCallingProc & vbCrLf & _
  77.         "Error Number " & lngErrNumber & vbCrLf & strErrDescription & vbCrLf & vbCrLf & _
  78.         "Unable to record because Error " & Err.Number & vbCrLf & Err.Description
  79.     Msgbox DbT_Message, vbCritical, "LogError()"
  81.     Resume Exit_LogError
  83. End Function
It is called using something like
Expand|Select|Wrap|Line Numbers
  1. Function Translate(InputStr As String, ControlTypeID As Long, FieldNames As String, ColCount As Integer, _
  2.     OutLanguage As String, Optional InLanguage As String, Optional ShowIE As Boolean = 0) As String
  4.     On Error GoTo Translate_Err
  6.     ' Blah, Blah Blah
  8. FinalTranslate_Exit:
  9.     Exit Function
  11. Translate_Err:
  12.     If Err = 3022 Then                          ' Duplicate
  13.         Resume FinalTranslate_Exit
  14.     ElseIf Err = -2147467259 Then               ' Automation error Unspecified error (Internet Explorer running multiple times)
  15.         Resume FinalTranslate_Exit              ' Exit
  16.     ElseIf Err = 424 Then                       ' Object required
  17.         Resume FinalTranslate_Exit
  18.     Else
  19.         LogError Err, Err.Description, "Translate", Erl, Marker
  20.     End If
  22. End Function
This is part of the text file produced
Expand|Select|Wrap|Line Numbers
  1. Database: DB Phil.accdb    Calling Process: CheckTranslation
  2. Error No: 91  Object variable or With block variable not set
  3. Date Time: 30/04/2018 13:33:39  User: Philip
  4. Parameters: 0
  6. Database: DB Phil.accdb    Calling Process: CheckTranslation
  7. Error No: 91  Object variable or With block variable not set
  8. Date Time: 30/04/2018 13:33:40  User: Philip
  9. Parameters: 0
  11. The Language Pack for: French is not installed
  12. Date Time: 30/04/2018 14:41:39  User: Philip
  14. The Language Pack for: German is not installed
  15. Date Time: 30/04/2018 15:07:07  User: Philip
  17. The Language Pack for: Spanish is not installed
  18. Date Time: 30/04/2018 15:49:06  User: Philip
  20. Database: DB Phil.accdb    Calling Process: TranslateMsgBox
  21. Error No: 3021  No current record.
  22. Date Time: 30/04/2018 15:49:08  User: Philip
  23. Parameters: 0
  25. Database: Translate 17 May 2018.accdb    Calling Process: TranslateMsgBox
  26. Error No: 3021  No current record.
  27. Date Time: 17/05/2018 16:48:47  User: Philip
  28. Parameters: 0
It's another way, but as I said, for client's databases, it can help

Jul 16 '18 #5

P: 47
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!"
Jul 16 '18 #6

Expert Mod 2.5K+
P: 2,780
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!
Jul 16 '18 #7

P: 47
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 :)

Jul 16 '18 #8

Expert Mod 15k+
P: 31,164
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.
Jul 16 '18 #9

P: 47
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 :)

Jul 16 '18 #10

Expert Mod 2.5K+
P: 2,780
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"!
Jul 16 '18 #11

P: 47
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 :)

Jul 16 '18 #12

P: 47
hi twinnyfo

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

Jul 16 '18 #13

Expert Mod 5K+
P: 5,285
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
Jul 16 '18 #14

Expert Mod 15k+
P: 31,164
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.
Jul 17 '18 #15

P: 47
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!
Jul 17 '18 #16

P: 47
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

Jul 17 '18 #17

Expert Mod 2.5K+
P: 2,780
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Public Sub SearchTableDefs(strSearch As String)
  5. On Error GoTo EH
  6.     Dim tdf         As DAO.TableDef
  7.     Dim fld         As DAO.Field
  8.     Dim prp         As DAO.Property
  9.     Dim intColumns  As Integer
  10.     Dim intFields   As Integer
  11.     Dim strSource   As String
  12.     Dim strTDFList  As String
  13.     Dim strMsg      As String
  15.     strTDFList = vbNullString
  16.     For Each tdf In CurrentDb.TableDefs
  17.         intColumns = tdf.Fields.Count - 1
  18.         For intFields = 0 To intColumns
  19.             For Each prp In tdf.Fields(intFields).Properties
  20.                 If prp.Name = "RowSource" Then
  21.                     strSource = prp.Value
  22.                     If InStr(1, strSource, strSearch, vbTextCompare) > 0 Then
  23.                         strTDFList = strTDFList & Chr(9) & _
  24.                             tdf.Name & _
  25.                             " - " & tdf.Connect & _
  26.                             " - " & tdf.Fields(intFields).Name & _
  27.                             " - " & tdf.Fields(intFields).Type & _
  28.                             " - " & prp.Value & vbCrLf
  29.                     End If
  30.                 End If
  31.             Next prp
  32.         Next intFields
  33.     Next tdf
  34.     strMsg = "Text '" & strSearch & "' found in "
  36.     If Len(strTDFList) > 0 Then
  37.         strMsg = strMsg & "Table Defs:" & vbCrLf & strTDFList
  38.     Else
  39.         strMsg = strMsg & "No Table Defs"
  40.     End If
  42.     Debug.Print strMsg
  44.     Exit Sub
  45. EH:
  46.     Resume Next
  47. End Sub
  49. Public Sub SearchQueryDefs(strSearch As String)
  50. On Error GoTo EH
  51.     Dim qDf         As DAO.QueryDef
  52.     Dim strQDFList  As String
  53.     Dim strMsg      As String
  55.     strQDFList = vbNullString
  56.     For Each qDf In CurrentDb.QueryDefs
  57.         gstrSQL = qDf.sql
  58.         If InStr(1, gstrSQL, strSearch, vbTextCompare) > 0 Then
  59.             strQDFList = strQDFList & ";" & vbCrLf & Chr(9) & qDf.Name
  60.         End If
  61.     Next
  63.     strMsg = "Text '" & strSearch & "' found in "
  65.     If Len(strQDFList) > 0 Then
  66.         strMsg = strMsg & "Query Defs:" & vbCrLf & Mid(strQDFList, 4)
  67.     Else
  68.         strMsg = strMsg & "No Query Defs"
  69.     End If
  71.     Debug.Print strMsg
  73.     Exit Sub
  74. EH:
  75.     Resume Next
  76. End Sub
  78. Public Sub SearchForms(strSearch As String)
  79. On Error GoTo EH
  80.     Dim dbs     As Object
  81.     Dim oAO     As AccessObject
  82.     Dim frm     As Form
  83.     Dim ctrl    As Object
  85.     Set dbs = Application.CurrentProject
  87.     For Each oAO In dbs.AllForms
  88.         DoCmd.OpenForm oAO.Name, acDesign
  89.         Set frm = Forms(oAO.Name)
  90.         For Each ctrl In frm.Controls
  91.             Select Case ctrl.ControlType
  92.                 Case acTextBox, acComboBox, acListBox, acCheckBox
  93.                     If InStr(1, ctrl.ControlSource & "", strSearch) Then
  94.                         Debug.Print "Form: " & frm.Name & ": " & ctrl.Name
  95.                     End If
  96.             End Select
  97.         Next
  98.         DoCmd.Close acForm, oAO.Name, acSaveNo
  99.     Next
  101.     Set ctrl = Nothing
  102.     Set frm = Nothing
  103.     Set oAO = Nothing
  104.     Set dbs = Nothing
  106.     Exit Sub
  107. EH:
  108.     Resume Next
  109. End Sub
  111. Public Sub SearchReports(strSearch As String)
  112. On Error GoTo EH
  113.     Dim dbs     As Object
  114.     Dim oAO     As AccessObject
  115.     Dim rpt     As Report
  116.     Dim ctrl    As Object
  118.     Set dbs = Application.CurrentProject
  120.     For Each oAO In dbs.AllReports
  121.         DoCmd.OpenReport oAO.Name, acDesign
  122.         Set rpt = Reports(oAO.Name)
  123.         For Each ctrl In rpt.Controls
  124.             Select Case ctrl.ControlType
  125.                 Case acTextBox, acComboBox, acCheckBox
  126.                     If InStr(1, ctrl.ControlSource & "", strSearch) Then
  127.                         Debug.Print "Report:" & rpt.Name & ": " & ctrl.Name
  128.                     End If
  129.             End Select
  130.         Next
  131.         DoCmd.Close acReport, oAO.Name, acSaveNo
  132.     Next
  134.     Set ctrl = Nothing
  135.     Set rpt = Nothing
  136.     Set oAO = Nothing
  137.     Set dbs = Nothing
  139.     Exit Sub
  140. EH:
  141.     Resume Next
  142. End Sub
  144. Public Sub SearchModules(ByVal strSearch As String)
  145. On Error GoTo EH
  146.     Dim objComp     As Object
  147.     Dim strMsg      As String
  148.     Dim strModList  As String
  150.     strModList = vbNullString
  152.     For Each objComp In Application.VBE.ActiveVBProject.VBComponents
  153.         If objComp.CodeModule.Find(strSearch, 1, 1, -1, -1) = True Then
  154.             strModList = strModList & ";" & vbCrLf & Chr(9) & objComp.Name
  155.         End If
  156.     Next objComp
  158.     strMsg = "Text '" & strSearch & "' found in "
  159.     If Len(strModList) > 0 Then
  160.         strMsg = strMsg & "modules:" & vbCrLf & Mid(strModList, 4)
  161.     Else
  162.         strMsg = strMsg & "no modules"
  163.     End If
  165.     Debug.Print strMsg
  167.     Exit Sub
  168. EH:
  169.     Resume Next
  170. End Sub
  172. Public Sub SearchProject(strSearchWord As String)
  173. On Error GoTo EH
  175.      SearchTableDefs strSearchWord
  176.      SearchQueryDefs strSearchWord
  177.      SearchForms strSearchWord
  178.      SearchReports strSearchWord
  179.      SearchModules strSearchWord
  181.     Exit Sub
  182. EH:
  183.     Resume Next
  184. End Sub
Hope this hepps you (and others).
Jul 18 '18 #18

Expert Mod 15k+
P: 31,164
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.
Jul 18 '18 #19

Expert Mod 2.5K+
P: 2,780

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.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....
Jul 18 '18 #20

Expert Mod 15k+
P: 31,164
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.
Jul 18 '18 #21

P: 47
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

Jul 19 '18 #22

P: 47
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!
Jul 19 '18 #23

P: 47
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 :)

Jul 19 '18 #24

Expert Mod 15k+
P: 31,164
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.
Jul 19 '18 #25

Expert 100+
P: 1,382

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

Jul 19 '18 #26

Expert Mod 2.5K+
P: 2,780
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.
Jul 19 '18 #27

Expert 100+
P: 1,382
Always my pleasure

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


Jul 19 '18 #28

Expert Mod 2.5K+
P: 2,780

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.
Jul 19 '18 #29

Expert 100+
P: 1,382
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.

Jul 19 '18 #30

P: 47
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!

Jul 20 '18 #31

P: 47
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 :)

Jul 20 '18 #32

P: 47
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 :)

Jul 20 '18 #33

Expert Mod 2.5K+
P: 2,780

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!
Jul 20 '18 #34

Expert Mod 15k+
P: 31,164
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?
Jul 20 '18 #35

P: 47
hi twinnyfo

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

hab a good 'un!
Jul 20 '18 #36

P: 47
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

Jul 20 '18 #37

Expert Mod 5K+
P: 5,285
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
Jul 20 '18 #38

Expert 100+
P: 1,382
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.

Jul 20 '18 #39

Expert Mod 15k+
P: 31,164
Hi Molly.

You realise I was just talking about booze yeah?
Jul 21 '18 #40

P: 47
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

Jul 21 '18 #41

P: 47
hi neopa

huh? oh, hahahah yeah, what else!?

Jul 21 '18 #42

P: 47
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.

Jul 21 '18 #43

P: 47
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!

Jul 22 '18 #44

Expert Mod 15k+
P: 31,164
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.
Jul 22 '18 #45

P: 47
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.

Jul 23 '18 #46

P: 47
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?

Jul 23 '18 #47

Expert Mod 5K+
P: 5,285
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.
Jul 23 '18 #48

P: 47
hi zmbd
sounds like a plan. here goes!

hab a good 'un!
Jul 23 '18 #49

Post your reply

Sign in to post your reply or Sign up for a free account.