Microsoft Access SQL-Server Online Seminar
Welcome to the Microsoft Access SQL Server Online Seminar. You will learn how to connect multiple users to an online SQL Server database using either Microsoft Access or a simple Web browser as a front-end. This will allow anyone, anywhere in the world with an Internet connection, to use your Microsoft Access database, or view your data in their browser.
We will discuss why you would want to connect your Access database to an online SQL Server database. What the pros and cons are. What you need to get started, and what your end users need to connect to your database.
We will setup a Web and SQL Server hosting account with my preferred provider, WinHost. You will learn how to set up a new domain name or use an existing one. We'll see how to use their site control panel. We will create our first SQL Server Database online and get the connection string so we can connect to it from Access and the Web.
Next we will connect Access to SQL Server. We will install the SSMS (SQL Server Management Studio) which is a free download from Microsoft. You will learn how to set up an ODBC Data Source File (DSN). We will then export our Customer table to SQL Server, delete it from Access, and then link to the online copy.
We will now link the remaining tables in our database. We'll learn about the differences between Access SQL and T-SQL (Transact-SQL) which is what SQL Server uses. You will learn how to create pass-through queries and what their significance is. You will learn about QueryDefs and how to create queries on the fly in VBA. You'll learn how to store your database connect string in the TempVars.
We will learn how to relink our tables dynamically using VBA. You'll learn about the TableDefs collection. We'll see how to connect to the tables directly without linking using ADO recordsets. We'll see what happens if we change the password on the database server.
We will learn how to secure the database to prevent unauthorized users from getting access. You will see how to lock down the database, hide the Navigation Pane, minimize the Ribbon, disable shortcut keys and menus, and most importantly disable the Access Startup Bypass Key (Shift). We'll set up an Admin menu where you can easily lock and unlock the database to prepare it for distribution. We'll make an encrypted ACCDE file that you will give to your end users.
You will learn how to hide Access objects including your linked tables and pass-thru queries. We'll see how there is a problem where your tables become read-only unless you specifically set a primary key (indexed) field, so we'll learn how to do that using SQL. We will create a distribution copy front-end file, copy it to a different computer not on the same network, and test it to make sure it connects to the server.
If you want to create new tables, it's easiest to do so in Access and export them like we did earlier. However you will want to make changes to existing tables. So, we will learn how to do that in SSMS using both the GUI or SQL. We will then relink the tables. We'll also discuss backup options including a manual backup and an automated nightly backup service.
If you want to allow users to work with your data without Microsoft Access, you can create a Web-based interface using ASP (Active Server Pages). In this lesson, I will show you how to use the EditPlus Web editor and create some basic pages using ASP so that you can generate a customer list and select a specific customer to display online.
Finally, we'll discuss security a little more. We'll talk about how Access will cache the database connection password in memory while your database is open. So we can achieve good security by simply linking to a single table on startup, after getting the logon password from the user. This way we don't have to store it in any other connections or our pass-thru queries.
It is strongly recommended that you have completed my entire Access Beginner and Expert series. My Developer 1 class is highly recommended so you understand the basics of programming in VBA. If not, at least watch my free Intro to VBA video. Developer 16 is recommended if you want to learn about Recordsets (although this is not required).
I also recommend my Access SQL Seminars which will teach you how to work with the SQL language. This is critical if you want to get the most out of working with SQL Server. I will teach you how to secure the online database with a single logon password for everyone in this course, however if you want to set up individual logons, groups, and permissions in your Access database, I recommend my Security Seminar.
I am using Microsoft 365, roughly the equivalent of Access 2016 or 2019. Everything in this seminar should work with every version of Access back to 2007.
Please note that this seminar is NOT downloadable. You must watch it online.
Enroll now so that you can watch these lessons, learn with us, post questions, and more.
Please feel free to post your questions or comments below. Thanks.
How to export from Access tables to SQL Server, how to export from Microsoft Access to SQL Server, how to convert an Access database to SQL Server, how to migrate tables and data from Access to SQL Server, migrate data from MS Access to SQL Server database, SQL Server connection string, SQL Server Management Studio, SSMS, ODBC Data Source Connection, File Data Source, DSN, Publish from Access to SQL Server, linked tables, TSQL, Transact-SQL, Pass-Through Query, QueryDefs, TableDefs, TempVars, relink tables in VBA, Recordsets, DAO, ADO, CreateObject, ADODB.Connection, ADODB.Recordset, Create Unique Index, Alter Table, Identity Specification
You may want to read these articles from the 599CD News: