Quick Queries #28
By Richard Rost
12 months ago
Access Tutors, Print to Edge, Security, Updater, More!
In this Quick Queries tutorial, we'll explore a series of insightful Microsoft Access tips and tricks. Discover how to print to the edge of a page with Microsoft Print to PDF, tackle issues with Access linked to a SharePoint list, and learn the essential steps for preparing an Access database before release. I'll also guide you on how to distribute new database copies on a network and discuss methods to make Access look like a full application. Plus, learn about my journey into teaching tech with a passion. Enjoy a compilation of quick and informative lessons designed to enhance your skills.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries video, Microsoft Access Developer Network, Microsoft Print to PDF, zero margins PDF, Design View, Page Setup dialog box, Print Options page, page edge printing, background color PDF, SharePoint list negative IDs, synchronize Access SharePoint, DLookup connectivity test, Access VBA relink tables, Access database release steps, record locking optimistic pessimistic, Access Updater tool, ACCDE front end, Access database look like application, Access runtime requirements, teaching access experience.
Intro In this video, we'll talk about a variety of Microsoft Access topics, including how to print edge-to-edge PDFs by setting margins to zero with Microsoft Print to PDF, troubleshooting negative primary keys when linking Access to SharePoint, and using DLookUp to test for connectivity issues. We'll discuss key steps to take before releasing an Access database, methods for distributing updates to users with batch files and utilities like Access Updater, tips for exporting and importing data when making table changes, and ways to customize Access to look more like a full application. I'll also share a bit about my own background as a Microsoft Access trainer.Transcript What's a Quick Queries video? Well, it's a video that contains a bunch of other little videos. Maybe it's a question that didn't merit an entire video on itself, but we're going to just talk about all that kind of stuff today. Here we go.
First off, before we get started with the questions, I just want to mention that I just recently updated my Microsoft Access Developer Network page. So if you are looking for a Microsoft Access tutor, maybe someone to walk you through stuff beyond what my videos cover, if you need help with your specific database, this is a page that contains a list of consultants, developers, tutors, people to do one-on-one training. Check it out. There's the link. I'll put a link down below, and that's all I'm going to say about that.
First up, we have a comment on last week's Quick Queries number 27 from one of my Platinum members, Donald Blackwell. I mentioned that with the normal printer drivers, you can't print to the edge of the page. But if you want to make a PDF, you can't do it. But Donald taught me something. I love it when you guys teach me something. He says, if you switch to a specific printer and use Microsoft Print to PDF, you can set the margins to zero. This is news to me. I never knew this. I love this.
So what he's saying is, if you've got, let's say, my invoice, and you want to make a PDF out of this and change the background color, that was the question in last week's video. All you have to do is go to Design View. We're going to go into Page Setup, and then here, Page Setup dialog box. We're going to go to the Page tab, Use Specific Printer. That means whenever you print this, it's going to go to a specific printer. So for this, we're going to pick Microsoft Print to PDF. Hit OK.
Now on the Print Options page, you can set the margins to zero, which you can't do with a typical printer driver because most printers can't print directly to the edge of the page. There are a few that can like plotters and stuff, but generally, you can't. But if the point is you're trying to make an electronic file like a PDF out of this and you want a full background color, this is how you do it. Hit OK. Now you just come in here.
The first thing you got to do, though, is make sure that this goes right out to the exact edge of the page. So eight and a half, for an eight and a half by eleven. Now just set your background color for each section. Click on the Report Header, go to Format, pick whatever color you want, like this light blue, and then just do the same thing for each of these sections. All right, we're going to save it, close it, and open it back up again. And now look, same, it's to the edge of the page. If you print this as a PDF, publish it. And there we go. I mean, you still got to set the background color of all these different objects, but you get the point. You can print this color right to the edge of the page. And that looks pretty good.
So Donald, thank you very much. And if any of you guys ever find something in my videos where I say I don't know if you can do it or you even find me wrong about something, I want to know about it. I am not one of those teachers that I'm going to be like, no, I'm always right. I love learning new things, and I love it when you guys teach me new things. In fact, I think it's a bad day if I don't learn something new. So by all means, please correct me. The only time I have a problem with it is when people get like, they get haughty with me like, oh well, this isn't right. But then they end up being wrong. Yes, then I will give it to you. Then I will let you have it. If you correct me and you're wrong, it's like when grammar police try to say, oh no, it's not this is that. Well, no, you're wrong. But no, I love learning new stuff.
Next up, Tony in an email says, I have access linked to a SharePoint list from my backend tables. Occasionally users get negative primary keys, negative IDs, auto numbers. When adding records, I've researched the issue and it seems this happens when Access thinks it's offline. That is correct. I have users check to see if they can browse the internet and they can. How can I force Access to synchronize with code and how can I test it for an offline situation?
All right, let's address this. Yes, if you have access linked to SharePoint and for some reason that link breaks, which can happen with a momentary loss of internet connection, the best way to handle it is obviously to restart access. You can check for that with something like a simple DLookUp. Just DLookUp an unknown value in a table. Like on my web server, I've got a table, customers, whatever. I check for connections by just simply DLooking up my own email address from my record. I know my customer ID is whatever, three, two, two, whatever. Yes, I know I'm not customer number one on my own database. I didn't think to add myself until I already had a bunch of customers. I'm like, I'm not a customer.
Anyways, so I just look up that ID. I try to pull in my email address. If it pulls in what it expects to see, it knows we're still connected. If not, you can force Access to relink the table if you really want to. And I do cover that in this video in the extended cut. I show you how to use VBA to relink the table, all the tables in your database. And this is good too. If you've got even just a front and back end situation or you're connecting to SQL server, if it doesn't see the table, I usually just try to do it when the database loads, but you can check anytime you want. Anytime they open a form, just check to make sure the table's there. So this video's extended cut does cover that.
Got a nice email from user Matthias. He sent me a couple of different questions. I'm going to address all three of them. He says, first, what are the steps you do before releasing an Access database to the users? In your security basics video, you mentioned some, but perhaps a dedicated, these are the steps you should do before release could be useful. Well, I do have that dedicated video. In fact, I have a whole dedicated seminar to it. And this is that seminar. I walk you through all that stuff, step by step, in addition to setting up proper user and group security inside the database, all that stuff.
If I had to give you a quick list, I'd say first, test all objects in your database. You don't know how many times I've had students of mine or clients that I was helping with their stuff. They work on something new. They use the customer form and the order form. They forget to test something as simple as one of the main menu forms. So make sure you test every object in your database. Remove unnecessary objects. If you've got temporary backup forms and lots of stuff in there, get rid of all that. Compile your code, go into your VBA editor, debug compile. That's an important step because any compile error in there, if you don't test for it first, it'll cause your database to crash. And if they're using an ACCD file, which is coming up next, then that's a problem too.
Disable the navigation pane and optionally the ribbon. I turn the ribbon off. If I'm not making a custom ribbon, which I cover in my Access developer classes, then I will disable the ribbon completely. I want the users to use all of my controls and my forms that I give them. I don't like giving them access to run around in the ribbon. I even turn off the right-click menus. Choose the appropriate form of record locking. Whether it's optimistic or pessimistic, I got a whole separate video on this coming out pretty soon. Most of the time, optimistic is fine. That's where the changes don't get saved until you actually commit to the table. It depends on what you want. Compact and repair. That's a big step. In fact, you should do that weekly, if anything else.
Then you're going to split the database. Again, I got a whole video on most of these topics already. Just search my website. I'm not going to put all the links down below. You're going to create an ACCDE front end for your users so they can't go poking around in your code and in your form design and stuff. Then on each machine, each user's machine, you're going to set up a trusted location so that when they get the database and they run it, it works. You're going to have a good backup strategy. That's always important. Then very, very important is training the users how to use the database properly. You don't know how many times. I used to go into companies and I'd set them up a beautiful database and all this. Then, of course, up front, I'd try to talk them into some training.
More billable hours for me, yes. But the most important thing is if you could save people a few minutes a day because they know how to use the software properly, that's saving your company money in the long run. But if your users don't know how to use the database, well, that's a big problem. But those are the basic steps right there. Again, I have videos for all of this stuff. My security seminar covers a lot of this stuff. That would be my list if I was to put a list together of what the steps to make sure you check off before releasing a database.
The second question is how do you release new copies of your database to the users on your network? Well, that's something you can do either very simply or very complicatedly. Simply you can do it with a simple batch file, just write a Windows batch file, whatever.bat. And then just copy down the front end from the server every time the user launches it. So that they always have a fresh copy. Usually, if your network is fast, that shouldn't take very long. If your front end is, let's say, 50 or 100 megs in size, it should take a second or two.
And then every morning when they log into the database, it just pulls down the latest copy of the front end. And then that's the shortcut you give them to open the database. So the first thing it does is it copies it down and then it opens up that new version of the database. That's the simple way to do it. And of course, I have a utility that I built. I call it the Access Updater. What it does is basically the same thing, but it checks to make sure that the new version is actually new before it pulls it down.
So it'll actually go out to your server, look and see what the latest version is on the server. And then if it needs to, it'll pull down the changed version. And also my updater lets you have it run on a timer. So you can have it so that every couple hours or whatever, it'll restart the system and check for updates, all that stuff. That's good if someone leaves for the day and forgets to shut their machine down. So you can find more information on this one here.
Now as far as if the new version requires data model changes, well, I'm assuming you mean the tables. Now if you're running on a LAN, okay, if you're running on a multi-user split database network, then you just may change it in the backend. And when the database restarts, it'll see those changes in the tables. If you're talking about a database that is a single database that you're sending out to people so that they have their own copies, then it's a different set. So that's a different story. I would still recommend a split database solution because I would say nine times out of ten when you release updates, it's going to be just coding updates or front end updates, forms, reports. The tables don't usually change as much.
So if you're sending out a new database, you just send them the front end, say, hey, replace your existing front end with this one, all your data is fine. If not, if you're doing a single database solution or you have to make changes to the tables in a backend, you can do it. You can either do it with SQL or you can do it with table-defs and records sets and all that stuff. I do cover all that in my full developer course. That is likely beyond the scope of what I would cover in a TechHelp video because it is pretty complicated.
I mean, you can make basic changes, like just adding a field, that kind of stuff. Like if you forget middle name, you can add that to the customer table with SQL or with some easy code, but as far as making major changes, it's a little bit of that. That's kind of complicated. The other solution that you could do is export the data from their database. Give them an export button, which isn't a bad idea anyways. Give them a button, they can click on it, exports all their data into whatever format you like, CSV or text or whatever, or even to another Access database. And then they install the new version and then they just re-import their data. You got to be careful with the keys, make sure all your IDs and your relationships match up. But there's a million ways to do it.
And finally, question three, he's asking, how do you make Access look like a full application and not just an Access database? I actually did a two-part video on this a while ago. And it's possible to make it look like it's its own standalone application to the non-savvy non-Access user. Real users, real IT people will know it's Access. There's no way to hide it completely.
But you can make it, you can hide most of what makes it look like Access using the techniques that I show you in this video. You can set up a custom splash screen. You can maximize stuff in here. I mean, there's a lot of tricks you can play. You can't hide it completely though. People will know, an expert user will just open up the task manager and see MSXS.DXC running. And you'll have to at least have the runtime on the machine so you can't compile it. This is another common question I always get. How can I compile my Access database into a standalone EXE? You can't. Not that I know of anyways. If someone else out there has some knowledge that I don't, please learn me, because you really can't.You can give your Access database to someone, but they either have to have the full version of Access or at least the Access runtime in order to open it. Those are awesome questions, Matthias. Thank you very much for asking them. And if you have any more, send them my way. I get this all the time. Is there a way to make Access look like an app instead of running with Access? Here's another question I get a variation of all the time. Curious how you learned how to teach it? Is there some formal method you studied? No, not at all. I have zero formal education in education.
I've been a performer pretty much my whole life. I learned piano at a young age, and I was in a rock band from the time I was 16 until my early 20s. That kind of prepared me for being in front of people and performing, I guess. I started my first computer business, building PCs, servicing them, and fixing problems in the 90s. I was asked all the time, constantly, where do I go to learn how to do all this stuff, like Excel and Word? After the 15,000th person asked me that, I decided to offer training. I opened my first computer training room, which was in the dining room of my house at first. That became more popular than the actual PC servicing part of the business. I decided to focus solely on that and opened up my own training center. I had a big spot in a strip mall that could seat 20-30 people. I've been doing training ever since.
Around 2001-2002, I started getting sick of doing the same class over and over again. How many times can you teach Word basics? So I decided to bottle it and record my lessons. I put them online, and people liked it. So here I am. I have no formal training in training. I just love what I do. I think the most important thing, the number one thing, is having passion for what you do. I look forward to getting up every morning and doing this. I love making videos. I love teaching you guys stuff. I love interacting with people, even if it's only online. You have to love what you do. If you love teaching people and the topic you're teaching, I love Microsoft Access. I enjoy Excel and Word and all that stuff too. I like it, but Access is what really fires my passion. I love getting in there and building databases and stuff like that.
Love what you do, and the rest will come. It took me many, many years before I was able to actually make it a full-time job doing just training. That answers that one. I guess before being passionate about it, you have to know what you're talking about too. I did a lot of contract Access development work while I was still doing training and service stuff. I have 20-plus years of Access experience under my belt. I have taught things before that I wasn't an expert on. The key there is just to stay one step ahead of the class. I have some stories about this on my website, like Photoshop, for example. I did not know much about Photoshop. I used it here and there to touch up a picture, but then I had some people ask me, "Hey, we have 10 people that we need to train in Photoshop." "Okay, sure, I can do it." So I just stayed one step ahead of the class. The most important thing to know is not to pretend to know anything. If you don't know it, say we'll cover that in the next lesson. That's why online training is so much better because I can say we'll cover that in lesson three, and between now and then I will learn how to do it.
I could go on for hours about this.
Alright, folks, that's going to do it for Quick Queries number 28. Enjoy your weekend, and I hope you learned something today. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Setting margins to zero in Microsoft Print to PDF
Changing background color for PDF
Page Setup for specific printer in Access
Setting background color in Access report sections
Handling negative primary keys in SharePoint
Forcing Access to synchronize with SharePoint
Using DLookUp to check for connectivity
Email notification settings in Access
Testing Access database before release
Steps to release new Access database copies
Creating a Windows batch file for deployment
Using Access Updater utility
Releasing Access database updates with table changes
Exporting and importing data for table changes
Making Access look like a standalone application
Customizing splash screen and maximize options in Access
Access database runtime requirements
COMMERCIAL:
In today's Quick Queries video, we're discussing a range of interesting topics. First, discover how to print full-page PDFs with zero margins using Microsoft Print to PDF. Then, we'll talk about issues like negative IDs when linked to SharePoint, and checking connectivity with DLookUp. We also discuss steps to prepare your database for release, including testing objects, disabling ribbons, and compacting. We'll talk about simple batch file techniques for updating databases and how to make Access look like a standalone app. Finally, we'll touch on my personal journey to becoming a Microsoft Access trainer. 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 a Quick Queries video primarily composed of?
A. A single detailed topic covered in-depth
B. A series of smaller video segments
C. Interviews with guest experts
D. Live Q&A sessions with viewers
Q2. According to the video, what is a limitation of most normal printer drivers mentioned?
A. They cannot print black and white documents
B. They cannot use colored inks
C. They cannot print to the edge of the page
D. They cannot handle multiple print jobs simultaneously
Q3. What does the video suggest using if you want to print a PDF with edge-to-edge background color in Access?
A. Generic Printer Setup
B. Manual Margin Adjuster
C. Microsoft Print to PDF
D. Advanced Printer Spooler
Q4. What solution is offered in the video for checking if an Access database is still connected to a SharePoint list?
A. Ping the SharePoint server regularly
B. Use DLookUp to verify a known value in a table
C. Manually refresh the SharePoint list
D. Restart the Access application daily
Q5. Before releasing an Access database, what essential step is suggested in the video?
A. Add extra unused tables for possible future use
B. Disable error handling to improve performance
C. Compile your code in the VBA editor
D. Enable file sharing for collaborative editing
Q6. What simple method does the video suggest for distributing new copies of an Access database front end to network users?
A. Email the new version to all users
B. Use a batch file to copy from the server each time
C. Post the new version on a company website
D. Save the file on a shared folder and notify users
Q7. What type of database network setup is recommended in the video for handling updates efficiently?
A. A single-file Access database for easy distribution
B. A split database network with separate front and back ends
C. A cloud-based SQL database with manual syncing
D. A distributed database with offline mode enabled
Q8. How does the instructor suggest Access can be made to look less like a typical Access database?
A. Use a third-party skinning tool to change its appearance
B. Disable all forms of user interface customization
C. Hide ribbons, use a custom splash screen, and maximize forms
D. Compile Access into an EXE file to remove Access branding
Q9. What is a crucial factor mentioned in the video for successful teaching?
A. Having a formal education in teaching
B. Using the latest hardware and software for presentations
C. Passion for both the subject and teaching itself
D. Limiting classes to small groups for better interaction
Q10. What was the instructor's solution when he had to teach a subject he wasn't an expert in?
A. He postponed the class until he was more knowledgeable
B. He hired a guest expert to cover the topic
C. He stayed one step ahead of the class by learning material beforehand
D. He decided not to offer the course
Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-B; 7-B; 8-C; 9-C; 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, we explore a range of topics that don't warrant full videos individually. Let's get started.
Before addressing today's questions, I have an update to share: I've revised the Microsoft Access Developer Network page. This resource is ideal if you need one-on-one database assistance beyond what's covered in my videos. The page lists consultants, developers, and tutors available to guide you. I will include a link to this resource at the end of the article.
Now, let's dive into the questions from Quick Queries number 27. One of our Platinum members, Donald Blackwell, commented on how to print to the edge of the page using Microsoft Print to PDF. While typically not possible with normal printer drivers, Donald shared that by selecting Microsoft Print to PDF as a specific printer and setting the margins to zero, you can achieve this. To do this, simply go to Design View, adjust the Page Setup for a specific printer, and choose Microsoft Print to PDF. Then, on the Print Options page, set the margins to zero. This lets you create a PDF with a full background color, which typically is not feasible with standard printers. Thank you, Donald, for teaching us something new.
Next, Tony sent an email about encountering negative primary keys when accessing a SharePoint list with Access. This can happen if Access goes offline temporarily. The best solution is to restart Access and check the internet connection. Using DLookUp to verify a known value in your database can confirm if you're still connected.
Moving on, Matthias asked about steps to take before releasing an Access database. While there is a seminar on this, key steps include testing all objects, removing unnecessary items, compiling your code, disabling navigation panes and ribbons if necessary, choosing appropriate record locking, and conducting a compact and repair. It's essential to split the database and create an ACCDE front end for users. Set up a trusted location on each machine and ensure users are trained properly. Training helps users interact efficiently with the database, saving the company time and money.
Regarding releasing new database copies on a network, use a batch file to copy the front end from the server whenever a user opens it, ensuring they have the latest version. An updater utility can automate this process, checking for and downloading updates as required. For data model changes, if you're running a multi-user split setup, modify the backend and have the front-end reflect those changes. For significant table changes, consider using SQL or programmatic solutions, which I cover in developer courses.
Lastly, Matthias asked how to make Access look like a standalone application. While you can't make it appear entirely separate from Access, you can create custom splash screens and layouts to disguise it. For those looking into standalone EXE questions, remember Access needs either the full version or runtime version to operate databases.
I also received a common question about how I learned to teach Access without formal education. My background in performance and technology led to offering training as I transitioned from computer building and service work. My passion for teaching and experience with Access fuels my dedication to making instructional videos.
That covers Quick Queries number 28. For a complete tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Setting margins to zero in Microsoft Print to PDF
Changing background color for PDF
Page Setup for specific printer in Access
Setting background color in Access report sections
Handling negative primary keys in SharePoint
Forcing Access to synchronize with SharePoint
Using DLookUp to check for connectivity
Email notification settings in Access
Testing Access database before release
Steps to release new Access database copies
Creating a Windows batch file for deployment
Using Access Updater utility
Releasing Access database updates with table changes
Exporting and importing data for table changes
Making Access look like a standalone application
Customizing splash screen and maximize options in Access
Access database runtime requirements |
