Quick Queries #15
By Richard Rost
2 years ago
Updating Backend, Memory Leak, Ref. Integrity, More
Welcome to another TechHelp Quick Queries video brought to you by Access Learning Zone. I am your instructor, Richard Rost. Quick Queries videos are for me to answer your questions that may not need a whole video themselves. In today's video...
Topics Covered
- Updating distributed database back ends
- Importing data into new back end database
- Automating updates with VBA
- TableDef for manipulating table structure
- Using DAO with TableDef
- Sending new front end files for updates
- Managing print permissions in Access
- Handling updates to usysRibbons system table
- Filling in PDF forms with Access data
- Using send keys to interact with PDF forms
- Automating PDFs with Adobe Acrobat
- Remembering form and report positions
- Access not remembering table and query window positions
- Missing property sheet button in form design view
- Identifying new line tool icon in Access
- Rulers and design tools in Access
- Memory management in Access
- Rebooting Access for memory cleanup
- Tracking individual inventory items
- Linking tables to individual inventory items
- Building an unsubscribe button for emails
- Linking Access data to web applications
- Matching records with identical timestamps
- Using TimeSerial to standardize time values
- Referential integrity in split databases
- Cascade delete in split back-end databases
- Coding for referential integrity in the front end
Previous Quick Queries
Links
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, qq, VBA automation, table updates, importing data Access, SQL seminar, TableDef, DAO, disable printing, usysRibbons, screen capture, filling PDFs, Access reports, data entry, Adobe Acrobat VBA, save layout, toolbox, table design, reboot Access database, inventory management, barcode tracking, email unsubscribe, =Now(), TimeSerial, linking records, cascade delete, split database
Intro In this video, we'll talk about a variety of Microsoft Access questions, including strategies for updating back-end databases for clients, limitations in Access design view, filling in PDF forms from Access, tracking inventory with expiration dates, how Access manages memory, building unsubscribe links for emails, handling date and time values, and understanding referential integrity in split databases. This is Quick Queries 15.Transcript 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.
Quiz Q1. What are the two methods recommended for updating a distributed backend database with new tables or fields?
A. Provide a tool to import data and programmatically edit tables
B. Change database passwords and enforce new data validation rules
C. Provide a complete new version of the backend and frontend
D. Request users to manually recreate tables and fields
Q2. What are the consequences of trying to lock down an Access database according to the video?
A. It's not possible to lock down an Access database
B. It can keep out 99.9% of users but not someone who knows their way around Access
C. It will completely secure the database from unauthorized printing
D. It doesn't affect database performance at all
Q3. How can you manage filling in a PDF with drop-down boxes using Access?
A. You cannot interact with PDF drop-down boxes from Access
B. Utilize send keys functionality to simulate keyboard inputs to the PDF
C. Convert the PDF to a Word document and use Access to fill it in
D. Only use the paid version of Adobe Acrobat for any PDF manipulation
Q4. According to the video, what functionality is NOT remembered by Access in design mode?
A. The size and location of forms and reports
B. The size and location of tables and queries
C. The size and location of the navigation pane
D. The property settings of form controls
Q5. How can you ensure that Access cleans up memory when using forms and reports heavily?
A. Access automatically cleans up all memory, so no action is needed
B. Reboot Access at least once a day
C. Increase the RAM on your computer
D. Manually delete the forms and reports
Q6. What kind of table structure would you need to track individual items with expiration dates as mentioned in the video?
A. A single table for products with expiration dates
B. Duplicate tables for each individual item
C. One product table and a separate inventory table with item tracking
D. Inventory table with only expiration dates but no item tracking
Q7. What is necessary to create an unsubscribe button in Access for sending multiple email?
A. Only Access is required to implement the complete unsubscribe feature
B. A service or external website to receive and process the unsubscribe requests
C. A complex SQL query within Access
D. A built-in Access feature that requires no additional setup
Q8. What is the reason Access databases should be regularly rebooted according to the video?
A. To allow for scheduled maintenance and updates
B. To prevent corruption of database files
C. Access may not free up memory after closing forms and reports
D. Rebooting accelerates the speed of database queries
Q9. For linking records based on time where there's a delay of a few seconds, what was suggested?
A. It is not recommended to rely on times to link two records together
B. Use =Now() to sync both tables
C. Manually adjust the times on each table as needed
D. Increase the server time-out settings
Q10. What is the requirement for referential integrity to work in a split Access database?
A. It requires a paid upgrade from Microsoft
B. It only works in the frontend database
C. All related tables must be in the same physical ACCDB file
D. You can have referential integrity across different backend files simultaneously
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-C; 7-B; 8-C; 9-A; 10-C.
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 In today's Quick Queries video, number 15 in the series, I'm answering a collection of your questions that do not quite need their own dedicated video. Let's jump right into it.
Steven asks about the best way to handle back-end updates for Access databases he's selling to clients. He wants to know how he can manage changes, like adding tables or fields, for his customers. This is a classic situation for anyone distributing Access databases. If you have a split database with a separate back end holding data, there are two main approaches.
The first method is to provide clients with the updated back-end file and a tool that imports their existing data into the new file. If you have added tables, fields, or changed properties, the import tool can transfer their data over. This is fairly straightforward and can be greatly automated with VBA, which I cover in detail in my Access Expert 21 class. The downside is it does require a little know-how from the user.
The second, more advanced approach is to update the tables programmatically within their existing back end. Using SQL or the TableDef object in VBA, you can alter or add tables and fields directly inside the client's file. I go over the SQL technique in part three of my SQL seminar, and I cover TableDef, which leverages DAO, in Access Developer 37. With these methods, you can distribute an updated front end that checks the back end and makes necessary changes if certain fields or tables are missing. Either method works, so choose the one that fits your users' needs. If you want more resources or walkthroughs on this, just let me know in the comments.
A YouTube viewer brought up an interesting point about my series on disabling printing in Access. Even if you hide everything else, a user who really knows Access might still use the usysRibbons system table to re-enable printing. The reality is, no matter how locked down your database is, someone with enough expertise can usually find a workaround. The standard techniques I teach are strong enough to deter the vast majority of users, but nothing is completely foolproof. And even if you lock it down thoroughly, a determined person can always just use a screen capture, paste it into Word, and print it from there. The methods I demonstrate are simply to make it inconvenient for casual users. That's about the best you can do.
Next, someone going by 'wq9' needs to fill out a state-mandated PDF with data from Access. Normally, I recommend recreating forms as Access reports for ease and flexibility, but sometimes you are forced to use the provided PDF, which may include tricky components like drop-down boxes. The technique I teach for filling PDFs uses the SendKeys method to automate entering data into the open PDF. For drop-down boxes, you can simulate keyboard operations with SendKeys, such as Alt+Down Arrow to open the drop-down, and arrow keys to select options. You may need to experiment since any change to the PDF's layout will mean updating your code. If you have the paid version of Adobe Acrobat, you can also consider automating with libraries included there, although I do not have direct experience with this myself. Give it a try, and let me know how it goes.
Benjamin, one of my Platinum members, says that when he opens a table in design mode, Access won't remember the size or position of the window. This is just how Access currently works. Forms and reports will remember location and size the next time they open, but tables and queries will not. It would be a nice improvement, but for now, that's the behavior we have.
Leonard, another Platinum member, noticed some tools missing in the form design view, specifically the little corner button for properties and the line tool. The trick here is that turning off rulers in the Arrange tab causes the Property Sheet button to disappear. To fix it, turn the rulers option back on under Arrange – Size/Space – Rulers. For the line tool, the icon changed appearance in recent versions of Access. Older versions showed a simple straight line, but new versions have a more stylized line icon. This tripped up even me before I had my coffee one morning.
We also had a discussion in the forum led by Jeffrey, a Silver member, about whether Access releases memory efficiently when forms and subforms are closed. From my tests, opening and closing various forms did not appear to reduce Access's memory usage significantly, even after waiting a while. My own main business database behaves similarly. After running it for a while and then closing most forms, the memory usage does not drop much until the application is completely closed and restarted. This is why I recommend restarting Access databases daily. For databases that run maintenance or background processes continuously, I automate a restart every hour to keep things fresh. Like Kevin Yip pointed out, Access is far less demanding than something like a web browser, but memory management is still a concern for long-running sessions.
Khan posted in the visitor forum asking how to track individual stock items, particularly those that may expire soon so they can be sold first. The answer here is to employ two tables – a product table for general inventory counts and an item or inventory table to manage each unit with a unique identifier and expiration date. That way, you can use your database logic to rotate stock and handle first-expiring items first. For info on tracking items with barcodes or serial numbers, check out my Access Developer 27 class or my Barcoding and Inventory seminar.
Anthony, another Silver member, wonders about building an unsubscribe option for emails sent from Access. While you can send emails from Access, handling unsubscribe requests requires more than Access alone. In the past, users would reply with 'unsubscribe' and some tools would pull that out of returned emails, but most modern systems use a web link that performs the unsubscribe process. I use a web page that receives the unsubscribe click and updates my own database accordingly. This is more of a web programming task than something you can do solely inside Access. If there is enough interest, I can put together a lesson covering a basic web-based unsubscribe solution.
Dan, one of my Gold members, asks about linking two tables that both use =Now() to record timestamps. However, one table ends up being two seconds behind, so the date/time fields do not match exactly. He's wondering about storing only the date or limiting precision to eliminate the seconds. My advice is to avoid joining records solely based on timestamps, since tiny differences are hard to avoid and can lead to mismatches. If you must do so, you can use functions like TimeSerial to build a time value that trims off the seconds, leaving just hour and minute. However, using unique IDs or other reliable matching fields is generally preferable. I cover these types of date and time functions in depth in my Access Expert 27 and 28 classes.
Sammy, one of my moderators, is looking for clarification on referential integrity in split databases. I've referenced before that referential integrity does not work in split databases. To be more precise, referential integrity, including features like Cascade Deletes, will work within a single back-end ACCDB file, even if that file is linked to a separate front end. However, if your solution uses multiple separate back ends, you cannot have referential integrity across two physical database files. For most single back-end splits, it works as expected. Personally, I tend to manage cascading deletes and other relationship maintenance in code for greater control, but the built-in features are certainly valid if it suits your design.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Managing back end updates for distributed Access databases
Automating data import from old to new back end
Programmatically editing table structures with SQL or TableDef
Handling user attempts to bypass print restrictions in Access
Sending keystrokes to interact with PDF drop-down fields from Access
Considerations and methods of automating PDFs with paid Adobe Acrobat
Access design mode UI: restoring missing property sheet and line tool
How to toggle rulers and toolbox items in Access design view
Access memory management and form closure effects
Strategies for avoiding memory bloat in long-running Access databases
Designing inventory tables for tracking individual items with expiration
Implementing first-expiring-first-sold (FEFO) inventory in Access
Approaches for unsubscribe links in email sent from Access
Building a product/item table structure to track individual inventory
Aligning date-time fields between tables and truncating seconds
Using TimeSerial to remove seconds from date-time values in Access
Referential integrity behavior in split Access databases
Limitations of referential integrity across separate back ends
Alternatives to built-in referential integrity using VBA code |
