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.
For more info please visit:
No comments:
Post a Comment