In this post I am going to show you how you can connect your Access database to a Microsoft SQL Server 19 running on an Amazon Virtual Machine.
Something great about the current technology is that you can have a version of something that would cost you BIGGGGGGGG Money in the past!
In this case it’s MS Windows Server 2019 with SQL Server 2019 Standard.
In the past, I had to figure out where I was going to find all the software, and even if it was around, and then if I had a spare computer around with the hardware required to run it. Microsoft has all sorts of “minimum hardware requirements” information.
Well now with a VM in the cloud, you can basically skip all of that, and “spin up” any kind of machine you desire!
I have spun up a VM on the Google cloud, but since NASA allows Amazon cloud use, I wanted to see how I could connect the Access database to SQL Server instance on Amazon VM.
So the main idea of this post is not how to connect the database and then refine it. It’s just to make it “live”, to see the “pulse”, and then refine it later.
Step 1: Setup Your Amazon Cloud Instance
First you will have to search for the “amazon cloud” or go to https://aws.amazon.com/products/storage/
After you go to the link you will need to create or login to you account then you’ll be redirected to something that looks like this:
Now you have a “cloud instance”
You are going to be paying for the time the instance is running (see red square). If it’s a yellow dot, the instance is just “sleeping”, and you’ll just be paying for “squatting” on their space. Which is less than 97% (or more) of the cost you would pay if it’s “running”.
Note: your VM will get an IP address, so you can access it, but if you turn it off, your IP address will change when you turn it back on (so you’ll have to change all your DSN’s to the new IP address)!
Step 2: How To Access Your New Amazon Cloud Instance
You’ll be accessing your VM using the “Remote Desktop Connection”
You’ll be directed to generate a PEM file for security and then you’ll get a screen like this:
After you make your connection, you’ll be able to see the desktop of your new Windows Server.
Congratulations! Go ahead and pat yourself on the back.
Step 3: Set Up Your SQL Server Database
Now you just need to recreate your database tables on your SQL Server.
We are not going to learn all about Windows Administration and user account setup in this post so all we need now it the SQL Server Management Studio.
Create your database on the SQL Server (right click Database > New Database)
this one is called “interface”
Create your database, and then right click tables and click “New Table”. Just set up one table right now, because you’ll do the same with the others.
Step 4: Set Up Your SQL Server Logins
You are going to need 2 logins to new SQL Server,
1. 1 to login to the server
2. 1 to login to the database
This one is for the SQL Server:
This user is allowed to login to the SQL Server, and ability to login to the “interface” database we created on the SQL server using “SQL Server Authentication”, since we are not physically connected to a Windows server network that has all of their Windows account information, so we cannot use “Windows Authentication”.
This login will be called “eman”
They’ll use SQL Server authentication
I’ll set their database password (because I have to set up their DSN)
I want them to login only to the “interface” database.
This one is for the Database:
This person (eman) can login to the database, and I set them as “db_owner” for all the database objects.
Step 5: Set Up Your DSN On Client Machine
OK, now the SQL Server can receive connections.
We’ll go to the client machine and setup a DSN using the ODBC 32 bit.
Step 6: Link To Your SQL Server Tables
That’s all for now.
Let me know if you have any questions.
PS. There’s more information about RDP in my post: How To Copy Files Using Remote Desktop To Your AWS Virtual Machine
Shared Access Database Management
Here is a handy setup for those who need to manage a shared access database. Either you can create this from scratch as per the example or add the new tables to your existing database. This setup will track: 1. the users currently using the database 2. what time the user and computer name currently […]
Access Database Users And Permissions Example
This post is a compilation of two of my previous posts: https://vbahowto.com/ms-access-object-security/ https://vbahowto.com/ms-access-login-form-revised/ This elaborates on the two posts a bit: First, here are all of the database objects: There are 2 tables here: tblLogin and tblAdmin Here are the main forms: frmLogin – this is the form that opens when the database opens Here […]
MS Access Object Security
Today’s post is based on a question that I received. We are going to assume that there are multiple users who have rights to view different database objects. So in this example there are 3 user logins with different rights to access 3 forms and 3 reports. Here is an overview of the database setup: […]
How To Send Email With Access And Outlook VBA
There are 3 things you probably want to send from your Access application. You probably want to send an Access report or send an attachment(s). 1. Email An Access Report (SendObject) 2. Email An Access Report With Attachments (Outlook Automation) 3. Email With Attachment(s) (Outlook Automation) First, if you are in the database, and want […]