Quick Queries #22
By Richard Rost
11 months ago
Performance over VPN, Fixing Grainy Logo, More!
In this Microsoft Access tutorial, I will guide you through practical solutions to common issues, including setting focus on fields, handling slow performance over VPNs, and adjusting date formats. We'll also explore image transparency challenges in reports and maximizing database efficiency with calculated fields. Dive into these essential tips for enhancing your Access experience.
In Today's Video...
- Access database performance over VPN
- File-based vs. client-server databases
- Access front-end with SQL Server backend
- Slow Access database with file-sharing services
- Fixing grainy logo in Access reports
- Calculating expiration dates with DateAdd
- Windows ISO date format vs. Access formats
- Access transparency issues with GIF/PNG images
- Installing Access on Windows S mode
- Using Access over a local vs. wireless network
- Record locking in Access databases
- Locking fields in Access with VBA
- Get next-to-last record using DMax
- Access Developer Network for custom solutions
- Why fields don't have spaces (naming conventions)
- Tracking changes with a change log table
- Automatically deleting old log records
- Date formatting: extracting year from datetime
- Adding combo boxes in Access search forms
- Breadth-first vs. depth-first learning
- Handling prerequisites in tutorial videos
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, SetFocus, Column Heads, Linux, Invert Boolean, VPN Access, file-based database, Access frontend, SQL Server backend, DateAdd function, ISO date format, SharePoint Access, field locking VBA, DMax function, Access combobox parameters, date-time conversion, combo box filtering, auto number limitation.
Transcript
Today is Quick Queries episode number 22, where I answer your questions. You send me an email or post in the forums or on my YouTube channel, and other good questions, but maybe they don't need a whole video of themselves, so we throw them together in this bundled joy called Quick Queries.
First up, a little program note: I apologize for the distorted stuff in the last episode. I've been trying some new video software, and some of this stuff came out looking like this when it should have been looking like this. Usually, I take screenshots and put them on slides like this in PowerPoint, but last time I tried just putting the screenshot in, and it got stretched, and it was weird. I'm sorry, so I won't do that again.
First up, a question from the forums. One of my students wrote, he said he's got an Access desktop application that ran very well before in Booker with Tom, Florida. Since the back-end file has been relocated to Orlando and the front end is in Boca Raton still, he said it's running very slow. So I'm assuming you're running it over a VPN connecting over the internet. If that's the case, yes, it's going to run slow. There's just no way around it unless you've got a super, super fast VPN connection. It's not going to work great. You've got to remember how Access works. Access is a file-based database system. It's not really designed to work well over large distances like that.
If you run a query, let's say you're looking for just a customer from Florida, and you've got 100,000 customers in your customer table. Well, Access will have to pull down all 100,000 records and then go through all those records and just find the ones from Florida. As opposed to a database server like SQL Server or any other database server, you send the request up, and it will then go through all those 100,000 customers on the remote machine, find those 10 customers from Florida, and just send you those. That works much, much better - that's a true client-server setup.
You can also use Access as your front end, and I strongly recommend it. Access is a great front end. The database server on the backend handles all the work like that, and it doesn't have to send so much traffic over the line. If you want to learn more about this, I've got a whole picture on Access online. This will tell you about all the different ways you can run with SharePoint, use SQL Server, remote desktop, Access database cloud. There's lots of different options. There's a link to that page. I'll put a link down below you can click on, and I hope that helps to answer your question. Access by itself over a VPN, I don't recommend it. And Access over a file sharing service like Dropbox or Google Drive? No, do not, do not, do not do that. No, do not put it in a file-sharing drive.
Rosa is asking about putting a logo on a printable invoice report, but the image shows terribly. Yeah, that can sometimes happen. I've got a whole separate video that talks about that. Go watch this video. I'll put a link down below. Yeah, sometimes when you put a high-quality image in a report, it looks all grainy and washed out. There's a fix to get it to look nice and pretty, and this video will talk about that.
Deanna says she's pulling her hair out trying to figure out a formula for determining a contract expiration date. She's got a start date, and she wants to add a certain number of days. Now, there's a couple of different ways you can do it. You can use the DateAdd function, and I've got a video for that. Here you go. DateAdd is good if you want to add whole years, months, quarters. Or if it's just days, if it's simple days like 30 days, seven days, you can just use date math. In Access, a date value is a number, and one day equals one. So if you want to add three days, just take your date value and add three. If you want to add six hours, that's a quarter of a day (6/24), so you could add 0.25. All right, so that's basic date math. Go watch both of these videos, and that should answer your question.
All right, Keith said that he changed his computer's system - Windows system - date format to the ISO date format, like I show in this video. I'll give you the link in a minute. But his Access database is still not showing this format. Why is that? Well, even if you set your Windows system default format to the ISO date, which is year-month-day, which I strongly recommend, here's the video. I'll put a link to this down below. Basically year-month-day, which is a universal format. My goal is to get everybody in the world using this so dates are not ambiguous anymore.
You can set your Windows system setting so that that's the default for all of the applications on your computer for Word, for Excel, for Access. But if you still have custom formats inside of Access, they will override the default. Let me show you. All right. So here in this database, my computer is set to ISO dates, of course. So there is a date field. But if I have a format in here, a custom format for this field, either in the form or in the query or in a table underneath it, if I go into format here, now I would suggest sticking to short date. That'll give you the default system short date. If you put something custom in here, like MMDDYYYY, like that, that will overwrite whatever the default system is. If I come back in here now, there you go.
This is a pain. And I didn't really notice that it was a pain when I just taught locally here in the United States. But as soon as I started doing classes around the world and YouTube and stuff, and people from Europe, they have a different format. It's just a pain. So that's why I strongly suggest if you're going to put custom formats and stuff, just stick to short date, long date, those kinds of things. Be aware that any formats in Access will override the system formats. OK?
Tecchi Yandin says, when I add an image with a transparent background, like a GIF or a PNG or something, it displays correctly in a report view, but print view and actual print, when you print it out, it's not transparent. Do I have a fix for this? No, unfortunately, I do not, but I can confirm that you're not crazy. Transparent GIFs do work in forms, but they don't work in reports. Why? I don't know. It's just a limitation of Access. Maybe this is something the Access team can address in a future version. Hint, hint. Sammy added to the list. But yeah, I tried it myself recently, and no, transparent GIFs just don't display right in reports. Sorry. Not my fault. I don't build Access. I just teach it.
ZVIG wants to know if he can install Access on Windows S mode. Well, the first thing I always tell people is, if you want to know, can Access do this or can you do that? Go ahead and try it. Try it and see. But no, Windows S mode is like their... it's kind of like safe mode. I don't want to call it safe mode because it's not safe mode. It's basically, it's like their version, where you can only run apps out of the app store. It's like if you had an Android, you can only run apps from the Google Play Store, unless you sideload. But no, Microsoft Access is not supported in S mode. So yeah, that's S mode.
Tertiary Spotlight Media. I get this question all the time, as you can see we have one earlier too. I split my database, and I learned I can share it on SharePoint or a local area network before multiple users can have access to it at the same time. Smoothly, if you're on a local area network, you're in one physical location, and you've got wires run to your computers. Can I do a hub or a switch? A local area LAN. Yeah, that's what Access is designed for. OK.
Can you run it over a wireless network? You can, but I don't recommend it. I have, but unless your wireless connection is spot-on perfect, you could run into problems because if that disconnects at all in the middle of a file read/write, then you're looking at a corrupted database. So I don't recommend using it over a wireless network. As far as SharePoint goes, yeah, sure. You can use SharePoint, you can use SQL Server, you can use all kinds of different things. Check that video that I mentioned earlier. You guys will be surprised how many times I get similar questions like this. That's why I put those other videos together.
Fiona is asking about locking a field. Now, in this specific video, I teach you how to lock a field without... I'm not really talking about Microsoft's Access's built-in record locking. All right, that's something different. That is set at the database level. All right, if you go into file and then options and then go into client settings and then come down to... or is it at the advanced here? All right, default record locking. Now, by default, it's set to no locks, which is if you're using Access by yourself in a single-user environment, no locks is fine, your database will run faster.
You can switch to all records or edited records. All records mean that if you're editing a record in that table, all of the records in that table are locked. Edited record means just that record is locked. Sometimes a record around it might be locked. It's based on page size and all kinds of stuff. Basically, usually just the record that you're working on gets locked. Now, this is set in the database that you're working on.
Okay. This video here that Fiona is talking about, this is something completely different. This video teaches you how you can lock something, like a credit limit field, once the user puts some data in there, or you could lock an order once it's marked paid. That's completely different from Access record locking. Locking in Access is to prevent two people from editing the same record at the same time. This is locking a field like you don't want them to be able to change this anymore. Once you enter in, let's say, a birthday, only a manager can update it. That's what this talks about. This is set in the front end. So yes, you would have to put the VBA code that I teach you in this video in every form where this field could possibly be edited. Otherwise, I could just go to a different form and change it there. So that's something you have to control.
Oh, wait, someone's beaming in. Hold on, let's see who's going to go. We've got... that's another Tribble. Damn it. Dork Fish. I'm guessing that's dork fish. Dork, Dork F1, SH. I've shown how to get the next to last date record. The second last, the second highest value. Is there a way to modify the max function instead of dmax to get the next to last date as well? I don't think so. DMax allows you to specify criteria. You can say, give me the largest credit limit in the customer table, and then with criteria, you could say, if you want the second highest one, you could say, give me the largest credit limit in the table less than the one I just looked up. So it'll find the largest one less than the previous one. And you can keep doing that. You can get the first, second, or whatever.
Max is a form footer function. It goes in the form footer. And I don't think you can use that to find the second. You'd have to use dmax. I mean, you could just put dmax in the footer as well. You wouldn't have to use just max. But I don't think it's possible. Sorry.
Here's something I get asked all the time too. I use... whatever that is... want to build a bill of materials check, purchase orders, receipts, inventory, all this stuff. Can you build one for me? I no longer do custom software development. I did for years and years and years. That's how I got all the knowledge that I have now - pulling my hair out, writing software for other people. So I don't do that anymore.
But I do cover most of these topics in my full developer series. I'm up to developer 46. We cover most of that: purchase orders, inventory, receipts, invoicing, all that stuff. Or, if you want someone else to build it for you, I do have a page called the Access Developer Network. Just go to my website, drop this little box down here, and pick Access Developer Network. I'll put a link to it down below. And on this page, you can find people that can help you with your project. Here are some of my Access veterans. These are students of mine, advanced developers who do consulting work. I've got some sponsors here. They're great at what they do. All right, and there's other Access consultants. These are all people who are available and willing to help you with your project. So check this out. I'll put a link to it down below.
All right, I get comments like this every now and then, and I just want to address it. This person is upset because I copied a combo box from another form instead of building it from scratch. Isn't this supposed to be a tutorial? Well, a lot of my tutorials and my courses and my videos, at the beginning of them, I always say, here are some prerequisites. Here are some other videos that you should have watched first before watching this video. And that's because I can't take the time to go over everything in every video. Otherwise, most of my videos would be three hours long.
So if I say, hey, this video is going to require you to understand how to make combo boxes. Here's a video you should watch first. Go watch that video first, then come back. And I think in most of my TechHelp videos, I say this. Almost everything has prerequisites except for the very beginner classes. So yes, they're tutorials, but they have different skill levels. For example, this one that I just released yesterday, it's a developer-level video, which means you need to know some VBA. And if you don't know VBA, I tell you to go watch this. This video first, this one's about 20 minutes long. And I'm not going to cover all of this stuff in every other video that requires VBA. Does it make sense? Yakapeesh? Are you picking up what I'm putting down, Kelly? Right? Yeah, it's a tutorial, but I cover combo boxes and that stuff in the previous video that you should have watched before complaining about this one. OK? OK. Shop smart. Shop S-Mart. You got that?
The Luca wants to know if it's possible to only take a date value, a date-time value, and only have the year left. In this video, the Remove Time video, I teach you how to take a date-time that's got a time on it and chop the time off, so you're only left with the date part. So can you wiggle that down to just a year? Yeah, sure.Use the Year function. There's a video for you. You can get the year, the month, the day, any part of a date-time value. You want the second, the minute, the hour. OK? Just remember, if you pull the year out with the Year function, you're no longer working with a date value. Now you have a number. All right? So make sure it's a number of type long integer, and it'll give you the year. Brian, one of my members, has two questions. He wants to know if you can add a search function that utilizes a combo box. He's asking this from my Search Form 2.0, which utilizes just text fields to filter the records in a continuous form. Is that possible? Yes, of course, it's possible. I'll talk about it in a second.
And the other question is, can you have more than one field with an auto number? I'll answer your second question first. No, you may only have one auto number in a table. That's just the limitation of Access. You can't do it. I can't think of a situation where you need to have more than one, right? The auto number should be used for the primary key field for making relationships, that's it. If you need some kind of another counter or a different type of variable, make your own. I've got several other videos on the topic. For example, you want sequential numbering for your orders so each customer has sequential order numbers. OK? You don't need auto numbers for that.
Now as far as your first question goes, you could just make combo boxes to replace the text boxes in that search form filter. It'll work the same way. Just make value list combo boxes or even relational combo boxes that have text in them. Then the after update event will still fire and just check the value. You might have to use column one, but it'll still work. If you need more help than this, let me know. Post something in the forums on my website, and I'll be happy to give you some more information. You can also use the wizard to make a fine direct card combo box. This isn't as good, but if your needs are simple, this might work for you too. So check this one out. I'll put links to this down below.
And then one-on-one brings up the concept of breadth-first versus depth-first thinking. This is the method that I've always used for my lessons - breadth-first. Depth-first is where you would drill down on every topic. Like Microsoft Access, for example, most books are written depth-first. Chapter one, tables. We're going to teach it. Everything there is to know about tables, all the properties, all of the field links, everything about tables, whether you need it or not right now. Okay, chapter two is going to be all about queries, everything you know about queries, action queries, append queries, make table queries, cross-tab queries, all that stuff, all right? And so on, forms, reports - that's depth-first. Each topic we're going to drill all the way down.
I think that's a horrible way to learn. I think that's a great way to write a reference book or an advanced book for people who already know a topic. But if you're learning something new for the first time, like Microsoft Access, for example, and you've never built a database before, I think a breadth-first approach is better. That's what I do. I teach you a little bit about tables, what tables are, how to build a simple table, the different types of data you could store. All right, we got a basic customer table built. Now let's go make a query. Just very simple select queries. See, show me all the customers from Florida. All right, that's good. We got that. So yeah, I got a little bit of queries down. Now let's go make a form, a really basic form. And then we come back, you know, once we've covered the beginner stuff, then we come back to tables and the expert levels.
All right, now we're going to get a little deeper into tables now. We're going to talk about the different, you know, input masks and stuff like that. So that's what breadth-first is. I think that's a much better way to learn from the beginning. You know, I'm not hitting you with too much stuff that's going to blow your brain up front. And that's how I've always developed my courses. In fact, one of the biggest complaints I used to get, I actually wrote an article about this. One of the biggest complaints I got was people would always complain that I'd say, well, we're going to cover that in a future lesson. Because as I'm doing the beginner stuff on queries, questions would come up in students' minds. Well, what about this? What about that? And I would have to say, we're going to cover that in a future lesson. Don't worry, we're going to get to it. It's too much for right now, all right? I'm not going to teach cross-tab queries when I just taught you about criteria, right? Or I'm not going to teach you VBA stuff because this is still a beginner lesson.
And years ago, when I first started doing this, people would get frustrated because I'd be like, well, we'll cover that eventually, but I didn't have that eventually lesson ready. Now I've got it. Now I've been doing this for 20-some years. I can tell you, okay, that's covered in Access Expert 14. Just go to my website, hit the search box, and it'll tell you where it's at. Well, where's inventory covered? Okay, right? A whole lesson or a whole article on this because, you know, that's you can't do everything at once. Like the person before was saying, I can't teach everything in every lesson. Some stuff's going to come later. Some stuff I covered before. You got to just go with the flow.
That's why I suggest if you really want to learn Access, take my full course, start with the beginner lessons, and work your way up because those are designed, you know, step A, step B, step C, step C. The TechHelp videos and the quick queries and stuff, these are just like one-off little helper topics. How to do something specific, but my course is a lot more comprehensive.
So thank you, Peladin. I salute you as well, sir. Good day. I say, good day. How good day? Good day. Good day. I said, good day. Another one of my members, Lauren, says you're a great teacher. Thank you very much. One question is there a way to adjust the table's screen to scroll as I tab to the next box. It's so frustrating when I tab over that I can't see the box. I just have to buy the table's screen. Do you mean a table in datasheet view? Are you just working in a table? Because as you tab, it should scroll over to the columns to the right. Is this what you're talking about? I'm not sure what you mean by the table's screen. So maybe give me some more details. And better yet, since you're a member, post this in the forum on my website because I check that a lot more often than I check my YouTube comments.
Gray Pazim is talking about my video where I said not to save your totals in the table if you can recalculate it on the fly, which is a general rule of thumb, but it's not hard fast set and stone. He says, although I agree the queries are a good method for getting a line total. I find that storing the totals in the table really optimizes the database by leaves. What I usually do is have an edit form. So when an invoice or an order needs to be adjusted, the user can use that. And some VBA, after some events and presto totals are recalculated. And yes, that is absolutely 100% true. And I think I even mentioned in that video, if that's the case, if you want to do that, if you've got, for example, millions of orders and you want to optimize the speed of your reporting, then great. Go right ahead and do that. Just remember that you have to have those calculations updated every possible place that that stuff can be recalculated. All right. Yeah, it's great for audit trails. It's the way that to. I agree. I agree. If you want to track, you know, what records were changed and when by whom? Absolutely. I agree 100%. You definitely need an audit trail or some way of tracking changes and saving that data in a table by all means. Everything that I teach is based on what's best in most scenarios. There are always going to be exceptions. All right, even in my own personal database, I've got some fields that are calculated in the tables because of optimization rules that I need for that particular instance, right? I try to teach things that are, you know, what the vast majority of people are going to find useful. But everything that I teach is always going to be exceptions. That's just how computers are. But thank you for the feedback.
Hulkout, Mohammed asks, how do you track changes and store them in a customer change log table like I teach in this video? I'll give you a link to this in a minute. And then only store those changes for a month and then delete them automatically. Well, what I would do is just simply have a delete query so that when you open the database the first thing it does in the background is run that delete query and just clear out your archive. I do the same thing. I download all the log files from my website and nightly. And then after I think I got it set to every quarter, every three months, it deletes the old ones because if it goes three months, I don't usually need to go back and find something. Same thing with my email batches. Whenever I send emails out for my database, the send logs, I purge those like, I think once a month. So yeah, just have an event that runs in the on open event for your main menu and then just run a delete query and set it so that it deletes everything over the month. And every day when you open your database, it'll clean it up. Take it after itself. Here's the Track Changes video that he's talking about. And here's a video on how to make a delete query. So check those out.
Here's a question I get a lot. Wout wants to know, why don't I put spaces between two words like first name? I see you're watching my How to Create a Database in Microsoft Access. This is my quickie. This is my how to do it in about a half an hour. And I put this together because a lot of people told me they didn't want to watch my four-hour Access Beginner 1 class. Now in the big four-hour class in this guy, it's a little over four hours long and teaches you all the basics. And I go over a more in-depth explanation as to why I don't use spaces in my field names or my table names. Basically it boils down to once you get into a little more advanced stuff, making queries with parameters and VBA programming or macros on it. When you've got fields that got spaces in them, you got to remember to put brackets around everything. And it just becomes a pain. And you don't have a lot of the problems that will come up if you don't put spaces in your field names. So one of the things I discovered after 10 years or so of building Access databases is, hey, it's a lot easier if I don't have spaces in all my object names. So if you have time, go watch this, it's free. It's on my website, it's on my YouTube channel, and it will explain all those little questions like that that I go over in the next.
Okay, Caval Green asks, if I wanted to format a sales date in a query as a month and year, could I use this calculated field instead? Month and year is format sales date, mm/yyyy. Yes, you can if that's the format that you want, but just keep in mind once you format it like that, you're now dealing with a text field. Anytime you use format on a date, it changes it to a text field. And I know I've done this before myself in years past, just be aware of that. I used to format stuff as yyyy-mm, and it works and it's good for, you know, you can sort and stuff like that, but just keep in mind, it's now text and you can't do things like, you know, add a month to it, that kind of stuff. Here's a video that talks more about the format function. I've got a couple of videos coming out very soon that deal with some additional date formatting. So look for that soon. And I cover formatting in tons of my different classes too. So I got lots of stuff on formatting.
All right, next up Michelle asks, it would be super if when going to the record that has the other phone number, that it goes to the record without filtering. All right. She's talking about my Warn But Allow video where I say, okay, let's say you're like a drug store or you, you know, you, when a customer comes in, you look them up by their phone number, right? I used to find that annoying, but now I get it. It's just an easy way to keep your customers, because nowadays everybody's got, you know, their own cell phone number. So you type it in. If it's there, it takes you to the record. If it's not there, it prompts you to add it. Okay. And she's saying it'd be helpful if it went there without filtering because the way that I open it in this particular video is I use a where condition in DoCmd OpenForm. Basically that will filter the results so that it only shows you that record. You can use a different method called DoCmd FindRecord, where it will open the form and then find the record by just going down the records so it finds it. That's a different method. It opens slightly slower, but it's certainly valid. In fact, this is the topic of a video I have coming out later this week. So it might be even tomorrow, I'm not sure, but keep your eyes open. This will be hitting soon. And I know I've covered it in a couple of my developer courses too, but just hang in there.
You'll see Alex asks, which one is the correct way of doing things? Tables, forms, data, then relationships, or relationships before you enter any data? Me personally, I find it easier to enter data first and get sample data in the tables. Not real data, just some junk data, some sample stuff. And then once you can see the data all laid out, then it's sometimes easier to understand how the relationships work. Unless you've been like me, I've been doing this stuff for 30 some years and it's just kind of second nature for me to know beforehand how I'm going to lay it out. But even then I still come up with things that I throw in on the fly last minute. That's why I love Access because it's a rapid development tool that you can very easily make modifications and change things. As I talk about in a lot of my classes, including my free Access Beginner 1 class, I like to lay out my table structure first, either on paper or in an Excel spreadsheet. Just show me the layout.In a lot of my different videos like this one, the association series, I go into Excel and I lay out the table structure first. Here's the person table, here's the family table, and so on. Once you get all your tables and your fields laid out, it's easier to see where you want the relationships to be between them. That's what I usually do.
Lay out the tables first, and then I get some sample data in the tables. I almost never use system level relationships in the relationships window. I just set up ad hoc relationships between my queries because I don't usually rely on referential integrity or things like that. But once in a while, I do. Again, I have millions of videos on this stuff.
But yeah, the bottom line is whatever you're comfortable with, whatever works, right? If you want to set up the tables and the relationships first, then put data in. I usually always build the forms last. I try to get all the tables built first. But sometimes I'll build the table, then whatever query I need to go with the table, and then the form based on the query. It's very situational. There is no one right way to build a database. I can just show you all the different options that are available. I give you a big box of Legos and I teach you how all the pieces work.
Abdullah thought that he broke Access because his wizards wouldn't start, like the combo box wizard and stuff. In this video, I explain why the wizards don't start. It's very hard to break Access. It's a very resilient program. You have to try to break Access. So don't be afraid to click on stuff. Don't be afraid to experiment and play with it. Make sure you back up your database regularly and then just go for it. If you're curious what something is, click on it. If you want to know what FDISC does, type it in and see. No, I'm just kidding. I'm going to type in FDISC or format.
Finally, I just wanted to thank everyone who wished me a happy Thanksgiving after I posted my little happy Thanksgiving video. This was 11 days ago now. Like I always say, I get to the YouTube comments whenever I can. I always read them and approve them because I have approvals on since you wouldn't believe the spam that I get on my channel. So many people post spam, and a few rude ones here and there. But mostly 99% of you guys are awesome. Thank you very much. There's just a couple of them, and there's a couple more. We got lots of them. Thank you very much. I appreciate it.
I do read all the comments and I do my best to respond to them. Usually, when I get around to doing quick queries is when I start going down the comments. I do them in reverse chronological order just because that's the way that YouTube shows them to me. It's very difficult to scroll all the way down to the oldest ones. If you have an old comment down there, there's probably some from a couple of years ago that I haven't gotten to responding to yet. I've read them all. If it's approved, it's read.
When I sit down to reply, I go down and it's like, okay, they get older and older and older. Eventually, I'm going to get down to like, if new ones stopped coming in right now, I'd probably finish them all in probably 2035. If I haven't responded to your comment, don't feel bad. I just don't have a lot of time for YouTube comments. But if you do have questions, post them on my website. I do read the forums there as much as possible. I have a great group of moderators. If there's anything really cool, they always flag it and I take a look at it.
So check that out. But that's going to do it, folks. That's Quick Queries Number 22. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
- Access database performance over VPN
- File-based vs. client-server databases
- Access front-end with SQL Server backend
- Slow Access database with file-sharing services
- Fixing grainy logo in Access reports
- Calculating expiration dates with DateAdd
- Windows ISO date format vs. Access formats
- Access transparency issues with GIF/PNG images
- Installing Access on Windows S mode
- Using Access over a local vs. wireless network
- Record locking in Access databases
- Locking fields in Access with VBA
- Get next-to-last record using DMax
- Access Developer Network for custom solutions
- Why fields don't have spaces (naming conventions)
- Tracking changes with a change log table
- Automatically deleting old log records
- Date formatting: extracting year from datetime
- Adding combo boxes in Access search forms
- Breadth-first vs. depth-first learning
- Handling prerequisites in tutorial videos
COMMERCIAL:
In today's video, we're diving into Quick Queries episode number 22, where we tackle your questions from emails, forums, and YouTube comments. You'll learn about Access performance on different networks, optimizing database image quality, calculating dates with DateAdd, and more. We also tackle FAQs like changing date formats, utilizing combo boxes in search functions, and why not using spaces in field names can streamline your work. Whether it's setting up your Access database for smooth multi-user access or understanding record locking, we've got you covered. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main reason Access databases run slowly over large distances like a connection over VPN?
A. Access is a server-based database system.
B. Access needs to download only the queried data.
C. Access is a file-based database system and downloads all data before processing queries.
D. Access databases are incompatible with VPN connections.
Q2. What is one potential issue when storing sensitive data such as logos or images in reports in Access?
A. Access reports cannot display images at all.
B. High-quality images may look grainy or washed out.
C. Access supports only GIF image format.
D. Logos cannot be embedded in Access reports.
Q3. Why might changes in the system date format not reflect within an Access database?
A. Access databases always use the US date format.
B. Access doesn't support international date formats.
C. Custom date formats within Access override the system date settings.
D. Access does not use system date settings.
Q4. Why are transparent images not displayed correctly in Access reports?
A. Transparent images are always incompatible with Access.
B. The limitation is specific to reports; they display correctly in form views.
C. Access does not support the PNG image format.
D. Access allows transparency in JPEG images only.
Q5. What is the issue with running Access databases over a wireless network?
A. Wireless networks have slower speeds than wired connections.
B. A disconnection during data transmission can lead to a corrupted database.
C. Access databases need a VPN for safe network usage.
D. Wireless networks do not support Access.
Q6. How can record locking in Access be managed to avoid multiple users editing the same record?
A. Use a VPN to control access to each record.
B. Enable default record locking at the database level.
C. Set each record to private to prevent edits.
D. Lock all records at the time of database creation.
Q7. What is a common solution for recalculating totals in Access tables for optimization?
A. Only calculate totals at the time of report generation.
B. Store recalculated totals after each transaction in the Access table.
C. Use real-time calculation in reports only.
D. Avoid storing any calculated totals in Access tables.
Q8. In Access, why should spaces in field names be avoided?
A. Spaces confuse users when entering data.
B. Spaces alter field calculations.
C. Spaces require brackets in queries that reference those fields.
D. Spaces prevent text sorting in Access.
Q9. What is the impact of using the 'Format' function to display a date as month and year?
A. It retains the date format and type.
B. It causes error when sorting the data.
C. It converts the date into a text value.
D. It keeps the original date value and adds text.
Q10. Why might it be beneficial to establish basic relationships between data tables before entering actual data?
A. It ensures better data accuracy from the start.
B. Sample data can be misleading.
C. Relationships are only beneficial after all data is entered.
D. Entering data first confirms that relationships are unnecessary.
Answers: 1-C; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-C; 9-C; 10-A.
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone is an installment of our Quick Queries series, where I address the questions you send in via email, forums, or my YouTube channel. These questions might not each justify a standalone video, so we tackle them together in this format.
Before diving into the questions, I'd like to make a quick note. I apologize for any distortions in the previous episode. I've been experimenting with new video software, which caused some display discrepancies. Normally, I integrate screenshots into PowerPoint slides, but recently, I inserted them directly, which resulted in stretching. I appreciate your understanding and will avoid this method in the future.
First, we have a query from a student experiencing slow performance with their Access application. Previously, both the front-end and back-end were in the same location, but now the back-end is in Orlando, and the front-end remains in Boca Raton. It seems you're connecting over a VPN, which can slow things down considerably unless you have an exceptionally fast VPN connection. Access is a file-based database system that doesn't perform optimally over significant distances.
Here's why: when running a query, Access must download all data before processing it locally, unlike a client-server database like SQL Server, which processes data on the server and only sends the relevant results. If you're using Access for its front end, I recommend leveraging a stronger server on the back end, such as SQL Server, to handle data requests more efficiently. There are various options for optimizing Access, including remote desktop setups, SharePoint integration, and cloud databases.
Now, on to Rosa's problem with inserting a logo onto a printable invoice report. If the logo appears low-quality, there are specific methods to enhance its appearance. I've addressed this issue in a separate video, which I'll link to at the end.
Deanna is facing difficulty calculating contract expiration dates from start dates. There are a couple of methods to achieve this. You can use the DateAdd function to add years, months, or quarters, or if you're dealing with a simple number of days, employing date math works well. In Access, dates translate to numbers where one day equals one unit. Adding fractions of a day allows adjustments down to hours.
Keith mentioned switching his Windows system date format to ISO but noticed Access not using this format. Even if Windows defaults to ISO, any custom formats in Access will override it. Ensure fields are set to use the default 'short date' format in Access unless necessary.
Tecchi Yandin experiences issues with transparent images in reports. While they appear correctly in form view, they lose transparency upon printing. Unfortunately, transparent GIFs don't function as expected in reports, likely a limitation of Access.
Next, regarding installation, ZVIG asked about Access on Windows S mode. Windows S mode restricts installations to apps from the Microsoft Store, so Access isn't supported in this mode. For local network sharing, Access works well over a wired network. Caution is advised with wireless networks due to potential data corruption risks during disconnections, and it's not recommended over file-sharing services like Dropbox.
Fiona's inquiry about locking fields involves distinctions between database-level record locks and front-end modifications to prevent changes once data is entered. For more in-depth controls, implementing VBA code across relevant forms is necessary.
For specific database functions, like determining a secondary maximum value, using DMax is ideal over form footer functions like Max. When it comes to creating advanced features, I no longer offer custom development but have a wealth of instructional materials covering most topics in my developer series. Additionally, my Access Developer Network connects you with professionals who can assist with projects.
In response to criticism about skipping foundational steps in some tutorials, I highlight the importance of prior knowledge from prerequisite videos. The layering of tutorials ensures a logical progression without overloading each lesson with basics.
For those exploring specific functions, like extracting just the year from a date-time, using the Year function suffices. Remember this converts dates to numerical values. As for member questions on adding search functionality utilizing combo boxes or managing multiple auto numbers, I've elaborated on these approaches in past discussions and videos.
My teaching philosophy contrasts breadth-first and depth-first approaches. I advocate breadth-first learning for beginners, introducing topics incrementally before delving deeper. This technique facilitates clearer understanding and application without overwhelming novices.
Social questions like Lauren's about navigational usability or questions regarding field naming conventions in Access reveal practical tips embedded throughout my resources. I generally avoid spaces in field names to prevent potential complications in querying and coding.
I offer guidance on formatting using the Format function and encourage exploring its applications in various scenarios, while advising caution when converting date types to text format.
Addressing another question, Michelle wishes for seamless record navigation without filtering. While using DoCmd FindRecord provides an alternative, this method is covered in forthcoming tutorials.
The order of database design also sparked interest. My usual process starts with layout planning, sometime aided by sample data before formally establishing relationships. Yet, flexibility remains a key strength of Access for iterative developments.
Despite challenges with Access seemingly 'breaking,' fear not; it's resilient. Most issues arise from misconfigurations like disabled wizards, which I address in troubleshooting tips. Always experiment and explore safe features within Access to expand your understanding.
Finally, I appreciate all the positive feedback, especially around holiday greetings. While response times to comments vary, active engagement within my forums is consistent for addressing technical queries.
For a complete video tutorial with step-by-step instructions on the topics discussed here, visit my website through the link below. Live long and prosper, my friends.
Topic List
- Access database performance over VPN
- File-based vs. client-server databases
- Access front-end with SQL Server backend
- Slow Access database with file-sharing services
- Fixing grainy logo in Access reports
- Calculating expiration dates with DateAdd
- Windows ISO date format vs. Access formats
- Access transparency issues with GIF/PNG images
- Installing Access on Windows S mode
- Using Access over a local vs. wireless network
- Record locking in Access databases
- Locking fields in Access with VBA
- Get next-to-last record using DMax
- Access Developer Network for custom solutions
- Why fields don't have spaces (naming conventions)
- Tracking changes with a change log table
- Automatically deleting old log records
- Date formatting: extracting year from datetime
- Adding combo boxes in Access search forms
- Breadth-first vs. depth-first learning
- Handling prerequisites in tutorial videos
|
