what should a novice learn first

**twinnyfo** · Jul 16 '18, 09:02 AM

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 “txtFieldN ame”. 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:

I need the value of the first text box
I need to check that this is a valid value
I need to manipulate that value
I need to make sure the result is valid
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** · Jul 16 '18, 11:24 AM

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/answers/970942-determining-if-attachment-field-null

Phil

**twinnyfo** · Jul 16 '18, 11:34 AM

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

Code:

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** · Jul 16 '18, 12:13 PM

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

Code:

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, allen@allenbrowne.com

    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

Code:

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

Code:

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** · Jul 16 '18, 04:30 PM

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** · Jul 16 '18, 04:52 PM

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 Jingleheimersch midt". 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 Jingleheimersch midt". It will then find the record associated with "John Jacob Jingleheimersch midt". Now, you want to count how many classes "John Jacob Jingleheimersch midt" has taken. So you query the table tblClasses for all classes taken by "John Jacob Jingleheimersch midt". After a few queries, your fingers are worn to the bone from typing "John Jacob Jingleheimersch midt". 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 Jingleheimersch midt", based on the PK. Now, you want to count how many classes "John Jacob Jingleheimersch midt" 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 Jingleheimersch midt" 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** · Jul 16 '18, 05:07 PM

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** · Jul 16 '18, 05:39 PM

Originally posted by Molly

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** · Jul 16 '18, 05:40 PM

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** · Jul 16 '18, 06:07 PM

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** · Jul 16 '18, 06:27 PM

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** · Jul 16 '18, 06:38 PM

hi twinnyfo

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

cheers!

**zmbd** · Jul 16 '18, 07:33 PM

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** · Jul 17 '18, 12:23 AM

Originally posted by Molly

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.

what should a novice learn first

what should a novice learn first

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment