40. (Advanced Programming In Access 2013) Conclusion

[@rohitharidas91]

I really appreciate the time and effort you put into these... Kudos prof. Steve, I owe you alot!

[@ProgrammingMadeEZ]

+Rohit haridas Thank you very much for the wonderfully kind words.

[@dajo5779]

Good evening Steve, Thank you so much for the time you have taken to develop an incredible video tutorial series. God bless

[@charliesk6239]

Hi Steve. Just finished your Advanced Access Series. Your thoroughness and amount of coverage is in a league of its own. I have taken other paid Access courses but none even come close to yours. Please accept my gratitude and many thanks for taking the time to create courses and for sharing your wisdom with the Dev community. As I have said before, you are an inspiration for thousands of aspiring dev's. Have a wonderful day and God bless!

[@bumpersmith]

I feel all of your videos have been nothing less than superb and I will be waiting anxiously for your .net series. Thank you for all, you are very generous with your knowledge.

[@kasem1977]

Dear Steve, I watch all of Access programming that is extra ordinary to new learner such as me in my point of view. Now this series I reached your last Video, If it is not last video Please want a continuation from you....It is really helpful to us.

[@BartekKan]

Good job man ;-) Thanks for the professional tutorial!

[@greghalliday4226]

Thanks Steve, outstanding effort.

[@ProgrammingMadeEZ]

+Greg Halliday Thank you Greg.

[@cephasdoni1755]

Hi Steve, thanks so much for these helpful video series. Great work

[@ProgrammingMadeEZ]

Thank you!

[@janezklun]

Thank you for all those nice, useful videos you created for us. I hope that you would create some new videos in the How to section, I will definitely ask make some questions. On the other hand, I am looking forward to see .net lectures. Thank you again Janez

[@kokobrussom2610]

Hi Steve , I really appreciate you and I would like to thank you for the videos you shared .

[@AliTwaij]

Many thanks Steve, excellent work

[@RicardoBarreraArellano]

You are right. But, I also use your Access videos in order to apply that theory in my VBA applications for Excel, Word and so on. oAuth2 is mandatory in order to retrieve data from the Calendar API for example

[@ProgrammingMadeEZ]

+Ricardo Barrera Yeah, the VBA concepts are the same, just some of the available libraries, methods, and properties of the application are different. I'll see what I can do about getting something together, but it would take a bit of time to even work out the details of doing this.

[@RicardoBarreraArellano]

+Programming I would really appreciate it. Thanks again

[@mohamediflah3591]

Many Thanks Steve, Excelent videos, I really learned a lot.

[@hr.sanders]

Great! keep on the good job. your videos are really great.

[@m3xpl4y]

You Sir Mr. Bishop, your videos are where i learned how to you use Access, please do not stop making Access Videos/Tutorials. I have a Access problem, i need to send a mail to one or more Contacts, the Subject and a part of the Body is the same but the other Body information i need to send, is stored in 3 different tables (to gather all needed information i created a query). How do i show for each Contact on its own the needed information in the email separately? from he query without putting it as attachment, thank you

[@divanemy]

thank you so much for your help

[@medoo1982]

thank you very much for all this nice and helpful videos. and please make something about VB.Net if you can. your videos are the best on all KZbin.... thank you very much.

[@ProgrammingMadeEZ]

+Ahmed Hadi For now I'm focusing on C# basics since nearly all .NET development is focused on C#. VB.NET is ok, but not as versatile as C#.

[@omarba6972]

Hello professor, I would like to know how to share the database in SQL SERVER in a local area network (LAN) between 3 computers? 2) is it necessary to install SQL SERVER on client workstations? 3) how to access the SERVER station remotely (WAN)?

[@ali252982]

Dear Steve It was a wonderful time I spent learning from your amazing videos. As you are now moving to .net is it possible for us to convert all what we have done in access to .net because thanks to you my company has a considerable. accdb app and I'd like to convert it to web application is it possible?

[@ProgrammingMadeEZ]

+Ali Shrinah No, there is no direct conversion route. You have to write the .NET app from scratch.

[@gilad_paz]

Thanks for being such a mench and setting an example to all of us. I am sure it will come back to you with much reward in the future. I have a question with regard to your unique ability to compare platforms. What are the advantages/disadvantages of Access+sqlServer as compared to dot net? I know dot net is more geared toward wan and the former toward lan. But Access+sqlSrvr is very powerful and comparatively fast to implement right? And Access is also moving in the wan direction. So if lan is all you need isn't Access preferable? You can even have an automatic upgrade distribution installed so no need to install your app more then once on each workstation. Dot net also requires an initial install in some cases so not much advantage there.

[@ProgrammingMadeEZ]

+MyChannel Good question. Access is certainly a viable option for most of the simple things you would want to do in a small to medium sized business. However, there are several factors that play a part as to why .NET might be a better alternative. Access can only run on a desktop that has Access installed on it. When applications get more complex, the Run-Time doesn't always work and so the full application has to be bought and installed to run on client front end. This can become cost prohibitive to many companies. Additionally, if you did want to make a Web App, you cannot use any VBA code. You can only use the limited Macros. Then there's the forms. They only implement the most basic types of control objects like buttons, text boxes, combo-boxes, and listboxes. There is no menu system like you can get with MS Forms. Additionally, there is no real form factor customization. So a form that looks good on a 1600x900 screen could look awful on a 1024x786. The VBA code is VERY limited. It has no capabilities of being Object Oriented. There is no polymorphism, no inheritance, no abstraction, and only limited encapsulation. The fact that you can't create class initializers with parameters is akin to giving someone a Lamborghini but throwing a Prius engine in it. Sure it'll work, but it doesn't perform the way it should. Learning C# or VB.NET allows you to make not only Windows Forms applications but WPF, Silverlight, and ASP.NET applications as well. So the platform you present your application on can be much more diverse. The .NET languages have an incredible number of ways to make the construction of an application easier. Lambda expressions allow you to pass a function as a parameter! Think of that! There are also things called anonymous classes which allow you to create a class on the fly without any pre-determined class definition. There's also method overloading, so if you want a method to have the same name but different sets of parameters that it accepts, you can do that. The web aspect is probably the biggest difference though. With ASP.NET you can build websites that will work on any device that operates a browser. The same page you build for your desktop can run on your phone or IPad. No special OS or software required. Most people have a browser on their device right out of the box.

[@bobe48000]

thanks Steve

[@jgbaileyau]

Hi Steve, Thanks for these videos, I have learned a lot. Would you be interested in doing a video on a "Bill of Materials" application? Ie Product, which is made up of Parts, and also other Products, in a hierarchical model. ( and possibly an into to the TreeView control )

[@ProgrammingMadeEZ]

+Jamie Bailey Whew... That's not a bad idea. Bill of Matierials or Invoice generation is basically what you're talking about and is a fundamental job that Access can do. I'm just not sure if I have the time at the moment. I can put it on my list of ideas for a video though. As for a TreeView, I really didn't get into ActiveX controls very much so I'm not sure how much the viewers would be able to follow along.

[@jgbaileyau]

+Programming no worries. The area I was interested in was how to do a recursive query to get all of the "child" parts for a BOM. i know it can be done with CTE in SQL Server, but wasnt sure if there is a simple way to do it in Access?

[@ProgrammingMadeEZ]

Well this is a fairly straightforward use of parent form/subform. One table typically contains the header information for an order, while another table contains the line items of the order. Then the parent form contains the header table data, and the subform contains the line items.

[@dglwala]

Hi Steve, thank you so much for the Access video series which is helping me a lot to get a good understanding. Would you please try to resolve this table relationship? I wanted to create a DB with Users & Group membership. User can be a member of Group Group can contain Users and Group (nested) Example 1: Active directory Users/Groups relationship Example 2: Email (LDAP) users/distribution lists Hope to hear from you soon. Thanks in advance.

[@ProgrammingMadeEZ]

+Abdul Rasheed Why not do a group relationship table that does two foreign keys to the same group table?

[@dglwala]

+Programming Thank you for the prompt reply. I am stuck with visualizing the concept. A sample would be helpful.

[@khalidsial3587]

how to put balance broad down function in accounting ledgers in my Database?

[@ProgrammingMadeEZ]

+khalid Sial I don't understand.

[@scottduan9946]

thx for these videos.btw,Can u tell me the name of your screen recorder name.thanks in advance

[@ProgrammingMadeEZ]

I don't recall what I was using at the time but I use Camtasia now.

[@scottduan9946]

tkx.I just want to know how to show that,when u click the mouse.the cursor shape of the mouse is show.that is nice. I also use CS now,Thanks again

[@schalld1]

do you have a 2021 version

[@ProgrammingMadeEZ]

No, afraid not.

[@dajo5779]

I wanted to bring up a question I had asked in Lesson 17 concerning the Instr(), Len(), Left(), Mid() and Right() functions within VBA. Here is the code I had used: txtData = "SELECT co.dmyDate, co.[Order ID], co.[Product ID], co.TruDate, InStr([co.dmyDate],""1"") AS SlashPos, " & _ "InStrRev([dmyDate],"" / "") AS SlashPos2, InStr([SlashPos]+1,[dmyDate],""/"") AS Date2Pos, Left([dmyDate],[SlashPos]-1) AS Dday, " & _ "IIf([SlashPos][SlashPos2],Mid([dmyDate],[SlashPos]+1,[SlashPos2]-[SlashPos]-1)) AS Mmonth, " & _ "Right([dmyDate],Len([dmyDate])-[SlashPos2]) AS Yyear, DateSerial([Yyear],[Mmonth],[Dday]) AS TruDate1, " & _ "Year(CDate([dmyDate])) AS YYYear INTO CustomerOrdersDataDumpTempQT " & _ "FROM CustomerOrdersDataDumpT AS co " & _ "WHERE (((co.TruDate) Is Null) AND ((co.YYYear)=(SELECT Min(Yyear) AS Expr1 " & _ "FROM DateRangeQ " & _ "WHERE ((DateRangeQ.[DateRangeCompleted])=False) AND DMin(""DateRangeID"",""DateRangeQ""))));" The Instr() function produces a zero. The purpose behind the code is when importing an Excel spreadsheet, the date is coming in as text with the format as dd/mm/yyyy. Access is interpreting the date as mm/dd/yyyy , therefore I am trying to convert the Excel data date to the correct and then I will able to manipulate the correct. Keep in mind I tried the CDATE function from the beginning, but Access is sorting the data as text, and not as a Date. If I was to incorporate the conversion in the Query Designer, it works perfectly, but I cannot get it to work within the VBA, because the Instr() is producing a zero, therefore the rest of the code is not producing the correct results. Can you please assist me with this issue? Thank you for your assistance David

[@ProgrammingMadeEZ]

+da jo I suggest you look into using the format() function. It will make your life a lot easier and you won't have to do string manipulation. Based on your language and location Access automatically knows your local date format. If you want to then export that date out in US then use the format() function to specify the output.

[@dajo5779]

Thank you for the suggestion. For some reason when I tried it before I was not able to sort the converted formatted dates correctly. I used the following to help solve my issue; TruDate: CDate(Format(OldDate,"mm/dd/yyyy"))

[@RicardoBarreraArellano]

Thank you very very very much for doing such a task. But, please before you move on to .NET explain to us how to get an oAuth2.0 for signing to Google APIs and obtain a token. Please!!!

[@ProgrammingMadeEZ]

+Ricardo Barrera Ahhh yes. Tokens. Well you have to capture the token just like I showed in the JSON with WebAPI, but you have to use the Google URL's to obtain it. It should be in their documentation. Then they will require a header of some sorts with that token. I may do this as a how to video. Honestly, it's just so infrequent that someone would use oAuth in an Access database.

[@yvettethompson]

Thank you so much! I am getting hung up on the API-Access import - is there any way I can purchase your consulting services? I have tried to find you on Facebook.com/AccessDatabases but that didn't work.

[@ProgrammingMadeEZ]

+Yvette Thompson I work for a company that does consulting work. We're trying to set up a process for some of my viewers to get help since it's a common request.

[@yvettethompson]

can I submit a screenshot about your ReadJson() lesson? Please Please Please.

[@ahmedsabry1265]

I am creating a database for a school and the question is , Can I use whatsapp to send reports using phone numbers?

[@NuJust8]

whatsapp doesn't have an API available for you to connect to so I don't think you can unfortunately

[@santiagoangos4045]

Thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[@sissokom.prodev9979]

Hollo! Master Steve …. Saying Thanks will not qualify exactly the pharaonic job you have done! I have a very good knowledge in MS access, and I know working on some project, will take several months, and so if you need any extra hand on these projects, please Steve don’t hesitate to contact me… I will stop any project on my side and work on yours. And if possible I would like to translate all theses video in to French to extend more your kindness….

[@ProgrammingMadeEZ]

+Sissoko Malle That's a nice offer Sissoko. I think for now it would be best to just keep the videos as they are though.

[@SAMYTAHER]

√√√