OpenAI
By Richard Rost
3 years ago
Bring ChatGPT to Access with OpenAI's API
In today's Microsoft Access tutorial, I'll guide you through the process of integrating the powerful capabilities of ChatGPT into your Access database. We'll leverage the OpenAI API to send and receive data over the web, enhancing your database's functionality and versatility.
Prerequisites
Links
Recommended Courses & Templates
Part Two
Keywords
Web api, webapi, ChatGPT, OpenAI API, API Tutorial, OpenAI with Microsoft, GPT-4, Microsoft Access API, ChatGPT in Access, OpenAI GPT, Web Data in Access, API in Database, ChatGPT Tutorial, Database Automation, Access API Guide, Natural Language Processing, NLP in Access
Subscribe to OpenAI
Get notifications when this page is updated
Intro
In this video, I will show you how to connect Microsoft Access to OpenAI's ChatGPT using their API. You'll learn how to set up an OpenAI account, obtain an API key, and integrate it into your Access database with VBA. We'll talk about sending data from Access to ChatGPT for tasks like correcting spelling and grammar, as well as how to handle responses using JSON. I'll also cover important prerequisites like web API and VBA basics that you'll need to follow along. This video requires a paid OpenAI account and is intended for developer-level users.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today I have a good one for you. I am going to show you how to bring the power of ChatGPT to Microsoft Access using OpenAI's API. That is basically an application programming interface that you can use over the web so you can send data directly from your database to ChatGPT, have it do some stuff, like correct it for spelling and grammar, and add punctuation - that kind of thing. Then bring it back to your Access database with one click and, boom, there it is. You have got AI-powered database technology.
Before we get started, prerequisite time. If you have not yet watched my web API video, go watch it. We are going to use all the code in this video to talk to ChatGPT's OpenAI. If you have not finished this, go watch it now and then come back. I will wait for you. Of course, this is a developer-level video so it is going to require a bunch of VB code. If you have not learned how to program in VB yet, go watch my Intro to VBA video before that video and then come back.
We are also going to need my FindBetween function, because OpenAI is going to reply with a JSON string like that. We are going to have to use my FindBetween to locate the text that we want to pull out of there to make it usable. Go watch this video too. Gold members, of course, all of this code is in the code vault and you can download those other databases.
If you are not familiar with OpenAI, it is the company behind ChatGPT. If you are not sure what ChatGPT is, then you have been living under a rock for the last six months. ChatGPT is basically conversational AI. You can ask it a question like, "Tell me why Rush is the greatest rock band of all time," and it will answer you as if you are talking to another person. You can take this level of sophistication and put this in your Access database with not too much work.
Now I just have to see what it finishes up here. I am liking it so far. Yes, musical tastes are subjective, but Rush is the greatest rock band of all time.
Anyways, OpenAI has an API - an application programming interface - that you can use to query their AI engine, basically like you are talking about ChatGPT. You can use this for simple things. For example, if you use your Access database as a letter writer, like I do - I use it to write my customer service correspondence. I have voice dictation for a lot of stuff, so I do not bother to say "period" and put exclamation points in, but you can just free form, say the words into your Access database, and then click a button and it will send it to the API. Send it to OpenAI, it will correct it for spelling and grammar - as the example I am going to use in today's video - and then send it back to you and put it back in your notes field.
That is just one example. You can send it a list of the fields in your table and say, "Write me an SQL statement to do..." and it will write a query for you. That kind of stuff.
I am going to tell you up front, this will require a paid account at OpenAI. You have to get an API key, which requires setting up a paid account with a credit card. I will tell you, though, it is very inexpensive. I have been playing with this for the past couple of months - a lot - and using it for my own business for, like I said, customer service correspondence. I think over the past two months, I have racked up maybe $3 in charges and that is using a ton of it. So it is extremely inexpensive, but you will need a paid account. If you are not willing to do that, then the rest of this video is not for you.
I do believe they offer you a little bit of free credit before you actually have to sign up with your credit card, but do not quote me, because they may change this in the future. You can try setting up an account without a credit card and you might get a little bit of usage, but it is cheap. Trust me.
So let us go ahead and set up that account on openai.com. Go to openai.com - I will put a link to it down below for you. Go under their API and here you can play with ChatGPT. By the way, play with this first if you have not played with ChatGPT yet and get a feel for how it works and what it can do.
Come over here to Overview. Of course, it is currently October of 2023. They may change their website and their interface in the future, so do not hold me accountable for that. I will try to update the video if they make major changes, but go to Overview and then come down to Get Started. It is going to ask you to sign up.
I already have another account, but I am going to sign up a new account for you. I am going to continue with Google. I will use my secondary account here to create a new account.
All right, I do not know why they want your age, but type in your age. I am not telling you mine. Then, of course, your phone number - they are going to text you a code, type in the code.
Now you are on their OpenAI platform screen. Come over here to your account and go to View API Keys. You have to create a secret key right here. Click on that. Create a new key. Give it a name, like it says, "My Test Key," or whatever you want to call it. It creates the secret key. There it is. That is not the whole thing, it is longer than that. I am going to delete this after I am done with this video, so I am not worried about you seeing it. But copy it here and put it in Notepad. Open up Notepad, paste it in there. There is your secret key.
We are going to need this. We have to put this inside of Access in a minute, so I am going to slide this over to the side.
Here is the video that we built in the Web API video. It literally just goes out with the Web API and gets the current date and time. Here is the code for that. Where are you? Right here - GetUTCTime. Let us right-click Definition, and there is the code for that.
Now, this is essentially all the same framework that we need to talk to any website. But certain sites like OpenAI require that API key so they know that it is you. That way, not everybody can use your account and run up charges.
What I like to do is create another module that has any kind of private keys and stuff like that in it. Mostly for my videos, so I do not accidentally flash my key on the screen all the time. So I am going to create another module. In here, I am going to make a constant. Let us make it a Public Constant. We will call it MyAPIKey, and that is going to be equal to the thing we copied earlier. That is my API key. Save that module. All right, SecretKeyMod. Now I do not ever have to put that on the screen again. I can put my actual, real API key in there so you guys are not going to see it.
Let us set this form up here, though. I am going to get rid of this and I am going to make this field somewhere you can type in some text. Then we are going to send that text to ChatGPT and see what it does with it.
So let us reformat this guy here. Get rid of that =date. Get rid of that format. The name is going to be, I do not know, let us call it MyText. We will just type whatever we want in there and then we will make a button and we will send that to OpenAI.
So let us go into our code. Now, right down here, we are going to use the OnClick event for the button to do all this. We can get rid of this stuff - it is a blank line down here at the bottom.
Now we have got our API key, that is all set. We are going to need that in a few minutes - that is already set as a global constant.
We need two more constants and I am just going to copy and paste them instead of typing them in again. There they are. There is the URL - this is where we are sending the stuff to. It is openai.com/v1/chat/completions. That is just the URL they have set up for their API.
Now, the model - there are many different types of GPT models. 3.5 is their older one, but it is faster and it is a lot cheaper. It is like fractions of a penny per query versus the newer version 4.0 model, which is a little more complex, a little smarter. But I think for your average everyday usage, the GPT-3.5-turbo model is just fine. If you want to change that, go look at their documentation. They have tons of documentation on their website. What I am doing for you right now is distilling my weeks of reading about this stuff into a short video so you can get working with it. There are all kinds of different options and stuff you can change.
Now what I am going to do next is go over here to this GetTimeMod - the one that we made from the last video - I am just going to copy this stuff because we are going to use most of this. But instead of putting it in the module over there, just for now, I am going to drop it in here. We are going to need a lot of the same stuff.
We need the XMLHTTP object - obviously that is what we use to send and receive this stuff. MyResponseText is what you are getting back. MyURL we have up here as URL, so in fact, let us just change this to MyURL and then we can get rid of the old one. We do not need that.
In the last video, we used the GET method of getting information. Essentially, there are two kinds of methods for sending and receiving information over the web like this. There is GET, which basically sends it all as if you were typing it in the query string at the top of your web browser. Let me show you.
This thing here is essentially like if you were saying "599cd.com/test?name=Richard&phone=blahblah" - right? This is what is called the query string right there. GET is essentially using that query string.
The alternative is something called POST. POST is what forms use when you fill out a form on the web and you hit the Submit button. That gets posted as a form. So we have to change the way we are sending and receiving data just a little bit.
Instead of using GET, we are going to use POST here. We are posting it like a form.
One more thing I am going to add is some timeouts. Then we have to set the content type and the authorization.
These are timeouts. This is how long it takes to resolve, connect, send, and receive data so that if it - these are milliseconds - if it takes more than five seconds to resolve, five seconds to connect, fifteen seconds to send the data, or fifteen seconds to receive the data, it will time out and it will not just lock your application. The application (Access) will not just sit there waiting for stuff.
That is it for today, folks. Stay tuned for part two. Today is Friday the 20th, so part two will be released on Monday the 23rd on my YouTube channel, or members can watch it right now on my website. There is the link right there.
Same bat time, same bat channel. I will see you on Monday.
Quiz
Q1. What is the main purpose of integrating ChatGPT with Microsoft Access using OpenAI's API?
A. To perform database backups automatically
B. To enable AI-powered features like text correction within Access
C. To create visual dashboards
D. To generate Access forms automatically
Q2. Which programming language is primarily used in the tutorial to communicate with OpenAI's API from Access?
A. Python
B. JavaScript
C. Visual Basic for Applications (VBA)
D. SQL
Q3. Why is the FindBetween function needed when working with OpenAI's responses?
A. To parse XML responses from the API
B. To extract specific text from the JSON string reply
C. To find missing data in database tables
D. To locate errors in the VBA code
Q4. What is an API key required for when using OpenAI's API in Access?
A. To ensure only authorized requests are made to the OpenAI API
B. To speed up the responses from ChatGPT
C. To generate random database records
D. To encrypt user input data
Q5. Which model version is suggested in the video for "average everyday usage" in Access due to its speed and cost?
A. GPT-2
B. BERT
C. GPT-3.5-turbo
D. GPT-4.0
Q6. What HTTP method is required to send data to OpenAI's chat completions endpoint from Access?
A. GET
B. POST
C. PUT
D. DELETE
Q7. What is the main difference between the GET and POST methods as described in the video?
A. POST allows sending data in the URL bar; GET places it in a form
B. GET is faster and always secure; POST is only for images
C. GET sends data via the query string; POST sends data inside the request body
D. GET is only for uploading files; POST is for accessing APIs
Q8. Why do you need to configure timeouts when making API requests from Microsoft Access?
A. To prevent endless waiting if the API does not respond in time
B. To increase database storage capacity
C. To speed up database indexing
D. To refresh the Access user interface automatically
Q9. Before following this video, which topics or videos does the instructor recommend you be familiar with?
A. SQL optimizing and database indexing
B. Web API integration and VBA programming basics
C. Access report design and dashboard creation
D. Spreadsheet formulas and charting
Q10. When working with API keys in VBA, why does the instructor suggest placing them in a separate module?
A. To avoid code duplication and improve security by not displaying them unnecessarily
B. To decrease the number of required references in Access
C. To make the key available only to form events
D. To enable key sharing across multiple computers
Q11. What is a possible application of using ChatGPT with an Access database as demonstrated in the video?
A. Automatic report generation in PDF format
B. Spelling and grammar correction for user-entered text fields
C. Importing Excel data into Access
D. Generating barcodes for inventory records
Q12. What is a prerequisite to using OpenAI's API as described in the video?
A. Downloading a free open-source package
B. Creating a paid account with a credit card at OpenAI
C. Requesting access from Microsoft
D. Running a local server instance of OpenAI
Q13. What is the suggested first step for those who are new to ChatGPT before proceeding with the API integration?
A. Reading the entire developer documentation
B. Watching only Access-specific tutorials
C. Playing with ChatGPT via the OpenAI website to get familiar with its capabilities
D. Purchasing a license from Microsoft
Q14. What does the instructor say about the cost of using OpenAI's API for this type of integration?
A. It is prohibitively expensive for most users
B. The cost is minimal, even with frequent usage
C. There is an upfront fee of several hundred dollars
D. Access users get a special free tier
Answers: 1-B; 2-C; 3-B; 4-A; 5-C; 6-B; 7-C; 8-A; 9-B; 10-A; 11-B; 12-B; 13-C; 14-B
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 how you can harness the capabilities of ChatGPT directly from Microsoft Access by utilizing OpenAI's API. With this integration, you'll be able to send data from your database over the web to ChatGPT, have it process your information—such as correcting spelling and grammar, or adding punctuation—and then return the result right back into your Access database, all with the click of a button. This brings sophisticated AI-powered features into your database environment.
Before getting started with the main lesson, there are some topics you need to be familiar with. If you have not yet watched my video on working with web APIs in Access, you should do that first, as we rely on that code to communicate with OpenAI. For those who are new to VBA programming, I recommend viewing my Intro to VBA video so you have a solid grasp of the basics before returning to this material. In addition, my FindBetween function is important, because the responses from OpenAI come back in JSON format, and this function helps us extract the data we want from those responses. All of this supporting material, including the code, is available to Gold members for download.
OpenAI is the company responsible for ChatGPT. If you have not paid attention to developments in artificial intelligence recently, ChatGPT is a conversational AI platform that allows you to ask questions or give prompts and receive human-like responses. For instance, you can ask it to justify why a particular band is the best of all time, and it will answer you in a conversational way. The exciting part is that you can connect this level of intelligence to your Access database with less effort than you might expect.
OpenAI offers an API (Application Programming Interface) which allows you to interact with the same AI engine that powers ChatGPT from within your own applications. This can be incredibly useful for a variety of practical tasks. For example, you could use it for composing letters where you dictate your message into Access, and then a single button click submits the text to OpenAI. The AI will take care of enhancing the grammar and spelling, and then return the result to your database for you. This saves time and improves the quality of your correspondence. The same approach can be applied to other database tasks, such as asking the AI to generate an SQL statement based on your specifications or instructions.
A requirement for using OpenAI's API is that you sign up for a paid OpenAI account and obtain an API key. This does require providing a credit card. The good news is that the costs are very modest; even with heavy use, charges tend to only amount to a few dollars over a couple of months. OpenAI may provide a small amount of free credit initially, but be aware that their policies could change in the future. If you are not willing to create an account, then this tutorial will not apply to you, but for those who do, it is a very economical investment.
To begin, you will need to create an account at openai.com. Navigate to their website, find the API section, and if you have never used ChatGPT before, I suggest using their demo to get comfortable with its capabilities before integrating it into your own applications. Create your account using your preferred method, provide the necessary information (including your age and phone number for verification), and once registered, access your account area to generate an API key. Give the key a name for your reference, then copy the key and save it somewhere secure such as in Notepad, because you will need to use this key in your Access database setup.
Within Access, I recommend creating a dedicated module to store your secret keys and other sensitive information. This practice helps keep your API keys secure and prevents accidental exposure of this data in your projects and training materials. Define your key as a constant in a module (for example, MyAPIKey), and store it there for use in your code.
Next, you'll need to prepare a form in Access where you can enter text, submit it to OpenAI, and receive the AI's response. Adjust your form so that it has a text field for user input and a button you can click to process that input with ChatGPT through the API.
The integration requires some key technical components. You need to define both the API's URL endpoint and the specific AI model you want to use. OpenAI provides several models, and the GPT-3.5-turbo variant tends to strike the best balance of speed, cost, and capability for everyday use. More advanced models like GPT-4 are available, but they are slower and more expensive. You can refer to OpenAI's documentation if you want to experiment with those.
To communicate with the API, you will rely on VBA code similar to what is used in web API tutorials. For the connection, you'll use the XMLHTTP object for sending and receiving data. You need to make sure you adapt your code to use the HTTP POST method, as this is required for submitting data to OpenAI properly. This differs from HTTP GET, which simply sends parameters via the URL and is used for more basic requests. POST is preferred for sending structured data such as JSON.
When constructing the request, it's important to include your authorization header with the API key and to specify the correct content type. It's also good practice to add timeout limits to avoid having Access freeze up indefinitely if the request is slow or unresponsive; setting limits for connection, sending, and receiving data can help with this.
That wraps up the first part of this tutorial. If you are following along, the next installment will walk you through finishing the integration and handling the response data from OpenAI. 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
Setting up a paid OpenAI account
Registering and verifying an OpenAI account
Creating an OpenAI API key
Storing the OpenAI API key securely in Access
Overview of OpenAI API endpoint for chat completions
Differences between GPT model versions
Setting up an Access form for user text input
Creating a button to send text to ChatGPT
Configuring VBA to use the XMLHTTP object
Changing from GET to POST requests in VBA
Setting content type and authorization headers
Handling API timeouts in VBA requests
Sending text from Access to OpenAI's API
Receiving and processing ChatGPT responses in Access
Extracting the desired text from the JSON response using FindBetween function
|
