It's that time again, another quick queries video, number 15 in the series. These are videos where I answer your questions that may not need a whole video in and of themselves. So, let's get to it.

First up is a question from Steven. Steven says, "While developing my software, I'm creating it with the intention of selling it to other companies who might be facing similar challenges. I'm considering how to manage updates for clients if they use the software. For instance, updating the front end seems straightforward, but I'm not sure about the best approach for updating the back end, such as making changes to or adding tables. How would I handle these updates for my clients?"

So yes, Stephen, that's an age-old question. You've got a database you're distributing to different people, they've got their own sets of data, assuming it's a split database. There are two ways you can handle this. The first option is to just give them the new updated backend database and then give them a tool to import their data. So let's say you added a few new tables, added a couple fields, changed some properties, whatever. You just give them the new database, give them a button they can click on, and it will just import all of the data from their old database, and then you'll just have the new tables with the fields or whatever that you can fill with data then. That's probably the easiest way to do it, but it does require some work on the part of the user to know how to do that. You can automate a lot of that with VBA, though, and I cover the basics of importing in Access Expert 21.

Now, the more complicated way to do it, the developer way to do it, would be to programmatically edit their tables. So if you've added some fields or you've added some whole tables, in fact, you can manipulate the table structure using either SQL or VBA with something called a table def. Now, the SQL way is fairly easy. I cover that in part three of my SQL seminar. And you can use TableDef, which I introduced in Access Developer 37, that uses DAO. So either one of those ways, you can then send them a new front end file. And when they put that in place, it can check the back end to see, "Hey, are these fields here?" And if not, it can add them. So there's definitely ways to do that. But those are the two methods I recommend. And again, of course, if you'd like to see more lessons on this stuff, post a comment down below.

A rabid follower on YouTube says, "After all the stuff that I show in my three-part disable printing series, there's still another way to get in there if the user knows enough to find the usysRibbons hidden system table. They can get in there and change properties in there and that will still allow them to print."

Yeah, there's always a way around it. No matter what you do to lock down your database, there's always some way that someone who knows enough about Access can usually get in there and change things. And to that, I can only respond with, "Yeah, the techniques that I show you, they're good enough to keep 99.9% of people out of the database. But you get someone who really knows their stuff, yeah, there's a way around most things." And I mean, remember, anything you can get on the screen, you can print. You can just screen capture it, drop it in a word, and then print it that way. So the techniques that I'm showing you are just kind of to frustrate the users who don't know any better. So that's about all you can do.

Next up, a question from user wq9, a bunch of letters and numbers, whatever. And basically, what he's saying is he's got a PDF that he has to fill in with data from Access, and it's a state-mandated PDF. So you've got to use their form. Because normally what I tell people is do your best to just recreate that form in Access as a report, make it look exactly like the other one. But he's got to use this one, I guess. And it's got drop-down boxes in it.

Now, drop-down boxes are a nightmare. Now, the technique that I show in my video where I show you how to fill in PDFs is it uses the send keys to send the data from Access into the PDF file. And I believe you can use the arrow keys or the Alt-Down Arrow key to open up a list box or a drop-down list in the PDF. But it's been a while. I don't have one handy. And it's been a while. So you have to experiment and see whatever you can do with the keyboard to fill in that data, you should be able to replicate with Access and send keys. Alt-down arrow, you can do with send keys, and then you can up and down arrow. But if they change the form at all, it's going to mess everything up, and you're going to have to reprogram it again.

Now, like I do mention down here, with the paid version of Adobe Acrobat, you do get some libraries you can use, and you can automate it with VBA. Honestly, personally, I've never done it before because I've never had the paid version of Adobe Acrobat. I've never needed it. You know, whenever I create PDFs, it's either from Word or directly from Access and I've never needed to, you know, modify them at all with Acrobat directly. So it's a great document viewer but that's the extent of my knowledge. So try it. Let me know what you come up with.

Next up, Benjamin, one of my Platinum members, says, "When I open a table in design mode, my version of Access will not remember the size or location."

Well, that's just how Access is, unfortunately. Forms and reports will remember their location. If you open up a form in form view, not design view, in form view, just when you're adding and editing records, if you move it on the screen, resize it, whatever, it should, you know, you hit save, it should remain there the next time you open it up. It's going to remember its position. Tables and queries don't do that. In fact, Sammy, that'd be a great one to add to the list for Microsoft. It'd be nice if tables and queries remembered their position on the screen. They don't, it'd be cool if they did.

Next up, Leonard, another Platinum member, says all of a sudden in design view for his forms he's missing that little button in the upper left corner where you can double click to bring up stuff's properties. This little guy right here, double click and it brings up the property sheet. He said his line tool is missing from the design tools, from the toolbox. He can't figure this one out.

One of my moderators, Kevin, gets the save for this one. Your rulers are turned off. Go to the arrange tab on the ribbon, go to size space, and then rulers. I've seen this happen before to people. Right under arrange and then size space and then you can turn off the grid, the rulers or snap to grid down here. Once you turn that off, look, that's what you got. Right, see? It's missing. All right, so that's how you turn it back on and off.

And as far as the line tool missing, Kevin again comes in for the save. They just changed the way the line tool icon looks. In older versions of Access, it used to look like a straight line. Now it's this guy right here. If you go back a couple of versions, the line tool looks like this, just a line. Right, and they changed it to this. So they got fancy on you, Leonard. They changed the line. So, shout out to Kevin again for catching both of these. It stumped me for a few minutes, too, because it was in the morning before I had my coffee. So that's the excuse.

Next up's a conversation we had in my Access forum on my website. Jeffrey, one of my Silver members, says he had a thought that he wanted to know whether or not Access automatically freed up memory from forms and subforms and stuff. When you close an object, does that memory get, you know, returned back to the system? And honestly, I didn't know. I suspected that it does a pretty good job of it, but I said I'd have to investigate.

So I opened up the TechHelp free template and it was taking up 48 megs of memory. I opened up the customer form, closed the customer form, pretty much stayed the same, opened up the customer list form, it's up to 50 megs, opened up the contact form, it's up to 51 megs. Then I closed them all and I waited a minute to see if Access cleaned that stuff up and it still stayed at 50 megabytes. So test number one, it didn't look like it was doing a good job of cleaning up after itself.

So then I decided to start up my main database, the one that actually runs my business that I do all my work in. And it starts up at 81 megabytes to begin with. So then I did a bunch of work, you know, I do my customer service emails and stuff and my accounting tasks, and stuff like that, and it ballooned up to 120 megabytes from 80, okay? And then again, I waited, I shut everything down except I left the database itself running and it did not free up that memory that it took. So, I can only conclude that Access is not doing a great job of freeing up memory. When you open forms and reports and declare record sets and all that stuff, even if you manually destroy those objects, Access doesn't always get rid of the stuff in memory.

So that's why I recommend rebooting your Access database at least daily. Alright, if it's a database you're working with all day long, when you're done for the day, shut it down. Don't leave it running constantly. If you do have a database that runs constantly, like I've got what I call a server database. It's a front-end Access database connected to my SQL server that does a lot of, you know, maintenance stuff. It processes orders and that kind of stuff. I have that guy reboot every hour. Sometimes you'll hear it in the background in my videos. You'll hear the Star Trek transporter. That's my hourly chime. At the top of every hour, it just reboots, starts itself over again, cleans its memory. Because I know from the past, you know, I've gone on vacation before. You know, after three days of vacation, I'd get server warning messages that it's not running. I'd have to log in remotely and reboot it. So now I have it just that it reboots every hour on the hour and it keeps itself nice and fresh.

And Kevin Yip chimed in and said that Access is not the most resource-intensive app. He said, "Your web browser is probably using more memory." Yeah, that's probably right. Yeah, you can see it right there. Google Chrome, a whole bunch more memory than Access is using. But the point is that Access, if you let it run continuously, continuously, it's going to not free up the memory that it's taking. And it's actually not that good at managing its own memory if it's left running for multiple days in a row. Whereas I know my web browser, I leave open all the time. Well, yeah, the web browser will lock up too sometimes. But the point is, if you let Access run continuously and don't restart it once in a while, you're going to run into problems. So shut it down at the end of the day when you're done using it, restart it. If you've got a server machine or something sitting in the corner running Access continuously, reboot it at least once a day. Sammy, I would tell you to put this on the list, but I don't think there's much they can even do about that.

Khan, in the visitor forum on my website, where anyone can post a question, by the way. You don't have to be a paid student to post in the visitor forum. Basically, in a nutshell, his question says he's got his products up in this table, but he wants to be able to say that if something's got an expiration date coming up soon he wants to be able to sell that product first, in other words, keep track of each individual item in stock, not just a collection of products.

So in that case, you're going to need two separate tables. One table is your product table, and that just says "I have 10 apples," "I have 15 hard drives in stock." But your inventory table, or your item table, or whatever you want to call that, will track each individual item, and you're going to have to have some way of tracking those items like a serial number or a label or something you stick on it. This is hard drive one, oh, two, hard drive one, oh, three, and if you're selling produce, for example, you can track what the expiration date is on that, and then you know your system could then be programmed to say "Okay, sell the stuff with the expiration dates coming soonest," right? Rotate the stock properly.

I covered tracking individual items with barcodes or whatever kind of label you want to put on them in either my Access Developer 27 class, or if you want to get serious, I got a barcoding and inventory seminar. I'll put links to both of these things down below.

Anthony, one of my Silver members, asks if it's possible to build your own unsubscribe button when sending multiple emails, or do you need a service to do this. I got several videos where I teach you how to send email from Access. Now, maintaining that mailing list is something that you really can't do with just Access. You've got to get some way of getting that information back from the user. So they're either going to reply unsubscribe to that, which is how you used to do it in the old days. Get off the mailing list, reply with unsubscribe. And then the mail server would handle that. They had software for that. Nowadays, the way most people do it is there's an unsubscribe link. So there's a link on the bottom of the email. You click on it, but that link's got to go somewhere.

Me, personally, I have a web page built into my site where it just collects the click, and then it saves it in a database, and then the database processes that and removes the email address. Technically, I leave the email address in there, just mark it, do not send. That way it doesn't accidentally get sent to again. But that's something that you have to program outside of Access. It's not really something you can do in Access. That's more of a website thing. Now, of course, I do teach web design using ASP. So, if you guys are interested in seeing how something like this would work, post a comment down below. And, like everything, the squeaky wheel gets the grease. If enough of you are interested, I'll put together a lesson on it.

Here's an interesting question from Dan, one of my Gold members. Dan says he's got two tables that get updated with =Now(). So you got the exact time in the table. Okay, ignore this thing. And he'd like to match up the two tables somehow. But here's the rub: even though both fields populate on the After Update event, the table is two seconds behind the other. So the two date-time fields never match. So he wants to know if he could store it with just the date and not the seconds.

Now, my first suggestion is, I would not rely on times to link two records together. If there's any other way you can do it, I'd recommend doing that. Because you could have this situation happen, where you've got 11:59:59 and then the next one's on the following day at one second after midnight. So that could possibly happen.

But if that's what you want to do, you can always use TimeSerial and say =Date() + TimeSerial(). TimeSerial takes the individual components, the hour, the minute, the second, and builds a time value. Here's a video here. I'll show you the link to it in just a second. But you say, send it the Hour of Now(), the Minute of Now(), or whatever your date field is, and then a 0. And so if you send to it 11, 59, 59, you'll end up getting 11:59 and zero seconds. Then you could use that to link the two records together. But again, see if you can figure out some better way, like an ID or something, because times are not very reliable.

I just checked, and I don't have a TimeSerial video yet, a TechHelp video. I've got a DateSerial video. DateSerial works the same way. It's year, month, day. You give it the individual pieces; you make a date value. TimeSerial works the same way. I know I do cover TimeSerial in detail in my Access Expert Level 28 class, which is where I talk about all the date-time functions. That's actually part two. Part one is in Expert 27. I got a whole series where I cover all the different functions in Access, from the date functions, the currency functions, the trigonometry functions, all the functions are covered.

Next up is a question from one of my moderators, Sammy, a good friend of mine. And he says that he wants some clarification on referential integrity because I've mentioned a few times that referential integrity does not work in a split database. But he's tested it in his back end in a split database, and the cascade delete worked just fine.

So I should probably clarify, and I think I've mentioned this in a couple of my classes, referential integrity only works inside of one database file. So if you've got a front-end and a back-end, and you only have one of each, you can put referential integrity, including Cascade Deletes, in that single back-end file. What I meant to say was it doesn't work across database files. So if you've got your back-ends split into multiple back ends, like you've got your customers in one backend and your orders in another, you can't do referential integrity between two different files. But as long as all the tables are in the same physical ACCDB file, then you can have referential integrity there and it'll work just fine.

Me personally, I generally don't rely on referential integrity. I like to put that coding in my front end. So, for example, if I delete an order, then I delete all of the child orders in my code in the button that deletes the order. So I like to control all that myself with code. But yeah, you can certainly use it if you want to. And there's my video on referential integrity. And I cover it in a lot more detail in my "Access Expert" classes. The Expert classes focus a lot on relationships between tables.