Quick Queries #89
By Richard Rost
4 days ago
ODBC Insert Error with Linked SQL Server Tables
In Quick Queries #89, we address troubleshooting the ODBC insert error with linked SQL Server tables in Microsoft Access, especially involving long text fields and the recent Office Build 2604 bug. We discuss practical workarounds, differences between chained and nested queries, common issues with copy and paste in Access, using and adapting web APIs when services change, and adjusting list box column widths. We also touch on database storage best practices, working with abstraction layers in programming, compatibility between 32-bit and 64-bit Access versions, and answer several viewer questions on related Access development topics.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp QQ Quick Queries, ODBC insert error, linked table SQL Server, Office Build 2604 bug, NVARCHAR(MAX) issue, rollback Office update, ODBC driver 18 fix, nested queries, chained queries, copy paste issue, clipboard manager, swap subform Source Object, dark mode registry, runtime 2467 error, list box column widths
Intro In Quick Queries #89, we address troubleshooting the ODBC insert error with linked SQL Server tables in Microsoft Access, especially involving long text fields and the recent Office Build 2604 bug. We discuss practical workarounds, differences between chained and nested queries, common issues with copy and paste in Access, using and adapting web APIs when services change, and adjusting list box column widths. We also touch on database storage best practices, working with abstraction layers in programming, compatibility between 32-bit and 64-bit Access versions, and answer several viewer questions on related Access development topics.Transcript Are you getting that weird ODBC error in Access that makes it look like your whole database just exploded? Welcome to another TechHelp Quick Queries video brought to you by accesslearningzone.com. I'm your instructor Richard Rost.
Today we're going to talk about a lovely error that comes up with a linked table to SQL Server. It's just going to say "ODBC insert on linked table [whatever the table name is] failed." Or it might throw an "incorrect syntax near equals" message. We'll break down what's really causing it and a couple of the most common fixes, so you're not pulling your hair out thinking your database is broken. Hint, it's not.
We've also got questions from YouTube, my website forums, emails, and more, including chained versus nested queries, weird copy and paste issues, working with web APIs when services disappear, and a quick look at abstraction layers in programming.
All right, let's jump in.
Starting off today, we've got a bug running around in the latest version of Access, Office Build 2604. This one's a heads up for anyone using Access as a front end to SQL Server.
A couple of different people brought this up to me. Hell, caught one of my members ran into a problem where his Access database could no longer save new records, even though nothing changed in his code or his SQL Server back end.
It worked on another laptop, which made it look like a local machine problem at first. Alex and Darren had some good troubleshooting suggestions: checking drivers, DSNs, views, permissions - even system time can cause a problem. But then Alex pointed us to a bigger issue making the rounds right now.
Office Build 2604 appears to have introduced an ODBC bug with linked SQL Server tables, especially when long text fields in Access are tied to an NVARCHAR(MAX) field in SQL Server.
I put links down below to articles on both Developer Hut and on Access Forever, and they're aware of the problem. The Microsoft Access team knows about it. The problem seems tied to inserts.
Workarounds being discussed include rolling back to Office Build 2603. If you're not sure how to roll back your system, I got a video on that, of course. You can also try switching your ODBC driver to version 18. I know in my SQL Server class I say 19 is better, but 18 seems to fix it too.
There's also a registry setting override, which I'm not a huge fan of doing. Or you can save the record first and then update the long text field afterwards. I am a fan of doing that.
A lot of the time what I do is, with an insert, instead of inserting the whole record, you just insert the ID. Then once you have that ID, you can go back and do an update and update the rest of the fields.
But this is exactly why I don't update production Access machines the minute Microsoft says, "Hey, here's something shiny." Last update is on one machine first for a while, like a week or so. Because sometimes various fixes mean, surprise, your database isn't working now.
So be careful with those updates, folks.
Next up, we've got Colin. He's trying to dynamically swap subforms on a main form by changing that Source Object property, but he's getting error 2467: the object doesn't exist when he tries to filter or requery it right after switching.
This is a really common issue when you start swapping subforms dynamically. I show how to do that in a couple of different videos, including this one, my association series, part eight. You can see here I've got two little labels, Contacts and Demographics, and you click on this and it switches. It's like a tab control, but I like it better. It actually gives you a lot more control over what's going on here. I'm never going to be a fan of the tab control.
It's also a reason why you have to watch these little series that I do, even if you're not interested in that. Like my fitness series, there are what, seventy-some parts now, and they've all got really awesome cool stuff. It doesn't matter if you're into fitness.
Anyways, back to Colin's problem. Kevin pointed it out exactly: bang versus dot. Use bangs to reference controls or other forms, and use dot to represent properties or methods. This video does a good job of explaining it. This is one of my older videos; you can see it's before I started really caring about the quality of my title slides. Here are some different ways you can write it. You see we got the bangs in here, and we got dots for properties.
Another thing that can sometimes throw you off is timing. Sometimes when you change the Source Object, that form isn't instantly ready. So if you immediately try to requery it or set links, you can get that 2467 error.
What I would do is, maybe after you change the Source Object, put a little sleep here, a little sleep timer. Even sometimes a fraction of a second can give Access enough time to load that subform fully before you hit it with more code. Or, better yet, move that logic into the subform's load event so you know it's ready. This video will teach you how to put a little sleep timer. Just put like "Sleep 100." That'll make it pause for a tenth of a second. If that's not long enough, try half a second.
Next up, Brian asks a simple but really important question: where should you actually store your Access database files on your computer? Is it okay to put them in Program Files or Windows folders, or should they go somewhere else?
This is a great question. Not enough people think about this until something breaks. Short answer, don't put your database in Program Files or the Windows folder. Those locations are locked down by Windows for a reason, and Access doesn't always play nicely with that. In fact, you shouldn't put any data files in there.
Kevin mentioned using a trusted folder in Documents, which is fine. Sandra brought up using a separate drive, which is even better if you've got one. That way, if the C drive crashes, all you do is pull the D drive out and put it in your new computer. Your D drive could crash too, but that's why you want to have good backups.
Now, I'm actually with Donald on this one. I use a C:\databases folder for anything that I just use myself on this one machine. If I need to use that Access database from different machines, I'll share that folder from my server, which I'll map as my Z drive, which is basically the databases folder on the C drive on the server. Of course, I've got SQL Server running, so all the machines can hit that as well.
The bigger issue here, though, isn't just where you store them. It's how you protect them. If you're a regular watcher of my videos, you know I harp on backups all the time. Back up, back up, back up. I've got a nightly event that backs up my database folders, all of them, to my G drive, my Google Drive, off site, in case the house burns down, whatever. I've got Macrium Reflect, which backs up all my systems as well.
Speaking of Google Drive, don't run your database out of Google Drive, even if you're the only user. Don't run it out of a file sharing drive like OneDrive, Google Drive, Dropbox, any of those, because it can do some weird syncing in the background and it can cause corruption. Run it on a local drive or a network share. Wired network share, not wireless, and back it up to Google or Dropbox or OneDrive, but don't run it from that folder.
If you need to be able to run it from multiple computers or you want to share it with other people on your network, make sure you split it and make sure you get a front end copy of the database on your local machine, your C drive or your D drive. One of your local drives is fine. Then you connect to the back end file on your server or whatever your shared drive is.
I cover this in detail in a lot of other videos.
Brian also asked if there is a course that covers the proper protocols for development, testing sandboxes, etc. I don't really have a single course dedicated completely to these kind of setups, but throughout my entire Access course series and all my TechHelp videos, I talk about best practices: splitting databases, working in multiple environments, setting up backups, user security, etc. The further you get along into it and the further you get through my lessons, you'll just learn stuff as it comes up.
If you guys want to see me put together a single video or a single course on these kinds of development issues, let me know. If enough people want it, I'll put it together.
Speaking of wanting it, I want you to hit that like and subscribe button right now. I should put a picture like Uncle Sam up there: "I want you to click that like." Seriously, folks, if you like my videos, hit the like button, make sure you subscribe. It tells YouTube that you like my stuff and it helps my channel and it gets my videos out in front of more people. So I really appreciate it. Thank you.
Next up, Sarah was trying to follow one of my older web API lessons using World Time API, but it looks like that service has been shut down. So she's asking what to use instead and what needs to change in the code.
Yeah, this happens all the time. I did this web API video like three years ago and it was really just meant to teach the concept of how to pull any kind of data from the web in Access. World Time API was just the sample example to get the current date and time. But as APIs come and go, sites go up, sites go down, that's just part of working with web services. They change things all the time.
So if one disappears, you just swap it out for another one, adjust your URL. Maybe the data that it gives you is a little bit different, but that happens all the time.
Donald, thanks again for jumping in with a suggestion. He linked to one, which is timeapi.world. Why did .world become a thing? Anyway, they've got free API access, and Donald says it's 20,000 requests a month. Yeah, I don't think I'd need 20,000 requests a month, and it's $5 to $20 a month for more, depending on what you need. If you need more than 20,000 requests, that's a lot. What's that, like 600 a day or something?
The big takeaway here is don't get attached to any one API. Learn the technique, and once you understand how to call the API and process the data, you can plug whatever URL you want in there.
Next up, we got a question from Maradney. Maradney says, are nested queries the same as when you spoke of chained queries? Not quite. Good question, though.
Chained queries and nested queries are related, but not exactly the same. In Access, a chained query typically refers to using the output of one saved query as the input for another, while a nested query or a subquery means placing a query inside the other one, often right within the SQL statement.
Let me show you some examples.
All right, here I am in the TechHelp free template. I've got customers and I've got orders. Let's say I want to get a list of orders that are less than 30 days old. Let's see what we got in here. I've got a couple here that are from today, and the rest of these are all older.
It's a pretty straightforward query. Go to Create > Query Design, bring in the Order table. Let's say I want the Order ID, the Customer ID, and the Order Date. The criteria here will be greater than or equal to Date()-30, so it's newer than 30 days ago. I'm going to save this as RecentOrdersQ. Run it and that's what I got.
Now that's one query and that's all set. Now, let's say I want another query that takes the customer's first name and last name and shows that order date, but only from the recent orders. This is where I can chain another query.
I'll make a new query: Create > Query Design. I'll bring in the Customer table. Then I'll bring in RecentOrdersQ. You can see they're joined on CustomerID because Access saw those two fields.
I want to bring in, let's say, FirstName, LastName, and then OrderDate from over here. Now when I run it, there we go.
Here's the RecentOrdersQ output: we've got the OrderDate, CustomerID, and OrderID. So this gets chained or fed into this one. This one has to generate first, and then you can generate this one.
But what if you don't want to have two separate queries? You can do this instead. Now, this is more advanced. Normally you see people doing this on SQL Server and stuff, but it's a good skill to learn.
Go right to SQL View, and I'm going to zoom in so it's easier to see. Here's the SQL, let's clean this up. We've got FirstName, LastName, OrderDate, all right, from CustomerT. Here's our INNER JOIN RecentOrdersQ on CustomerID, CustomerID.
Now, instead of relying on a RecentOrdersQ, we can just put another SQL statement right here. We don't even need that RecentOrdersQ. We'll put a parenthesis here and then a parenthesis here.
Inside here, we're going to put:
SELECT OrderID, CustomerID, OrderDate FROM OrderT WHERE OrderDate >= Date()-30
This is exactly the same thing that we had in that other query, but now I'm going to stick it in here. I'll call this AS RecentOrdersQ.
So this is all inside the second query now. In fact, watch this: I'm going to delete this guy. So now that query doesn't even exist. We're going to call it RecentOrdersQ in here. Now when I run this Query1, the new guy, look at that, it works. It doesn't need to call a second query because it's nested inside of this one. This query doesn't technically exist; it's just SQL inside here. That's the cool part of learning SQL, the nested query.
Now, the benefit of nested queries isn't just reduced clutter in your navigation pane, although that is one good reason. Honestly, when I'm teaching beginners or even intermediate level students, I usually prefer chained queries because they're easier to design, they're easier to read, they're easier to reuse, they're easier to debug. You can go back through the steps of the query. If it takes five queries to chain to get your results, it's a lot easier to work through than one big nested query, which can be a lot harder to read.
The tradeoff is definitely readability. Nested queries can get ugly pretty fast. That's why in Access, while you're learning to build this stuff, chained queries are the better choice. I focus mostly on chain queries throughout my entire course until we get up into the advanced SQL stuff.
Think of it this way: chain queries are more modular and easier to work with. Nested queries are compact and self-contained. Same results, just a different style, really.
Normally, what I do - honestly, me personally, and I've been writing this stuff for 30 years - I will usually start with chaining queries together. Then once I get it working perfectly, and I want to move it up to SQL Server or something, I'll take the whole thing and I'll write it all as one big nested query and see if that works on SQL Server. I'd say about half the time it works fine.
But there's nothing wrong with chaining queries together. This is like fancy stuff. It's like those programmers who like to make their code as fancy looking as possible. I prefer readability. To me, chaining queries, multiple queries together, is often a lot easier to design and easier to maintain later on. Either one works.
Great question, though. Thank you. I hope this helps someone.
All right, heading over to YouTube. We've got B-Jones. He said, "Claude code can give you a little function that looks at one registry entry to grab the dark mode status from Windows." This is about my dark mode video that I did, where I show you how to switch your Access database to dark mode, which basically just changes the color palette of all your controls and stuff over to a darker version. I use dark mode all the time myself. It's wonderful.
As far as getting that registry entry from Windows, that's absolutely correct. That's actually something I covered in my full developer course. Here's the dark mode TechHelp video, where I show you how to do it with Access, and then we get deeper into it in Developer 55. I'm working on more with Developer 56 right now.
If you want to detect the Windows setting for dark mode, that's relatively easy to do. Like B-Jones mentioned, you just read a registry key.
The tricky part is Microsoft Office - that Office background, getting Word and Excel and PowerPoint and all the Office apps to follow along with your dark mode. Office does its own thing and there are roaming profiles and it's all crazy. But I'm going to cover it in Developer 56 pretty soon.
And yes, I use AI all the time. I ended up testing solutions from, well, I did my own Google research first. Then I asked GPT, Gemini, Claude, kind of piecing it together from all three of them. Between the four of us, we got it working.
But I do always give a quick word of caution to people. AI is great for helping you get ideas or snippets of code, but don't just copy and paste all that stuff into a production database and call it a day. You've got to understand what it's doing.
All these people doing vibe coding - I've done some videos on vibe coding. A lot of the time, you have to understand what it is you're putting in your software because if it breaks and you don't know how to fix it and the AI is like, "I got no idea," then you're stuck.
How many times did the Enterprise go to a planet where the people were, "We don't know how anything works," and then the technology breaks and now the people are screwed because the folks who designed the technology have been dead for hundreds of years? Now, Geordi's got to come in and fix everything for them. Prime directive be damned.
So use AI, absolutely, but use it responsibly. Learn what the code does. Otherwise, you're just building something that you won't be able to fix later, and that never ends up well.
Next up, Richard (cool name), says that in Access Design, copy and paste barely works. He has to hit copy a bunch of times before anything actually pastes, even using the keyboard.
Yeah, I have this problem myself. You'll see it in some of my videos. Honestly, it's not Access itself. Nine times out of ten, it's something messing with the Windows clipboard. Clipboard managers, remote desktop sessions, even some background utilities can interfere with it.
I've found that if I turn off the Windows clipboard manager, where you can have multiple items on your clipboard, if you turn that off, it works better. But I don't want to turn that off because I like it, so I just kind of deal with the fact that the copy and paste is weird sometimes. You'll see me get frustrated in some of my videos too.
But yeah, that's just one of those annoying system level quirks. It's not really an Access bug, but it definitely shows up there to annoy all the time. So turn off your clipboard managers, turn off Windows clipboard sharing, turn off all that stuff, and you should see the problem go away. But sometimes the cure is worse than the disease.
You don't have to be a member to ask a question, that's fine. If it's a good question, I'll include it in a video. Members do tend to get preference. If you're a Platinum member, I'm more likely to make a full TechHelp video out of it for you. But I answer questions from everybody all the time. You don't have to be a member.
Next up, Luis, hold on a second. He says, "Hello. I'd like to know why you don't include Spanish tracks." I do. I do include them. That's a YouTube thing, it's not a me thing. I've got all the languages selected on, as a creator. I go into one of my videos and I've got languages, I got them all selected, all the ones I can select. Now, see how it says here, like Hebrew is processing, Hindi is processing, they don't all turn on right away. Punjabi is processing.
Spanish - down here, Spanish, see? Published. So you might just have to wait for YouTube to generate it because they've got to do all their magic behind the scenes. So if it's not showing up yet, you have to wait. I got them all turned on, every single language that YouTube has available. So don't yell at me, yell at YouTube.
Oh, and Secret Club, I want you guys to know that those of you who said mulberry after last week's quick queries, I hear you. If you don't know what this means, you'll have to watch the whole thing. Quick Queries 88. Go back and watch it.
This one was kind of funny. Saad said, "The video is so long, I hate long videos because I'm Gen Z, but it works for me. Finally, thanks, Uncle." Hey, fair enough. You're welcome, nephew, I'll take the win. If it works, it works.
Yeah, I get it. The Gen Z TikTok era, 30 second videos, quick hits, boom, done. I watch that stuff too. It's great for quick tips, little life hacks, history clips, science news, all that kind of stuff. But when you're actually trying to learn something, especially something like networking or database development or fixing a real problem, there's just no way to do that in 30 seconds.
I can't make a full TechHelp video in three minutes, unless it's one teeny tiny little thing. I used to try doing fast tips, but I always felt like that's not enough. So, yeah, my videos are longer, but the goal is you walk away actually understanding it, not just clicking buttons hoping for the best.
So, hey, if it worked, it worked. I did my job, but stick around for some of my deeper dives and maybe you'll learn some more stuff. I'm going to post a note on your comment that I'm going to talk about this in today's Quick Queries video, but we're already up over 21 minutes, so I know you're probably not going to watch this. So, it was nice knowing you.
That's a quick family reunion right there.
Next up, Samantha's asking how to adjust the column widths in a list box after it's already been created. That's a nice easy one. Just open the form in Design View, click on your list box, go to the property sheet, look for "Column Widths" under Format, and then you can set the columns there. Let me show you an example.
See, here I've got Access in dark mode. You like that? Let's say on my main menu here, I want to throw a list box. So I'll get a list box, drop it right here. We're going to look up the values for table or query; let's get a list of customers.
Next, I want the CustomerID, the FirstName, LastName, and EmailAddress. Next, sort it by, I don't know, LastName. Next, okay, now you set the widths here in the wizard. Just like this, you make them as wide as you want. Next, what label? We're going to delete it anyway.
Here's my list box, there's the label. Let's get rid of that label. Now, open this up a little bit so we can see it. I'm going to save this, close it, and reopen it. There's my list box with all my customers in it.
Now, let's say that the second column here isn't wide enough. You want to make this wider, so we'll go back to Design View. We're going to open up the properties for this guy, double click on it, and on the Format tab, you'll see Column Widths right there.
Unfortunately, I can't easily zoom in here to show you this better. You can't Shift+F2 in these property fields. Not all of them let you. You can Shift+F2 in some of them, like if you go to Data > Row Source, you can Shift+F2 on that one. Why you can't Shift+F2 on this one, I don't know, but you can't.
So I'll just copy this over to clipboard to Notepad so you can see it better. That's what's in there. It's zero inches because that's the first column. It's hidden. That's your ID. That's the bound column. That's what the value of that list box actually is: that CustomerID. Then we got FirstName, that's 0.67 inches. LastName, this one I want to make wider. And then the EmailAddress.
So if you want this one to be wider, let's go 0.6 for this one, 0.9 for this one, and we'll go 2 for this one. Actually, let's keep these all even. Let's go 1, 1, and 2. So we have 4 inches total. I'm going to copy this, stick it back in here.
Now the issue is now we're going to be too wide. So if I save this, close it, open it, look, we're too wide now. We're scrolling right, see? So because these got wider, you're going to have to make your list box wider.
If you want to make sure it's exactly as wide as you need it to be, find the width of this guy, which is right here, that's the Width property. Make sure this is equal to the sum of that. Otherwise, you're going to get a scroll bar. So put 4 in there. Now that's going to widen it out on your form. Even now, you know it's wide enough to fit that data. Save it, close it, open it, and there you go. You've got no horizontal scroll bar now. See? So that's how you adjust the widths in here.
Oops, sorry about that. See? Windows Update. I got Windows Updates turned off because I like to manually Windows update. That's a whole topic for a whole other video. But I don't like it when Windows is like, "Oh, we just rebooted your machine last night at 4 a.m. Hope you like it," when I've got a million things I need to do this morning and I don't have time for problems that sometimes show up. So I've got it turned off, and you have to registry hack it to not make it update, but it still pops that up. A little message in the corner says, "Hey, we got an update for you." No thanks. Trying to record a video. Don't need your interruptions.
But Samantha, hope that helps. I cover that in a lot more detail in my beginner classes, all right, list boxes and stuff.
Learning to Ranch says I'm a great teacher. Thank you very much. I appreciate that. The lesson helped you get past a hurdle you've been trying to learn for several weeks. I wish I had checked here first before I wasted my time. Next time I'm coming here first for sure.
Yes, come to my website, come to my YouTube channel. Here's the thing I tell everybody: if you search for something Microsoft Access related, whether you're on Google or YouTube or Bing or wherever you search, and one of my videos doesn't show up, let me know. I'm trying to hit all the popular keywords with my videos. If it's something that you think more than one other person would search for, let me know. But yeah, I want to hit those gaps. So if you don't find my video or at least a good video from anybody that answers your question, let me know about it. Tell me where you were and what exactly you were searching for, and I'll look into it. But I certainly appreciate the praise. Thank you very much.
Next up, JC is talking about how Access sits on top of C++, which sits on top of lower-level languages, and it's all just layers of abstraction. This is a really cool concept and it's worth understanding. Access isn't some lesser tool, it's just sitting on top of other tools. Access, VBA, all of that eventually gets translated down through layers. Access was built with C++. C++ was built on older languages, assembly, and finally, that all gets written down into machine code, which is just ones and zeros if you think about it.
So every layer is there to make life easier, and you don't have to think about registers and memory addresses and binary math, you just say, "Hey, open this form, run this query," and Access handles the rest. That's really the point of software evolution. Each layer hides complexity so you can focus on solving your real problem, whatever it happens to be. The best developers aren't the ones writing everything in assembly, they're the ones using the right level of abstraction to get the job done efficiently. At the end of the day, it is all just ones and zeros, but we keep building better and more efficient ways to talk to them.
Now, it all depends on what you're trying to do. If you're building a database, you want to use Access or SQL Server, which will then compile down and convert it all that way. If you're building a video game, you want to use a lower-level language like C++ because you get more control over the hardware. You can talk directly to the registers and all that stuff, but you have to know what you're doing. You can build a database without having to understand programming in C++ and that's why I like Visual Basic, because Visual Basic is easy to understand as a non-programmer. You can be someone who isn't a programmer and still write some effective code with VB because the abstraction layer is there.
Message Box, MSGBOX, that one command, lets you put a little window on the screen and lets the user click Yes, No, Cancel, OK, whatever, and you don't have to know a whole lot to do that. In the old days when I used to program in C, that was a lot of work just to put a window on the screen.
That's how tools are. Now we've got another abstraction layer on top of everything, which is AI, which can write all that stuff, but take into consideration that you should really understand what it's doing.
So yeah, very cool comment. Thanks.
Next up, Fleet says when their office moved to Windows 11 and newer 64-bit setups, they kept running into compatibility issues with older 32-bit Access apps and had to maintain separate versions just to keep things working.
Yeah. So a quick background, if you missed last week's video: this usually happens when you've got a brand new machine with 64-bit Windows, 64-bit Office already installed, and then you try to install an older Access database that was built in a 32-bit environment. Those two things don't play nice together, especially if there are compiled components or references involved. That's where you get those nasty install errors that I talked about last week.
Fleet (or Ferris, whichever one you want to call me) is exactly right. The whole transition was messy. For years, Microsoft was basically saying stick with 32-bit, you're good, unless you really, really need 64-bit (you know, people with the gigantic Excel spreadsheets that make sheets bigger than they ever should be). That's what everybody did, myself included. I kept all my stuff 32-bit. Then all of a sudden, it was like, "No, everything's going to 64-bit now, so we'll go and upgrade." Now I was like, "What?"
Meanwhile, I've got years of stuff built on 32-bit, and then you had to have code that worked with both with the VBA pre-compiler directives and all that stuff. A lot of developers ended up doing exactly what you did: keeping separate versions to keep things running. It wasn't pretty, but it was reality at the time.
In a perfect world, 32-bit would have just rolled right into 64-bit, and nobody would have had to think about it, but here we are. So today, the best advice I can give is to just pick a lane. Standardize on 64-bit going forward, update the older stuff when you get the chance and just fingers crossed. If you have an old, old, old database that's still 32-bit, fix it. Find a consultant to fix it for you, that's about all I can say.
I've done some videos in the past, and I mentioned them in this video, about how to convert your 32-bit stuff over to 64-bit. Here it is. Hard to believe this one's five years old already, but it's one of the first TechHelp videos I did. You basically have to go through your code and just do this to it, mostly, all right. If you start seeing errors like this, then you have to start doing this stuff. It's a lot of fun, trust me. I had a whole lot of fun doing this with a whole lot of databases.
If you haven't done it yet, hit that like and subscribe for me. Be sure to stop by my website and see what's new, what's going on. You've got a report columns video here. Alex released a VB form builder, that's pretty cool. The dark mode stuff, Access Developer 55, and of course, the captain's log. You always stop by my captain's log. Check stuff out there. Always try to release some interesting articles. A lot of them are like me in Starfleet uniforms, but, you know, check it out.
Stop by my merch store, pick up a t-shirt, mouse pad, all that stuff. I'm trying to get them to do penguins. I sent them an email; I said, "Hey, you guys have plushy bears and rabbits and stuff. I want plushy penguins." So we'll see what they say. If you start seeing me promoting penguins, that's why.
Pick up a copy of my book on Amazon. Stop by the forums on my website. If you've got questions, folks, that's the best place to post it: on the forums. I can only do so much with the YouTube comments. If they're good ones, I'll try to include them in a Quick Queries video. Most of the time, it's just me doing those, so I run down them and hit them quickly as I can.
But the forums on my website: lots of great moderators, other students, lots of people help on my website, so check it out. If you need help, need a consultant, need a developer, need a tutor, check out my developer network.
So today we covered how to work around that ODBC insert bug with SQL Server. We talked about when to use nested queries versus chained queries, a few quick fixes for clipboard glitches and handling web APIs when services go down, and lots more.
Let me know how you like today's video and post any questions you want to see me cover next week down below.
That is going to do it for your Quick Queries video for this week, brought to you by accesslearningzone.com. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
Yeah, we've got a special weekend edition this week. Things get busy. Life happens. I try to get Quick Queries done for Fridays, but I just had a crazy busy week. It's a special Sunday edition, Sunday evening edition too. It's late on Sunday, but hey, better late than never.Quiz Q1. What recent bug affects Access users working with linked SQL Server tables?
A. ODBC bug related to Office Build 2604 and inserts
B. An error in the Access database engine
C. Access fails to open MDB files
D. SQL Server no longer accepts connections from Access
Q2. Which combination is known to cause the ODBC insert error mentioned in the video?
A. Text fields mapped to integer fields
B. Long text fields in Access mapped to NVARCHAR(MAX) in SQL Server
C. Date fields mapped to Boolean types
D. Image fields mapped to CHAR(20)
Q3. What is one workaround for the ODBC bug affecting linked tables in Access?
A. Rolling back Office to Build 2603
B. Switching to ODBC driver version 12
C. Converting all text fields to integers
D. Uninstalling SQL Server Management Studio
Q4. What does error 2467 usually indicate when dynamically swapping subforms in Access?
A. The field name is misspelled
B. The subform control does not yet exist or is not ready
C. The table does not have a primary key
D. The database file is corrupted
Q5. What coding syntax should you use to reference a control or another form in Access VBA?
A. Square brackets
B. Dot notation only
C. Bang (!) notation
D. Curly braces
Q6. What can help avoid timing issues when changing a subform's Source Object in Access?
A. Restarting Access
B. Adding a short sleep/pause or handling code in the subform's load event
C. Recompiling the database
D. Only using continuous forms
Q7. Where is the best place to store your Access database file on your computer?
A. Inside the Program Files folder
B. In the Windows system folder
C. A local folder like C:\databases or a trusted folder in Documents
D. On the root of your network's sysadmin drive
Q8. Why should you avoid running an Access database directly from Google Drive, OneDrive, or Dropbox?
A. Networked folders are slow
B. These services can cause file corruption due to background syncing
C. They are not supported by Microsoft
D. The files become read-only
Q9. What should you do to share an Access database with other users on your network?
A. Send copies of the frontend file and connect to a shared backend
B. Let everyone edit the same file on Google Drive
C. Store the database in Program Files
D. Use only the Access web app
Q10. What is the main conceptual difference between chained queries and nested queries in Access?
A. Chained queries are faster than nested queries
B. Chained queries use multiple saved queries, nested queries embed SQL inside SQL
C. Nested queries cannot be used in Access
D. Chained queries only work with macros
Q11. What is a benefit of using chained queries, according to the video?
A. They always run faster
B. They are easier to design, reuse, and debug for beginners/intermediates
C. They use less memory
D. They are required for all reports
Q12. Why is reliance on a specific web API discouraged in Access development?
A. APIs are always owned by Microsoft
B. Web APIs can change or disappear, so you should learn the general technique
C. All APIs are unreliable
D. Web APIs only supply numeric data
Q13. How can you adjust the column widths of a list box in Access after creation?
A. It is not possible to adjust after initial creation
B. Change the font size instead
C. Open the form in Design View and update the Column Widths property
D. Run a VBA script each time you open the form
Q14. What should the total Width property of a list box match?
A. The height of the tallest label
B. The sum of its column widths
C. The width of the first column
D. The number of rows in the list
Q15. Why are abstraction layers useful in application development?
A. They reduce the number of required computers
B. They hide complexity and allow focus on solving actual business problems
C. They slow down application performance
D. They are only for web apps
Q16. What is a common cause of copy and paste issues in Access Design View?
A. Access memory leaks
B. Interference from Windows clipboard managers or remote desktop sessions
C. Faulty Office installation
D. Invalid form design
Q17. What is a key piece of advice when using AI-generated code in Access?
A. Always copy and paste directly
B. Never use code from AI
C. Understand what the code does before using it in production
D. Only rely on one AI model
Q18. What compatibility issue is often faced with Access when upgrading to 64-bit Office?
A. Data types not supported
B. 32-bit Access apps may not run or need separate versions/updates
C. 64-bit Office cannot save files
D. Old reports do not print correctly
Q19. For effective database development and backup, what is recommended in the video?
A. Store all files in the cloud only
B. Have regular, automated local and off-site backups
C. Print all reports nightly
D. Use Access macros exclusively
Q20. What is the instructor's advice about updates for production Access systems?
A. Always update immediately
B. Only update once a year
C. Test updates on a separate machine before rolling out to production
D. Ignore all Microsoft updates
Answers: 1-A; 2-B; 3-A; 4-B; 5-C; 6-B; 7-C; 8-B; 9-A; 10-B; 11-B; 12-B; 13-C; 14-B; 15-B; 16-B; 17-C; 18-B; 19-B; 20-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 from Access Learning Zone, I want to address a variety of questions and common issues that have come up lately, including a particularly vexing ODBC error, subform troubleshooting, database file storage best practices, APIs, differences between chained and nested queries, clipboard nuisances, language track issues, sizing list box columns, abstraction layers, compatibility with 32-bit and 64-bit Office, and a few more topics.
To start, let's talk about a recent ODBC error that has been showing up for many Access users who link to SQL Server tables. If you've encountered the error "ODBC insert on linked table [TableName] failed" or "incorrect syntax near equals," it's not that your database is suddenly broken. Instead, it's tied to a bug introduced with Microsoft Office Build 2604. This seems especially common when Access long text fields are linked to SQL Server NVARCHAR(MAX) fields. Microsoft and several blogs have acknowledged the issue, and the main workarounds being suggested are rolling back to Office Build 2603 or switching your ODBC driver to version 18, even though version 19 is generally recommended for other reasons. Some people also save their records first, then update the long text fields afterward, which usually avoids the problem. You could also try a registry override, although I generally don't recommend messing with the registry if you can avoid it. The key point is, this kind of bug is exactly why I never update production Access machines immediately when Microsoft releases something new. Always test updates on a single PC for a while, just in case.
Next up is a question from someone who tried dynamically swapping subforms on a main form using the Source Object property, only to run into error 2467 ("the object doesn't exist") when trying to filter or requery the new subform immediately. This issue is extremely common when replacing subforms on the fly. The error usually comes down to timing, as Access might not have finished loading the subform before you try to interact with it. After changing the Source Object, it's often helpful to pause code execution for a fraction of a second with a Sleep statement, or better yet, move any initialization logic into the load event of the subform itself. Another related pitfall involves the use of the bang and dot operators in referencing controls and properties. Use the bang (!) to reference controls or forms and the dot (.) for properties and methods.
Moving on, I got a great question about the right location to store your Access database files. The short answer is, don't place them in system folders like Program Files or Windows, as they are locked down and may cause Access not to function correctly. It's safest to use a dedicated data folder such as C:\databases, or even better, a separate physical drive if you have it. If you're sharing the database across machines, put the back end on a network share or server, but always keep the front end local for each user. And absolutely make sure you have strong backups in place - locally and off-site - because things go wrong more often than you'd like. Also, don't run your Access database directly out of a cloud-sync folder like Google Drive, OneDrive, or Dropbox. That can result in weird syncing issues and even file corruption. Back up to the cloud but work from a local or wired network location. I talk about these best practices throughout my various Access courses and videos, though not in a single course dedicated to setup topics. If enough people are interested, I'll consider putting one together.
Another question came in about web APIs. Sometimes when I demonstrate using a public web API, such as World Time API in previous lessons, those services eventually go offline. If that happens, just substitute another time API and adjust your VBA code accordingly. The important thing is to understand the technique: once you're comfortable making API calls from Access, it's just a matter of changing URLs and working with slightly different data formats.
There was also some confusion between nested versus chained queries in Access. A chained query is where you use the output of one saved query as the input for another - essentially building queries in stages. A nested query, or subquery, involves placing one SQL statement directly inside another, right in the SQL View. While nested queries can help minimize clutter in your Navigation Pane, chained queries are often easier to build, read, debug, and maintain, especially when you're starting out. For advanced scenarios or migration to SQL Server, nested queries can be worthwhile, but for most users, chained queries are the most practical approach.
Another user wrote in about problems with copying and pasting in Access Design View. Sometimes, copying a control or field requires hitting the copy command multiple times before it will paste at all, even with keyboard shortcuts. In most cases, this comes down to interference from clipboard managers or utilities in Windows that affect how the clipboard works. If you turn off the Windows clipboard manager and any other clipboard utilities, the issue usually disappears, but then you lose those extra clipboard features. It's a bit of a tradeoff and a quirky frustration for many users with design elements in Access.
Some people have asked about subtitles or tracks in other languages for my videos, like Spanish. I do enable every language YouTube offers on my channel; however, it sometimes takes YouTube a while to process and make those tracks available. Once they're finished, you'll see them in the options.
There was a question about resizing columns in a list box after it's been created. To do that, open your form in Design View, select the list box, and adjust the Column Widths property in the property sheet under the Format tab. Set the width for each column, remembering that the first column might be hidden (width zero) if it's a bound field like an ID. Make sure the overall width of the list box matches the sum of the column widths if you want to avoid a horizontal scroll bar. If you make the columns wider than the list box, just increase the Width property.
Some users have commented that my lessons help them overcome long-standing hurdles (thank you, I appreciate the positive feedback!). If you search for Access topics and don't find my videos coming up, please let me know - I want to cover every useful keyword and fill in any content gaps people are searching for.
Every so often, a comment pops up about abstraction layers in programming, and it's an important point. Access itself is built on C++, which sits on even lower-level languages all the way down to machine code. Modern database tools, VBA, and Access exist to let you focus on your actual work without worrying about the details of computer hardware. Every new abstraction layer is a leap in productivity, so don't think of Access as a "lesser" tool - choose the right level for the problem you're trying to solve.
Another compatibility challenge involves running 32-bit Access databases on 64-bit Windows and Office, particularly with older code or compiled controls. Ideally, everything would transition smoothly, but often it requires maintaining separate 32- and 64-bit versions or revising code using conditional compilation. If you're starting new Access projects, stick to 64-bit moving forward and update legacy apps as soon as feasible.
Don't forget to check out the discussion forums on my website for even more support from students and moderators. That's always the best place to get detailed answers. You can hire a consultant or a developer through my developer network if your situation is particularly tricky. Also, check out my latest videos, articles, and even my merchandise in the online store. Stay up-to-date and make use of all the resources available.
That covers the main topics for this week's Quick Queries: the current ODBC bug and workarounds, understanding chained versus nested queries, working with APIs that disappear, clipboard issues, handling subform swaps and property referencing, language support, resizing list box columns, abstraction layers, and 32- versus 64-bit compatibility.
You can find a complete video tutorial with step-by-step instructions covering everything we discussed here on my website at the link below. Live long and prosper, my friends.Topic List Diagnosing ODBC insert errors on linked SQL Server tables
Issues with NVARCHAR(MAX) and Access long text fields
Workarounds for Office Build 2604 ODBC bug
Rolling back Office updates to fix Access bugs
Switching to ODBC Driver 18 for SQL Server
Using registry overrides for ODBC issues
Splitting insert and update for long text fields
Safe locations to store Access database files
Importance of database backups and strategies for backup
Dangers of running Access databases from cloud sync folders
Splitting Access databases for multi-user environments
Front end and back end Access database design
Replacing deprecated web APIs in Access VBA
Techniques for swapping list box API sources
Chained queries vs nested queries in Access
Examples of chaining queries for modular design
Writing and running nested (sub)queries in SQL View
Readability tradeoffs between chained and nested queries
How Access/VBA relates to C++ and abstraction layers
Design View: Adjusting column widths in list boxes
Working around Windows clipboard issues with Access
Detecting Windows dark mode status in Access
Troubleshooting copy and paste problems in Access forms
Compatibility issues between 32-bit and 64-bit Access
Strategies for migrating legacy Access apps to 64-bit
Selecting API endpoints when services change
Avoiding clipboard manager conflicts with Access
General troubleshooting tips for Access environmentsArticle If you are working with Microsoft Access as a front end to SQL Server, you might encounter some frustrating ODBC errors. For example, you may see messages like "ODBC insert on linked table failed" or "incorrect syntax near equals." These errors can appear suddenly, even if nothing has changed with your code or backend, especially after automatic updates to Office. Here's what's really going on and how you can fix it.
Recently, an Office build - specifically Build 2604 - introduced a bug involving ODBC linked tables, particularly when you have Access long text fields linked to SQL Server's NVARCHAR(MAX) fields. The error tends to show up when you perform INSERT operations on the linked table, preventing new records from saving. One common clue is that it works on one machine but not another, making it look like a localized problem, but it is often due to the Office build version.
Troubleshooting from the community points to several solutions. You can try rolling back Office to an earlier build, such as 2603, which does not have this bug. If you do not know how to roll back, plenty of tutorials are available online. Another workaround is to change your ODBC driver to version 18 if you are currently on 19. Although 19 is newer, 18 can sometimes resolve these types of problems. There are also registry settings that some recommend changing, but this approach is riskier if you are not comfortable editing the registry. Alternatively, you can save a blank record first, then update the long text fields separately. In VBA, that might look like inserting just enough fields to create a record, capturing the new record's ID, and following up with an UPDATE for the long text column.
This situation highlights the importance of not updating production Access environments the moment Microsoft releases a new update. It is better to test updates on one machine for at least a week before rolling them out, as unexpected bugs can bring your database work to a halt.
Another common issue Access users face is error 2467, which states that an object does not exist. This can happen when you programmatically switch a subform's Source Object on your main form and then try to immediately reference or requery it. The root cause is that Access does not always load the new subform instantaneously; if your VBA code tries to requery, filter, or link the form before Access has fully loaded it, an error results.
To handle this, you have a couple of options. One simple trick is to introduce a brief pause using VBA's Sleep function after you change the Source Object, letting Access catch up. For example, you can do this:
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
' After setting the SourceObject property
Sleep 100
That line halts execution for a tenth of a second, giving Access time to finish loading the new form. If 100 milliseconds is not enough, try increasing it to 500. An even better approach is to move your initialization or requery code into the subform's Load event, which guarantees the form is ready before any further actions are taken.
Another topic that comes up often is where you should store your Access database files. Avoid putting your .accdb files in folders like Program Files or Windows, as these are protected by Windows security and can create permission headaches. Stick with a folder like C:\databases or a location in your Documents folder, which you can make a trusted location. If you have a separate internal drive, that's even better, as it offers an extra level of redundancy if your primary system drive fails.
For multi-user setups or if you need to share the database across a network, do not run the database from cloud-synced folders like OneDrive, Google Drive, or Dropbox. Background syncing can corrupt the file. Instead, run your database from a local or network share, and use these services strictly for backups. Always back up your database regularly, ideally with nightlies to both a physical and a cloud location.
If you plan to share your Access database between multiple users, it is essential to split your database. The backend file (containing your tables) should reside on a server or network share, while each user should have a copy of the frontend (containing forms, queries, reports, and code) on their local computer.
When it comes to handling APIs in Access - such as grabbing the current time from an online API - be aware that free API services can go offline or change terms with little notice. If your code points to a service like World Time API and it disappears, just find another similar API, update the web request URL, and adjust your parsing code if the response structure is different. The takeaway is: do not get attached to any one web service for your critical automation. Structure your code so you can easily swap APIs as needed.
On the subject of queries, there is a frequent question about the difference between chained queries and nested queries. In Access, a chained query is when you use the output from one saved query as the input to another. For example, say you want a query called RecentOrdersQ that returns orders from the last 30 days:
SELECT OrderID, CustomerID, OrderDate FROM OrderT WHERE OrderDate >= Date()-30;
Then, you make a second query that uses RecentOrdersQ and links it to the CustomerT table so you can pull out customer names for just those recent orders. This approach is modular and easy to manage - you can test or troubleshoot each step independently.
A nested, or subquery, is when you write one SQL statement inside another directly. Using the previous example, you could replace the join to a saved query with the subquery itself:
SELECT CustomerT.FirstName, CustomerT.LastName, q.OrderDate
FROM CustomerT
INNER JOIN
(SELECT OrderID, CustomerID, OrderDate
FROM OrderT
WHERE OrderDate >= Date()-30) AS q
ON CustomerT.CustomerID = q.CustomerID;
The result is the same, but you don't see the intermediate query cluttering up your navigation pane. However, debugging and maintaining nested queries can be trickier. For beginners and when learning, chained queries are often easier to read, test, and reuse. More advanced Access or SQL developers sometimes favor nested forms for efficiency or code portability.
A frequent design question concerns how to change the column widths in an Access list box after it is created. To do this, open your form in Design View and select your list box control. With it selected, open the property sheet, go to the Format tab, and find the "Column Widths" property. You can manually enter a comma-separated list of widths, in inches (or centimeters if your regional settings are metric). For example:
0;1;1;2
This would make the first column hidden (0 inches, typically the bound ID), and the next three columns would be 1, 1, and 2 inches wide, respectively. The total width should not exceed the width of the list box itself, or you will get a horizontal scrollbar. To adjust, also update the "Width" property of the list box control so it matches the sum of your column widths.
If you want to quickly view or edit these widths in a large property entry, you might need to copy the value out to Notepad and adjust it there, since Access doesn't always support the expanded property editor for every field. Don't forget to widen the list box to avoid scrollbars.
You might also encounter clipboard problems in Access forms or design mode, where Copy and Paste is unreliable. This is often not an Access bug but is caused by Windows clipboard managers, remote desktop connections, or other clipboard utilities that interfere with Copy/Paste. Disabling the Windows 10 or 11 clipboard history, or shutting down third-party clipboard managers, usually solves the problem - though it may be a trade-off if you depend on those clipboard features.
Another topic worth mentioning is how abstraction layers work in software development. Access itself is written in C++, which in turn is built atop lower-level languages like assembly, which finally translates down to binary machine code. Each layer is designed to hide some complexity from you, making it easier to focus on the business problems rather than technical minutiae. There is a balance to strike: higher-level languages and tools like Access or VBA make some things easier but offer less low-level control. For most business development, it is best to focus on getting the job done efficiently, not by working at the lowest possible layer.
Many people run into trouble upgrading from old 32-bit Access applications to new 64-bit Office and Windows environments. Old databases, particularly those with custom add-ins or compiled components, can be incompatible and produce errors. If possible, standardize on 64-bit Office going forward and update or refactor old code with the required changes (such as using "PtrSafe" declarations in VBA). If repairs seem overwhelming, you may need to seek expert help to bring the code up to date.
For Windows dark mode detection in Access, you can check the appropriate registry key. In VBA, the method to read the registry is straightforward. For example:
Dim WshShell As Object
Set WshShell = CreateObject("WScript.Shell")
Dim darkMode As String
darkMode = WshShell.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Themes\Personalize\AppsUseLightTheme")
If darkMode = "0" Then
' Dark mode is enabled
Else
' Light mode
End If
Bear in mind that reading the Office dark mode settings is a different topic, as Office stores its theme info elsewhere and sometimes via roaming profiles.
In summary, Microsoft Access is a versatile development tool sitting atop many abstraction layers, and it's important to be aware of issues that can affect workflow: be cautious with Office updates, use chain queries for clarity, avoid storing databases in protected or synced folders, back up regularly, and be cautious using online APIs. If you encounter compatibility issues or errors, look for the simplest solutions first, such as downgrading updates or switching ODBC drivers, before considering more complex registry or code changes. And as always, keep learning and stay proactive with backups and best practices to minimize surprises. |
