473,847 Members | 1,498 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

what should a novice learn first

47 New Member
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
48 2448
3,653 Recognized Expert Moderator Specialist
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:
  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
1,430 Recognized Expert Top Contributor
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
3,653 Recognized Expert Moderator Specialist
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
1,430 Recognized Expert Top Contributor

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
47 New Member
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
3,653 Recognized Expert Moderator Specialist
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!
Jul 16 '18 #7
47 New Member
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
32,584 Recognized Expert Moderator MVP
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
47 New Member
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

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

Similar topics

by: aaronfude | last post by:
Hi, I'm an experienced C++ programmer (mostly in unix and other abstract environments such as mex). I'm now getting into Windows programming and finding out that there are many acronyms and technologies. So I'm trying to figure out what to start learning. It seems that .Net will replace MFC so might as well not invest time in MFC.
by: dydx13 | last post by:
Hello, I'm currently attending a university majoring in Computer Science. I took C and C++ at a community college and got an A in both classes. That was three years ago. I have not programmed or anything like that in three years. I want to get back into the programming scene so I have a question: Which should I relearn first, C or C++? I have forgotten a lot about both languages so please tell me which i should relearn first! ...
by: dydx13 | last post by:
Which programming language should I learn first: C or C++? Thanks! -- --------------------------------- --- -- - Posted with NewsLeecher v3.7 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- -
by: LayneMitch via WebmasterKB.com | last post by:
Hello. I've already learned HTML/CSS...I'm completing the learning process for Javascript and the next language is going to be Actionscript. Now, I've been reading the opinions about different Actionscript books and some of them say that if you are knew to programming, then you may want to read a book about programming basics first. Is this the route that I need to go or should learning Javascript be the facilitator for learning...
by: LayneMitch via WebmasterKB.com | last post by:
Hello. I've already learned HTML/CSS...I'm completing the learning process for Javascript and the next language is going to be Actionscript. Now, I've been reading the opinions about different Actionscript books and some of them say that if you are knew to programming, then you may want to read a book about programming basics first. Is this the route that I need to go or should learning Javascript be the facilitator for learning...
by: =?GB2312?B?0rvK18qr?= | last post by:
Hi all, I read this interesting post comparing Boost.Python with Pyd: http://pyd.dsource.org/vsboost.html What's your opinion about it? What's your first choice when you have write a C/C++ module for Python?
by: sifounas | last post by:
Hi, i am a php programmer who has no previous programming experience neither in java nor in c#(sharp). My goal is to learn both languages java and c#, so i am looking for an experienced programmer who has in depth knowledge of both languages to suggest me which one to start first in order to make my learning process easier and to help me grasp fast on the second language once i have finished learning the first. (e.g some people find it easier...
by: stirredo | last post by:
I have just "finished" learning C. By finished, I mean I have learned all the basics in plain vanilla C (command prompt style). I want to get into windows developement so that i can create something useful. What should be my next step? Should i be learning a object oriented language like c++ and then learn .net or MFC? Or just start learning win32 api?
by: Rina Patel | last post by:
I m a student of web designing. But due to some reason i had left my course so now i m learning my remaining softwares by my own. I have learn HTML, Dream Weaver, Php (half). Now i m learning through internet my remaining softwares. My remaining softwares are XML,Asp.Net,Cold Fusion,Java scripting. But now i m confused which should i learn first so that i can easily learn.
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.