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.
599cd.com/AccessSQLServerOnline
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.
SEMINAR GOALS
Setup Web/SQL Server Hosting at WinHost
Connect Access to SQL Server
SQL Server Management Studio
Link to Tables on the Server
Get Data from the Server using Pass-Thru Queries
Relink Tables using VBA Code
Connect Directly to Tables using ADO Recordsets
Securing Your Database
Locking Down the Front-End
Disabling the Access Bypass Key
Hiding Access Objects (Tables, Queries)
Editing the Back-End Tables (SSMS and SQL)
Display Data on the Web using ASP
WHAT'S COVERED
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.
LEARN MORE
599cd.com/AccessSQLServerOnline
TOPICS
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