Introduction

Welcome! Connect Users & Secure SQL Data

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this lesson, we provide an introduction to the Microsoft Access SQL Server Online Seminar, covering how to connect multiple users to an online SQL Server database using Microsoft Access or a web browser. We will discuss setting up hosting at Winhost.com, installing SQL Server Management Studio, linking tables, using pass-through queries, relinking tables with VBA, working with ADO recordsets, and basic security measures such as disabling the Access bypass key and hiding database objects. We will also talk about editing and backing up SQL Server tables, and displaying data on the web using ASP pages.

Navigation

Prev: Lessons
Next: Lesson 01

Keywords

Access Seminars, SQL Server, ODBC connection, web browser front end, Winhost, SQL Server Management Studio, pass-through queries, VBA relink tables, ADO recordsets, Access security, ACCDE, Transact-SQL, table indexing, ASP web database, EditPlus

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to the Microsoft Access SQL Server Online Seminar brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

This seminar will teach you how to connect multiple users to an online SQL Server database using Microsoft Access or a web browser as your front end. We will learn how to set up web and SQL Server hosting at Winhost.com. We will connect Microsoft Access to the SQL Server.

We will install the SQL Server Management Studio, which is free from Microsoft, to maintain our server. We will see how to link our tables to the server. We will learn how to get data from the server using pass-through queries, which allow the server to do most of the work before sending the data down to Access.

We will learn how to re-link our tables using VBA code. We will see how to connect directly to tables using ADO recordsets. We will learn how to secure the database, including locking down the front end and disabling the Access bypass key so people cannot get into your code. We will learn how to hide Access objects like our tables and queries, anything you do not want your end users seeing.

We will learn how to edit the back end tables, so once you have exported a table from Access up to SQL Server, you can make changes. Finally, we will learn how to display data on the web using a simple web browser and ASP active server pages to connect to our database.

This is a developer level seminar. I strongly recommend that you have taken my beginner and expert classes and up to Developer Level 1, so you know the basics of VBA. I also recommend you take Developer 16 if you want to learn the basics of recordsets before starting this class, although that is not a firm requirement. You do not have to do recordset programming to benefit from today's seminar.

At the bare minimum, if you have not taken Developer 1, watch my free Access VBA intro class. There is a link right there. I will put it down in the links section below.

To use Access and SQL Server, I strongly recommend also that you have taken my SQL language seminars. I have three different seminars that cover the basics of the SQL language: how to select queries, modify data, and so on. Again, this is not required, but you will get more out of this class if you already know SQL.

Another recommendation is to take my Security seminar. I will show you today how to secure your database and lock it down to prevent unauthorized use. However, I am going to show you with a simple one-database password. Anyone who knows the password can get into the database. If you want to set up user-level security with different users and groups and permissions, I cover that in my Access Security seminar.

I will be using the equivalent of Access 2019. I have a Microsoft 365 subscription. However, the techniques I am going to show in today's class should work for pretty much every version of Access going back to about 2007.

If you have any questions regarding the material covered in today's class, just scroll down to the bottom of the page and post them there. Be sure to also take a minute to read through any other questions that have been posted, as your question may have already been answered. Also make sure you click on the subscribe button to get notified of any other questions and comments that are posted for this class, including updates. Sometimes I post update videos. Be sure to stop by my Access forum if you have any other Access related questions.

Now let's take a closer look at exactly what is covered in today's seminar.

In lesson one we are going to discuss why you would want to connect your Access database to an online SQL Server. We will talk about the pros and cons. Then we will discuss what you need to get started and what they will need to connect to your database.

In lesson two we are going to set up an account at Winhost.com. We will talk about setting up a new domain name or transferring an existing one if you have one already. We will see how to use the Winhost Site Control Panel. We will create our first online SQL Server database and I will show you how to get the connection string.

In lesson three we are going to connect our Access database to SQL Server. We are going to set up the SQL Server Management Studio. We are going to set up an ODBC data source file to connect to SQL Server. Then we are going to export the customer table up to the server, delete it from the Access database, and then link to it up on the server.

Lesson four is all about properly querying data on the server. We are going to link our remaining tables. We are going to talk about the differences between Access SQL and Transact-SQL, which is what SQL Server uses.

We are going to learn about pass-through queries and why they are important for downloading the right amount of data from the server. We are going to learn about the QueryDefs collection and how to create a query on the fly using VBA. We will learn how to store our connect string in TempVars.

In lesson five we are going to see how to relink our tables in VBA code using something called a TableDef. We will learn about the TableDefs collection. Then we will learn about ADO recordsets, which are very similar to DAO recordsets for those of you who have taken my developer classes.

I will show you how to change the database password on the server. Then we will see how you can avoid a little quirk with the database not connecting to the tables correctly. All you have to do is learn how to read a value or ping a pass-through query when the database loads. That is a cool little trick we will learn in this lesson.

In lesson six we are going to start talking about security for your database. We are going to lock down the database. We are going to set up an admin menu with two buttons on it: Lock Database and Unlock Database. The Lock Database button turns off all the features you do not want your end user having, the navigation pane, and so on. Plus we will see how to disable the bypass key so users cannot hold down the shift key to get into your database and see the navigation pane. Then we will make an ACCDE file to encrypt it and lock it up.

Part two of security: we are going to see how to hide your linked tables and pass-through queries to make it harder for someone to import them into a different database. Then we are going to talk about a read-only problem with linked tables. I will show you how to create indexes in each of those tables so you can connect to them on the SQL Server and have read-write access. Then we are going to create and test a distribution copy on a second PC.

In lesson eight we are going to learn how to query data directly in SSMS, the SQL Management Studio. I will show you how to alter your tables. If you need to create new tables, it is best to create them in Access and publish them up like we did before. However, you are going to want to make changes to your existing tables without having to re-upload them. So I will show you how to make alterations to your tables on the server using either SQL or the editor in SSMS. Then you will have to relink your changes.

Then we will talk about some backup options, how to manually back up your database, and I will show you their automated nightly backup at Winhost.

In lesson nine we are going to see how to connect to our SQL Server data on the web using ASP directly from our website. I am going to talk about the EditPlus Web Editor, but you can use whatever web editor you are comfortable with. We are going to use ASP. We will create our first ASP page, default.asp. We will connect to SQL using ADO. We will display a customer list from our customer table. We will select a specific customer using a query string. I will teach you about that and we will display the customer details that the user clicks on.

In lesson ten we are going to talk about security one more time. We are going to talk about how Access caches passwords for your connections to the server and that you do not have to store them in your queries once you have actually reestablished that connection. Then we will talk one more time about the benefits of pass-through queries.

Thanks for watching.

Quiz

Q1. What is the primary goal of the seminar presented by Richard Rost?
A. To connect multiple users to an online SQL Server database using Microsoft Access or a web browser as the front end
B. To teach basic Microsoft Access usage for single users
C. To develop e-commerce websites using only Access
D. To demonstrate the latest features of Microsoft Windows

Q2. Which hosting provider is used in the seminar for setting up web and SQL Server hosting?
A. GoDaddy
B. Winhost.com
C. Bluehost
D. Google Cloud

Q3. What free tool from Microsoft is installed for server maintenance?
A. Visual Studio Code
B. SQL Server Management Studio
C. Power BI Desktop
D. Notepad++

Q4. What is the purpose of pass-through queries in this seminar?
A. To let Access perform all data processing
B. To allow the server to process queries and optimize data transfer to Access
C. To bypass SQL Server authentication
D. To create backup copies of the database

Q5. Which programming language is recommended to best understand the techniques taught in the seminar?
A. Python
B. VBA
C. JavaScript
D. C#

Q6. What method is shown for adding security to your database in Access?
A. Complex encryption algorithms only
B. Simple database password and disabling the Access bypass key
C. Two-factor authentication only
D. Biometric authentication

Q7. How does the seminar recommend hiding sensitive Access objects from users?
A. Move them to a hidden folder in Windows Explorer
B. Use Access features to hide tables and queries from the navigation pane
C. Store them in a separate Excel spreadsheet
D. Encrypt them with external software

Q8. What is a main benefit of linking Access tables to SQL Server?
A. Local data can be read-only
B. Multiple users can access and update data online
C. Only one user can access the data
D. Data is stored in Access only

Q9. What type of queries are emphasized for efficient data transfer from the server to Access?
A. Simple select queries in Access
B. Pass-through queries
C. Aggregate queries in Excel
D. Action queries only

Q10. What does the seminar recommend regarding the SQL language before starting?
A. No prior knowledge of SQL is needed
B. Take at least the basic SQL language seminars to understand query concepts
C. Only learn about Excel functions
D. Only hardware knowledge is necessary

Q11. How can you alter tables after exporting them from Access to SQL Server?
A. Only by exporting them again from Access
B. By either using SQL or the editor in SQL Server Management Studio
C. By using PowerPoint
D. By sending them through email

Q12. In terms of distributing the database to users, what is one security step mentioned?
A. Create an ACCDE file to encrypt and lock the database
B. Share the unencrypted ACCDB file
C. Use only manual backups
D. Distribute raw SQL script files

Q13. How does the seminar suggest handling read-only problems with linked tables?
A. Accept that tables are always read-only in Access
B. Create indexes in the SQL Server tables for read-write access
C. Move the tables back to Access
D. Use only Excel for editing data

Q14. What technology is used in the seminar to display data on the web from SQL Server?
A. PHP and MySQL
B. ASP active server pages using ADO
C. HTML only
D. Node.js and MongoDB

Q15. What is explained about Access caching SQL Server connection passwords?
A. Passwords are never cached by Access
B. Once the connection is established, Access can remember the password, so it does not have to be stored in queries
C. Passwords must always be included in each query explicitly
D. SQL Server does not use passwords

Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-A; 13-B; 14-B; 15-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 video from Access Learning Zone focuses on how to connect multiple users to an online SQL Server database using Microsoft Access or a web browser as the front end. I will walk you through setting up web and SQL Server hosting with Winhost.com, connecting Microsoft Access to SQL Server, and managing your server using SQL Server Management Studio, which is available as a free download from Microsoft. You will learn how to link your Access tables to the server, and how to use pass-through queries so the SQL Server does most of the processing work before sending data to Access.

We will also explore how to relink tables using VBA code and how to work with ADO recordsets to interact directly with your data. Database security will be an important topic, including how to lock down the front end of your database and disable Access shortcuts so users cannot access your code. I will show you how to hide tables and queries in Access to prevent end users from seeing anything you want to keep hidden.

There will be a segment on editing the back-end SQL Server tables after you have exported data from Access. You will also learn how to present data on the web using a standard web browser and ASP (Active Server Pages) to connect to your database.

This seminar is designed for developers. It is strongly recommended that you have already completed the beginner, expert, and at least Developer Level 1 courses, as a solid understanding of VBA is necessary. Developer 16, which covers recordsets, is helpful but not absolutely required. Even if you do not write recordset code, you will still benefit from this seminar.

If you have not yet taken Developer 1, I suggest watching my free Access VBA introduction class to cover the essentials. In addition, familiarity with the SQL language is recommended for this material. I offer three seminars that explain the basics of SQL, including how to select and modify data. While prior knowledge is not mandatory, understanding SQL will make the seminar more valuable.

For those interested in securing their databases further, my Security seminar offers a more advanced look at user-level security, creating users, groups, and setting permissions. In this seminar, I will cover simple database password protection, which means anyone with the password can access the database, but for more granular security, refer to the other class.

I will be using Access 2019, running as part of Microsoft 365. However, the methods shown should work in all Access versions back to 2007.

If you have questions about the seminar, please scroll down to the bottom of the page to post them. Check through existing questions first, as yours may already have been answered. Make sure to subscribe for updates on questions, comments, or any new content related to this class. I also encourage you to visit my Access forum for any other questions about Access.

Here is what is covered in this seminar:

Lesson one begins with reasons for connecting Microsoft Access to an online SQL Server database, discussing the advantages and disadvantages, and what you will need to get connected.

Lesson two guides you through setting up a Winhost.com account, arranging a new domain name or transferring one you own, using the Winhost Site Control Panel, creating your first online SQL Server database, and retrieving your connection string.

In lesson three, you will connect your Access database to SQL Server. This involves setting up SQL Server Management Studio, configuring an ODBC data source file for the connection, uploading an Access table (such as your customer table) to the server, removing it from your Access database, and then linking to the online version.

Lesson four focuses on querying server data correctly. You will link additional tables and learn about key differences between Access SQL and SQL Server's Transact-SQL. The lesson covers the value of pass-through queries for optimizing server data downloads. You will also learn about the QueryDefs collection, how to create queries dynamically with VBA, and how to use TempVars to store your connection string.

Lesson five shows you how to relink tables using VBA and TableDefs. You will learn about the TableDefs collection and how ADO recordsets work, which are very similar to the DAO recordsets from earlier developer classes. I will review how to change the database password on the server, and how to avoid certain connection problems by reading a value or pinging a pass-through query when your database loads.

Lesson six shifts to database security. You will set up an admin menu with Lock Database and Unlock Database buttons. The Lock Database button switches off features you do not want end users accessing, such as the navigation pane. You will learn how to disable the Access bypass key so users cannot override security with the shift key. Finally, you will compile the database into an ACCDE file to encrypt and protect it.

You will further secure your database by hiding linked tables and pass-through queries, making it harder to import them into another database. I will address a common read-only problem with linked tables and explain how to create indexes on your SQL Server tables for proper read-write access. There will be a demonstration of testing a distribution copy on a second computer.

Lesson eight dives into using SQL Server Management Studio to query and alter your tables directly. If you need to add new tables, the best practice is to create them in Access and upload them to SQL Server. However, for modifications to existing tables, I will show you how to make changes directly on the server and then relink them in Access. You will also learn about both manual and automated backup options, including nightly backups through Winhost.

Lesson nine covers connecting to your SQL Server database via the web using ASP. I will introduce the EditPlus Web Editor, but any web editor can be used. You will see how to create an ASP page to connect to SQL Server using ADO, display a customer list, select an individual customer using a query string, and show customer details.

Lesson ten closes out with additional information about security. I explain how Access caches passwords for server connections and why you do not have to store them in every query after establishing the connection. The advantages of using pass-through queries are reinforced one more time.

For a complete video tutorial with step-by-step instructions on everything mentioned here, visit my website at the link below. Live long and prosper, my friends.

Topic List

Connecting Access to online SQL Server databases
Setting up web and SQL Server hosting at Winhost
Installing SQL Server Management Studio
Linking Access tables to SQL Server
Using pass-through queries in Access
Re-linking tables using VBA code
Connecting to tables with ADO recordsets
Securing the Access database front end
Disabling the Access bypass key
Hiding Access objects from end users
Editing SQL Server back end tables
Exporting tables from Access to SQL Server
Displaying data on the web using ASP
Creating an account at Winhost
Setting up a domain name for hosting
Using the Winhost Site Control Panel
Obtaining the SQL Server connection string
Setting up an ODBC data source file
Exporting and linking customer tables
Differences between Access SQL and Transact-SQL
Using the QueryDefs collection in VBA
Storing connection strings in TempVars
Relinking tables with the TableDefs collection
Changing database password on SQL Server
Troubleshooting database connectivity
Locking down features: navigation pane and ribbons
Making an ACCDE file to encrypt the database
Hiding linked tables and pass-through queries
Creating indexes on SQL Server tables for read-write access
Creating and testing distribution copies
Querying and altering data in SQL Server Management Studio
Manual and automated backup options at Winhost
Connecting ASP pages to SQL Server data
Displaying data in a web browser using ASP
Access password caching with server connections