Chatbot With Memory
By Richard Rost
4 months ago
Create a Chatbot That Remembers Conversations
In this Microsoft Access tutorial, I will show you how to build a chatbot with memory by connecting your Access database to OpenAI and allowing it to remember ongoing conversation threads. We will cover how to modify your database and VBA code to send the entire chat history to the AI, handle JSON formatting, and manage multiple chatbots for different tasks. You will learn how to update your forms, process conversational context, and troubleshoot common issues so your chatbot can interact more naturally and remember previous exchanges.
Members
In the extended cut, we will learn how to save conversations in your database by creating a table to store each conversation, manage multiple conversations at the same time, and handle situations where your conversation threads get too long by using techniques to deal with the context window limit. I will show you how to implement all of this step by step.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, build a chatbot with memory, OpenAI chatbot, chatbot remembers conversation, connect Access to OpenAI, GPT 4.1 API, continuous conversation chatbot, store conversation history, database conversation threads, DnD adventure chatbot, anatomy quiz AI, customer support bot, context window limit, escape JSON characters, VBA code modifications, save chat sessions
Intro In this video, I will show you how to build a chatbot with memory in Microsoft Access by connecting your database to the OpenAI API. We will walk through modifying the Access interface for better user interaction, adding fields to store conversation history, and updating the VBA code so the chatbot can remember the entire conversation thread. You'll learn how to handle API changes, escape special characters for JSON, and ensure the chatbot maintains context across multiple messages. We'll also look at testing with sample conversations, fixing common bugs, and exploring practical uses for a chatbot that remembers past discussions.Transcript Got a good one for you today. We're going to learn how to build a chatbot with memory - how to make an OpenAI chatbot in Microsoft Access that remembers the conversation.
Now, I've done a couple of videos before like this one where I show you how to take your Access database and connect it to OpenAI. That's the company behind ChatGPT. They've got an engine you can basically send requests to and get responses back, and it turns your Access database into a simple chatbot. But it doesn't remember the previous conversation. It's very simple. You get a basic question and answer chatbot.
So what we're going to do today is we're going to rustle us up a couple of low-mileage pit walleys and help them build a memory. Anybody? Star Trek fans out there know. Remember that one?
So instead of treating every request as a brand new conversation, you can have the system remember the entire thread, just like ChatGPT does in the browser. It remembers everything you talked about, and you can have multiple different conversations. This of course opens the door to much more practical business uses.
What kind of stuff? Well, you can do customer service and customer support tracking. The bot can remember a history of everything that was talked about with the client. You can talk to your database about an ongoing project and keep all that in the database itself - internal help desk stuff, sales and CRM, training and onboarding.
You can basically set up one of your employees and have the thread there, right inside the database, of everything that they've learned so far. I use this myself for quizzes. You can have the system quiz you and remember what questions it asked. The list is endless.
Let me give you a little demo of what I built myself. Here's my little sample database that I built just to test all the concepts out. I got a little Star Trek talk. I was chatting Star Trek. I was doing a little role playing the adventure with it, and I had it give me an anatomy quiz.
So let's do another sample conversation here. We'll just say sample. Then up top here is where you type in what you want to say. Let's say, it was a shame that Firefly only had one season. And I'm going to send that to the chatbot. We'll get back a response.
Here you can see, it replied with, "Yeah, it really was. Firefly had so much potential. Blah blah blah. We got a Serenity movie. Blah blah blah." Now if, at this point, I just said to you, "Any word on a season two?" If I wasn't sending the conversation thread back, it would have no clue what I'm talking about. Any word on a season two? But now, since I'm sending the entire thread back, it says, "Sadly, there are no official plans for a Firefly season two." See, now it can remember the entire conversation. And that's what I'm going to show you how to do in today's video. Long live brown coats.
All right. Before we get started, this is definitely a developer level video. If you've never done any VBA programming before, go watch this video. In this video, I teach you how to get started working with OpenAI and Microsoft Access. So go watch this video to get all the basics.
After that video, I did another video teaching you how to get images from OpenAI, where you can send it a request for a picture, and it'll give you a picture back. In this database, I made some improvements and some enhancements. So you're going to want to watch this one too. If you don't care about pictures, just get what those extra enhancements are - just some changes to the code. I switched the API model to 4.1 and a few other minor details.
Gold members, I'm going to be working with this database. I'm going to be starting from the database that I ended this video with. So I'm going to grab this video right here or this - this database right there is what I'm going to be starting with.
Silver members and nonmembers, you're going to have to just build the database following these other two videos.
So here's the database at the end of the images from OpenAI video. First thing we're going to do is put our API key in here that you get from OpenAI. I'm not going to show you mine. No, you may not have it.
Let's modify this guy and I'm going to make it a little bit bigger so we have some more room here. This is my text box. I want this to be bigger so I have plenty of room to type in my text that I'm going to send to it. Make that nice and big. We'll keep the button and the combo box. That's going to be our send AI button and we're going to make a bot for the instructions.
I'm going to make two more text boxes here. Just going to copy this one, copy paste paste.
The first text box is going to be to handle the AI's response. So we'll call this guy response. The one on the bottom down here is going to store the entire thread. We're going to keep track of everything that was said in the conversation.
The way this works basically is that you have to send back the entire conversation every time you send a request to the AI. We're going to instruct it to say, "Here is the conversation so far. Here is the next bit of information. Now give me your reply." It's basically how it works. That's essentially what ChatGPT does behind the scenes in the web browser version. It remembers the conversation because it has to constantly parse over that entire thread while we're doing this.
Let's change the color just so it's a little bit different from the other one. Let's make this like a light blue. I like to distinguish stuff. This will be gray because this will be our thread. We'll do a light gray.
We don't need this unless you plan on using the OpenAI images button. I'll just slide it down here and status. We're going to use this to see different stuff that's happening.
Save it. Now we do have to make a couple of minor adjustments to the code because they do make some changes from time to time with the API. So once in a while, I have to make an adjustment.
For example, if I say "Hi there" and try to send it, I get back this refusal note because now we have to look for just the quote, comma - there's no longer a curly brace there. It's a really easy fix. Just go into your global modifications. Find your ask OpenAI function. This is the guy that actually sends the data and gets the response.
I'm going to keep GPT 4.1 as my model. It's currently October 2nd, 2025, and I think this is still the best model. There is a GPT 5 available as well as a 5 Mini and a 5 Nano. I don't like them. 5 by itself is way too slow, and Mini and Nano I don't get good responses from. I like 4.1. It's got the best response to time ratio, I think.
Now we're just looking for our response text equals line. This guy right here. We no longer have that curly brace there. So we're going to replace this with just another double quote like that. We're going to look for that double quote, comma at the end. Save that, and now we should get a response. There we go. "Hi there."
I'm waiting for it to reply because I'm used to my chatbot that I've already built. We gave it the correct spelling only, so it took that and capitalized the H and the period.
So it's working. I was wondering why it was not responding.
Now, one thing we are going to do is come into the code behind the main menu here. I have a button for it up here. This brings me right to the code in the main menu. If you don't have that, then you'll have to come in here and go into one of the events that way. There is a status sub in here. I'm going to delete that because in the newer version of this template, we have a global status function that has a bunch of extra features like the colors and stuff.
Now the next step is we're going to create a bot that is going to be for a continuous conversation. I'm going to call this my chatbot - chatbot continuous.
Here's the prompt that I've been working with. I'm just going to share it with you. I'm going to copy and paste it from my notes.
It says: You are an AI assistant in a continuous conversation. Each request I send will contain thread - the full transcript of everything said so far. May be empty at the start. Then new, my latest query or comment. Reply to new as if it's the next turn in the same ongoing conversation using thread as context. Do not repeat the thread in your reply. Be concise and natural. Respond should say respond. I've been using this, but it obviously knows what I'm talking about. Respond with plain text only, no formatting or markdown. Use only low-ASCII characters because I hate those curly quotes and M dashes and it always wants to use them.
That's the prompt I've been using. We're going to just close that and that is going to be bot number seven now. That's bot seven. So I'm going to make the default for this guy be seven. I just want to keep changing it. Data default value here - seven. Save it.
Now, in our button, we have to behave a little differently if it is bot seven. So we're going to Dim S as String. I'm also going to Dim a command text as String because we're going to build a command text that we're going to send to it.
First up, let's check to make sure that they actually type something in. We didn't do this before. If IsNull(mytext) or mytext equals an empty string, then status your text required. You can give it a color if you want to. Let's go red like that. Then exit. So if they don't type anything in, they're going to get an error message.
Now, if bot combo, that's the bot we picked, equals seven, then it's a continuous conversation. If the thread is empty, then we're going to tell it it's empty.
So if IsNull(threadbox) or thread equals blank, then command text equals thread, and then we'll put like empty like that. That just tells the AI that it's empty. I'm going to put a blank line after it, vbNewLine. Otherwise, command text equals the thread is threadbox and vbNewLine. We can put the thread up top here and then just add empty or whatever. You know what I'm doing.
Now, here's where we're going to add the new conversation, the new text. So now command text equals command text and the new stuff is mytext.
Else, this is a single one-off message. Here all we have to do is basically say command text equals mytext. In other words, if we're not using bot seven, they're using one of the other ones.
I'm going to status it right here. We're going to send it to OpenAI. Status: sending. You can put the whole command text here if you want to see what it is. That's fine. Let's make it yellowish. I had to look up a yellow color.
Now we're going to say s equals askOpenAI. We're going to send it the command text and bot seven or bot combo, in this case, bot combo. Now the response is going to come back. So response equals s.
Why don't I just put response equals askOpenAI on one line instead of using that temporary variable? I don't know.Because in some other versions, I actually do some other stuff with that s. This is just a simple version of it. But, yeah, I like to use string variables. If I can, it's at a sending text boxes.
So now we're going to store that in the thread. So if bot combo equals seven, again, conversation, then thread equals thread and VB new line. Give it a new line after that. VB new line. And we're going to say, "The user said my text," and VB new line. Let's go to the next line. "OpenAI said response." So it's going to take the thread.
And why is it not reading thread? I didn't name this guy today. Nope. So text 21. See that? Capitalization is important with the red. And now if I did something like this, I'm going to press enter. Yep. See how it finds it?
Watching for the capitalization trick, that will help you a lot of the times. That's why I always type in my variables in here in lower case. So we've added to the thread the new my text and response. Now we're going to set my text equals blanks, so we're ready for our next thing. My text.setFocus. After I push the button, it sends it, it updates the thread, then it puts me back up ready to type in another conversation. That should do it.
This is all the old stuff. We can get rid of that. I believe actually ask open AI has code in it that handles the sending. Let me see here. Yeah, this guy has sending to open AI, so we don't need that. We can get rid of this. This extra sending here. Ask open AI takes care of updating the status.
Save it. Debug compile. Another thing I'm going to do real quick is make this the default button. So when I hit enter it pushes that. I can just type, hit enter. I'll see the response, type again, hit enter. Other default button. Save it.
There are a couple of little minor modifications we still have to make for some other situations, but we might be good enough now to test it. Let's try it. Ready? Hi there. Enter. Oh, bad request. We have to fix it right now.
Here's the problem. It took me a few minutes to troubleshoot this. When we send stuff and get replies back, we have to get rid of any VB new line characters because they're not JSON friendly. JSON is this format that we're sending. This stuff here is all JSON. It's a particular format. We have to get rid of new line characters and we have to get rid of double quotes in our user content, our system message. Then we have to account for them in the response text that comes back. It's not hard to do. It's a similar place, but we're going to come right up here.
We're going to say user content equals replace user content, comma, VB new line, comma. We have to replace it with a backslash n. So anywhere that there's a new line character coming in, we have to replace it with a backslash n. We also have to do the same thing with any double quotes. Those have to be what's called escaped. So here we're going to take a double quote and replace it with a slash double quote. See that?
Then we have to do the same thing with the system. The system message is down here. So let's just move this down a little bit. System message is our instructions, our bot instructions. So you can now put quotes and lines in there if you want to. System message. We'll do that next.
When we get the reply, the response, we have to go the other way. Down here, here's the response text. We're already replacing CHR 10 with the VB new line, but we're also going to do the same thing we did before. We're going to respond. We're going to replace slash n with the VB new line as well as slash quote is going to be replaced with double quotes like that.
In fact, we could put this up here and close the object at that point, then we'll do our cleanup. That should do it. That was the problem. I ran into this problem. I had to analyze everything and I figured I'd save you the time. Usually I say if you can pause a video and figure it out, but no, it's one of those things. It's a hair puller. You have to clear these characters before they go across because it'll mess this string up.
Save it. Debug compile. Once in a while, now let's give it a test and say hi there. And there we go. Understood. How can I help you today?
My name is Rick. What's yours? Hmm. I went through a few more. Let's talk about Star Trek. I just kept saying this. I made a mistake and I'm leaving it in the video so that it's a teaching moment.
Now I want you to pause the video and see if you can figure out what I did. I messed up somewhere. I'll give you a hint. It's a copy and paste problem. This is what happens when I don't take the time to carefully look at my notes and I'm talking to you and I don't catch something.
See it? It's right here. I copied these two lines and I'm saying user content is now becoming just the bot. So that's my mistake. This has to be that. It's not getting any new user content. It's just getting the bot.
That was my bad. David, the bug. I'm gonna pause. See, I'm not perfect. Especially when I'm chatting with you. If I'm just sitting here quietly contemplating and typing in code, I almost never make mistakes. But when I'm chatting about it and I'm talking about it and I'm trying to do two things at once, yeah. I goofed up.
We're going to start this conversation thread new and we're going to say hi there. My name is Rick. What's yours? And now it should get it. There we go. Nice to meet you, Rick. I don't have a personal name, but you can call me Assistant or anything you like. How can I help you today?
Let's chat Star Trek. Sure, Rick. See, it's remembering me. Star Trek is a classic. Do you have a favorite series or character?
Picard. Great choice. Picard. Are you a fan of the Next Generation series? Are you getting the newer Picard series too?
I'm just going to say next gen. Just like that because normally by itself it would have no clue what next gen is. Hit enter and now it continues the conversation. It knows what I was talking about. I did the same thing yesterday with a D&D adventure.
Let's try a new one. I'll say something like, I'm going to use my voice recognition. Let's see if I can... I don't think I've ever tried it with recording a video at the same time.
Let's play a game. I would like you to be a dungeon master for me in a D&D type game. Ready? Great. I'm using Whisper Flow. It's awesome. I love it. And then I just send that.
It says, absolutely. I'm ready to start. What kind of character would you like to play? Pick a race, class, maybe a name. Or if you like, I can make some suggestions.
I'll say, I would like to be a barbarian like Conan out to avenge the death of his father. Then send it. Great choice. You're a powerful barbarian, driven by vengeance. What's your character's name? Any particular weapons or special traits?
Let's say my name is... Farothgar. I use a two-handed sword. Let's begin.
All right. It's a windbreaks over the ashes of your burned village. Your father's a certain hand, but it did it. Tracks lead away, and the dense snowy forest follows the tracks. See? And it can continue playing the game like this. It's really cool stuff.
I also did an anatomy quiz for myself. I had it quiz me on all the muscles and bones. I said, don't repeat any, and it was pretty good. You get to repeat every now and then, but sometimes I think it's just trying to.
There is one more bug fix that we have to do. Now that we've got it working and all this is good, there's one more bug fix I've got in the code. There's delete. Let me move this over so you can see it here. Actually, that's not going to help us. Let me move this this way.
So we've got this delete-trailing characters here that we run. It's up here somewhere. Yeah, there it is. After we get the response text, we delete-trailing characters, because sometimes it comes over with an extra CHR10 or CHR13 or a new line. But this was causing me problems right here.
We're going to remove this if there's a quote at the end because, especially when I was playing this RPG type game, if it ended with a quote, like the wizard says, "I will kill you," and if a double quote was the last thing, then it errored out. So we're just going to get rid of that. It should be able to handle quotes just fine now in the end. Save that and that should do it.
Now, some additional considerations. Right now we're not saving anything in the database. If you want to save your conversations, it's very easy to do it. I'm going to walk through it in the extended cut. Just take this form, make a table, to store your conversation, whatever you want to call it. Store an ID, a description, and a thread, which is what I did here in my version. I've got a conversation table. You'll see down here I've got a conversation ID, a description, name.
The second consideration is the length of the conversation. Now, eventually if you keep having these long, D&D style conversations or chatting with them about customer service or whatever you're doing, you're going to run into what's called the context window. These conversation threads can only be so long. They can be pretty big. In fact, about 100,000 characters is the limit where you are going to start running into problems. But if your threads get that long, remember every time you talk to the AI, it's going to send that whole thread. It's going to get slow. The AI has to process through all of that.
So in the extended cut also, I'm going to teach you a technique for dealing with that problem.
So that's going to be today's extended cut. We'll learn how to save conversations, have multiple conversations in the database at the same time, and we'll deal with those long threads.
Silver members and up get access to all of the extended cut videos. You've already seen today how being a gold member gives you the ability to download these databases directly from my website. When I say, hey, we're going to start working from this database, you don't have to build the whole thing yourself. Although you should, you'll learn better that way if you build these databases yourself. But sometimes to follow along with a video like this, it's a good shortcut just to say, "Hey, this is the starting point where you'll grab this guy." It's like what I do during my full course.
Before we start level seven, I'm like, "Here's the level six database. Download this. We're all on the same starting point." Because I know you like to make little changes and little bugs creep in and you have little differences between what I'm teaching and what I'm teaching in this way, we're all on the same starting block.
So check it out. Join today. I don't know what you're waiting for. I keep telling you, hit that blue join button and get on board.
So that's it.That's going to be your TechHelp video for today. I hope you learned something and had some fun. This is a lot of fun for me. I had a lot of fun putting this together last night and I couldn't stop playing with it. I was up until two in the morning just goofing around with this thing.
I've been using ChatGPT forever in the browser, but now you can do this right in your Access database. It's really cool.
Live long and prosper, my friends. I'll see you next time. Let me know what you think. Post your comments down below and tell me what kind of projects you're planning to build in your database now that you can have real conversations with the AI based on your data. That's cool.
See you next time.
TOPICS:
Building a chatbot with memory in Microsoft Access
Configuring Access to connect to the OpenAI API
Creating a larger text input box for user interaction
Adding a response text box for the AI's reply
Adding a thread text box to store the conversation history
Modifying the VBA code to send the full conversation thread to OpenAI
Updating the askOpenAI function to handle API changes
Implementing logic to differentiate single-turn vs multi-turn chatbots
Using a custom prompt for continuous conversation context
Handling JSON formatting by escaping newline and quote characters
Appending new user and AI messages to the conversation thread
Clearing input fields and setting focus after sending a message
Debugging common issues with thread management and string handling
Fixing trailing character and quote handling in AI responses
Testing the chatbot with sample ongoing conversations
Playing a text adventure game using the chatbot
Conducting a self-generated anatomy quiz through the chatbot
COMMERCIAL:
In today's video, we're going to learn how to build a chatbot with memory inside Microsoft Access using OpenAI's API. I will show you how to modify your Access database so the chatbot can remember entire conversation threads instead of treating each message like a brand new discussion. You'll see how to manage conversation context, set up those memory features, and adjust for API changes like handling new lines and special characters. We'll also discuss common bugs you might run into, tricks for organizing your responses, and ways you can use this in your business, like customer support or interactive training. In today's Extended Cut, we will cover how to save and manage multiple conversations in your database and what to do when your conversation threads get too long. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the main improvement introduced in this video compared to previous chatbot tutorials?
A. Adding the ability to handle multiple users at once
B. Allowing the chatbot to remember the conversation thread (memory)
C. Integrating image generation from OpenAI
D. Upgrading to a new interface design
Q2. Why is it important to include the entire conversation thread each time you send a new request to OpenAI?
A. So that the API responds faster
B. To allow for image responses as part of the conversation
C. So the chatbot can use previous context to give accurate replies
D. To prevent connection timeouts
Q3. What are some practical business applications of a chatbot with memory in Microsoft Access?
A. Payroll calculation only
B. Only handling appointment scheduling
C. Customer service, project tracking, internal help desks, sales and CRM
D. Real-time stock trading
Q4. In the demonstration, what problem is highlighted when sending a follow-up question without sending the conversation thread?
A. The chatbot ignores all input
B. The chatbot only responds with an error message
C. The chatbot cannot connect to OpenAI
D. The chatbot has no idea what earlier messages refer to
Q5. What key VBA change was made to handle changes in the OpenAI API's response format?
A. Searching for curly braces in JSON as before
B. Parsing based on a double quote followed by a comma instead of a curly brace
C. Using machine learning to reconstruct the response
D. Sending raw text instead of JSON
Q6. Why is it necessary to replace certain characters before sending user input and system messages to OpenAI via JSON?
A. Because high-ASCII characters speed up the response
B. Because JSON is sensitive to newlines and double quotes, which must be escaped
C. Because OpenAI only accepts uppercase input
D. Because double quotes are not allowed in English
Q7. What issue can arise if conversation threads become very long?
A. User responses get deleted automatically
B. The chatbot starts ignoring the context entirely
C. You may hit the context window limit, making responses slower or causing errors
D. Messages disappear after five exchanges
Q8. What technique was recommended to manage the issue of lengthy conversation threads?
A. Restart your computer regularly
B. Split threads into smaller conversations and track them
C. Reduce the size of the Access database file
D. Only use single-word inputs
Q9. When updating the conversation thread, why is it important to ensure that variable names are correct and consistently capitalized in VBA?
A. Because variable names in Access are case-sensitive and incorrect naming can cause bugs
B. Capitalizing variable names automatically increases performance
C. Incorrect capitalization deletes the entire code module
D. It does not matter in VBA
Q10. What is the suggested method for allowing users to resume previous conversations in the Access chatbot?
A. Save and reload conversation threads in a dedicated table in the database
B. Use the clipboard to paste old messages
C. Print all conversations to a PDF file
D. Don't allow users to resume conversations at all
Q11. In the VBA code, before sending a message to the chatbot, what user validation step is added?
A. Checking if the API key matches a fixed value
B. Ensuring the input box and thread box are not empty
C. Making sure the request is from a gold member
D. Reloading the entire main menu form
Q12. What modification is made regarding trailing characters in the AI's response?
A. All trailing numbers are removed
B. Trailing double quotes are no longer deleted to avoid cutting off valid responses
C. All periods are stripped from the end
D. The response is reversed
Q13. Why does the presenter emphasize using low-ASCII characters in the bot's response?
A. Because some database fields only support low-ASCII characters
B. Because curly quotes and em-dashes can cause formatting issues or incompatibilities
C. Because it speeds up API responses
D. Because OpenAI only understands low-ASCII
Q14. What new bot is introduced in the video that's designed for continuous conversation?
A. Bot model 5 Nano
B. The Star Trek bot
C. Bot 7, the continuous conversation bot
D. The picture bot
Q15. When building the command text for a continuous conversation, what is the correct sequence of steps?
A. Only send the new message
B. Append the new message to an empty thread
C. Combine the entire thread with the new message, formatted for the AI
D. Use the last three messages and ignore earlier context
Answers: 1-B; 2-C; 3-C; 4-D; 5-B; 6-B; 7-C; 8-B; 9-A; 10-A; 11-B; 12-B; 13-B; 14-C; 15-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 Today's TechHelp tutorial from Access Learning Zone focuses on building a chatbot in Microsoft Access with real conversation memory. This means creating an OpenAI-powered chatbot that can remember and carry on an ongoing conversation, just like ChatGPT does in your browser.
Previously, I have shown how to connect your Access database with OpenAI to create simple chatbots. Those early versions were limited since they did not retain any memory of prior conversation: each question was a fresh start, and there was no context from what had been discussed earlier.
In today's lesson, I want to show you how to give your chatbot the ability to remember the conversation. Think of it as adding "memory" to your chatbot, much like ChatGPT remembers all the back-and-forth you have in a session online. With this improvement, the chatbot in Access can maintain ongoing threads, making it useful for all kinds of business situations: customer service chat records, tracking ongoing project discussions, internal help desk issues, sales and CRM histories, employee training, or even creating self-quizzing tools that remember what has already been asked. There are numerous practical applications when your bot can remember each back-and-forth with users.
To illustrate this, I set up a sample database for testing. For example, in one conversation, I chatted about Star Trek, role-played a little adventure, and even had the bot quiz me on anatomy. Each interaction is part of a continuous thread.
In a typical session, you start a conversation and ask something like "It was a shame that Firefly only had one season." The chatbot will respond appropriately. Now, if your next message is something like "Any word on a season two?", the chatbot, because it remembers the previous discussion, knows you are referring to Firefly and can respond correctly. Without this memory feature, it would not make that connection.
This tutorial is geared toward those comfortable with Access at the developer level. If you are new to VBA programming or have never worked with OpenAI in Access, I recommend reviewing my introductory video on connecting Access and OpenAI for all the basic setup steps.
After that introduction, I also created a video on requesting images from OpenAI and making further improvements to the codebase. Even if you are not focused on images, I recommend watching to learn about enhancements like switching to the GPT 4.1 API model and other minor changes.
For members at the Gold level, I will be working from the database that was developed at the end of the images video. Silver members and nonmembers, you will have to build out the database by following the previous tutorials.
To get started, in your test database, first place your OpenAI API key into the designated spot. I suggest enlarging the text box used to send input to the AI to give yourself more space to type. You should also create two more text boxes: one to display the AI's response, and another to store the full conversation thread.
The critical point here is that for the memory function to work, you must send the entire conversation thread back and forth with each request. The prompt essentially tells the AI, "Here is the conversation so far. Here is my latest comment. Please reply accordingly." This builds context, just as ChatGPT's web version maintains its state by constantly referencing the conversation history.
I suggest using different background colors for these new text boxes to distinguish their purposes visually. If you no longer plan to use image features, you can set aside those controls.
Next, there will be some minor adjustments required in the supporting code. Occasionally, OpenAI changes the API response format, so things like the markers for response text may need updating. For example, if the AI's response format changes, you may need to adjust the parsing logic in your function that sends queries and receives replies.
I recommend sticking with GPT 4.1 as your model for now. Although GPT 5 and its Mini and Nano variants are available, GPT 4.1 generally gives the best balance of response quality and speed.
You will need to update the code in your form's command button to handle both the single-message and memory-enabled chatbot modes. If the user fails to fill in the input box, make sure you prompt them with a status message before sending anything to OpenAI.
For our memory-enabled chatbot (let's call it bot number seven in your system), update the logic so that if the conversation thread is empty, you flag it as such for the AI. Each time the user submits input, append both their message and the AI's response to the thread box, always keeping it current. When the bot is in memory mode, make sure your command text incorporates the full conversation thread, followed by the new message.
Be aware that all data sent to and from OpenAI must use JSON formatting. This means you cannot send VB new line characters or double quotes as-is—they must be escaped or converted into friendly characters (for instance, using "backslash n" for new lines and escaping double quotes). Similarly, when processing the reply, convert any escaped characters back into their appropriate Access equivalents for display.
Occasionally, you might encounter bugs due to copy-paste errors or API changes. For example, if your code mistakenly overwrites input variables or fails to add updated content to the conversation history, double-check that you are passing and appending data correctly. Consistent naming and careful troubleshooting habits pay off here.
Once set up, you can see the memory feature in action by having a conversation about favorites from Star Trek or launching into a Dungeons and Dragons adventure, with the AI retaining context and responding naturally to follow-up comments. You can also create learning quizzes, where the AI remembers which topics or questions have already been covered.
There are still a few small details to polish, such as ensuring the chatbot can handle conversations that end with certain special characters (like double quotes) without error, and deleting unnecessary clean-up steps in the code if they create problems.
Currently, the conversation is only stored in the form during use. If you want to preserve entire chat threads or manage multiple ongoing discussions within your database, you can create a table to save conversation records, including an ID, description, and the thread text. This way, every interaction is available for later reference.
One item to keep in mind is the conversation length. Each time you interact with the chatbot, the entire thread is sent back to OpenAI. Long conversations—those reaching around 100,000 characters—will slow down performance and may hit the limits of the AI's context window. In the extended cut of this lesson, I will show techniques for handling long threads and managing multiple chats in your database. Gold and Silver members get access to these extended-cut videos, and as always, Gold members can download the databases shown in the videos for their own use.
When I begin advanced lessons, I always start from a standardized database so everyone is on the same footing. This helps avoid issues from previous small modifications or errors that may have crept in while following along at home.
In summary, today's tutorial covered creating a chat interface in Access with memory using OpenAI, adjusting your code for context handling, conversation tracking, JSON compatibility, and addressing common pitfalls. With this setup, you can have rich, ongoing conversations right from Access and tie them into your actual business data.
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 Building a chatbot with memory in Microsoft Access
Configuring Access to connect to the OpenAI API
Creating a larger text input box for user interaction
Adding a response text box for the AI's reply
Adding a thread text box to store the conversation history
Modifying the VBA code to send the full conversation thread to OpenAI
Updating the askOpenAI function to handle API changes
Implementing logic to differentiate single-turn vs multi-turn chatbots
Using a custom prompt for continuous conversation context
Handling JSON formatting by escaping newline and quote characters
Appending new user and AI messages to the conversation thread
Clearing input fields and setting focus after sending a message
Debugging common issues with thread management and string handling
Fixing trailing character and quote handling in AI responses
Testing the chatbot with sample ongoing conversations
Playing a text adventure game using the chatbot
Conducting a self-generated anatomy quiz through the chatbotArticle In this tutorial, you will learn how to build a Microsoft Access chatbot powered by OpenAI's API that can remember the entire conversation thread, similar to how ChatGPT does in the browser. This memory feature enables you to create much more useful applications, from customer service bots that track conversation history, to interactive training modules that remember which questions were already asked.
To begin, you need an Access database set up with the ability to send requests to OpenAI's API and receive responses. You also need some familiarity with VBA (Visual Basic for Applications) within Access, as that is how we will handle sending and receiving data, as well as managing the conversation thread.
First, make sure your form is ready for conversation. You will want a text box for inputting your messages, a command button to send those messages, a text box to display AI responses, and a larger text box to display the whole thread of the conversation. Resize these controls as needed for convenience. It's a good idea to give the thread text box a distinctive background color (for example, light gray) to set it apart visually.
Now, let's look at how you make the chatbot remember the whole conversation. The key idea is that every time you send a message, you also send the full transcript of the conversation so far. The bot then responds using this as context, making the conversation feel seamless and continuous.
To make this happen, you need to adjust the code that sends the prompt to OpenAI. The system prompt (this is the message OpenAI's models use for instructions) needs to explain that each request contains a 'thread' (the running transcript), which may initially be empty, as well as 'new' (the latest user input). Instruct the AI to reply only to the new part, but to use the thread for context. Here is a sample prompt you might use for your chatbot:
You are an AI assistant in a continuous conversation. Each request I send will contain thread – the full transcript of everything said so far (may be empty at the start) – and new, my latest query or comment. Reply to new as if it's the next turn in the same ongoing conversation using thread as context. Do not repeat the thread in your reply. Be concise and natural. Respond with plain text only, no formatting or markdown. Use only low-ASCII characters.
Now, integrate this into your VBA code. When the user clicks the send button, the code should check which bot type is selected. If it is your new continuous conversation bot, build the command text by concatenating the thread (if any) and the user's new input. Here's what the general structure of your VBA could look like for this:
Dim commandText As String
If IsNull([thread_box]) Or [thread_box] = "" Then
commandText = "thread: (empty)" & vbNewLine
Else
commandText = "thread: " & [thread_box] & vbNewLine
End If
commandText = commandText & "new: " & [input_box]
You then call your routine (such as askOpenAI) and pass in this commandText along with your system prompt and chosen model (for example, GPT 4.1). When you receive the response, display it and update your thread like this:
[response_box] = response
If continuous_conversation_mode_then
[thread_box] = [thread_box] & vbNewLine & "User said: " & [input_box] & vbNewLine & "OpenAI said: " & response
End If
Set [input_box] to blank and set focus to it for smooth ongoing interaction.
When sending data to the API, remember JSON formatting issues: new lines and double quotes must be handled properly. Replace all vbNewLine instances in user/system content with '', and double quotes with '\\"'. When you get a response back, reverse this: replace '' with vbNewLine and '\\"' with a plain double quote. This is important to avoid errors processing JSON.
For example, before sending:
userContent = Replace(userContent, vbNewLine, "")
userContent = Replace(userContent, """", "\\"" )
And when receiving:
responseText = Replace(responseText, "", vbNewLine)
responseText = Replace(responseText, "\\"", """" )
Always ensure your variable names reference the correct controls in your form. Naming mismatches can cause confusion and bugs, so double-check what each textbox is named and reference it accurately.
You may also run into limits with conversation length. The context window (the maximum amount of text you can send to and receive from the AI in a single request) can be as high as 100,000 characters but sending a massive thread will slow things down. For longer conversations, especially if you're running a role-playing game or having an extended support chat, you'll eventually need to trim or summarize parts of the thread to keep context manageable and efficient.
If you want to store your conversation for later review, set up a table (for example, 'Conversations') with fields such as ConversationID, Description, and Thread. Every time you start a new talk, save it with a new ID and, as the thread updates, store it so you can return and pick up where you left off.
To sum up, the steps to create a chatbot in Access with memory are: create a form with appropriate text boxes and buttons, set up the form so both user input and the AI's responses are visible, store the conversation thread in a box, and update this thread every time a message is sent and received. Adjust your prompts and VBA code so the API receives the full thread every time, and handle all necessary text formatting to avoid JSON errors. Finally, consider how you'll store and manage long conversations for both practical and technical reasons.
With these principles, you can now turn Access into an environment for dynamic, ongoing conversations with an AI, opening up new possibilities for business, training, games, or anything else you can imagine where context and memory matter. |
