Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
Home > Courses > Access > Seminars > Access SQL Server Online >
 
Access SQL Server Online

Microsoft Access SQL-Server Online Seminar


 S  M  L  XL  FS  |  Slo  Reg  Fast  2x  Buy Now

Welcome

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.

Resources

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

Topics 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. 

Pre-Requisites

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

Version

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.

Notes

Please note that this seminar is NOT downloadable. You must watch it online.

Enroll Today

Enroll now so that you can watch these lessons, learn with us, post questions, and more.

Questions?

Please feel free to post your questions or comments below. Thanks.

Keywords

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

 

Dealing with Attachments Upload Images   Link 
Yecheskel Krishevsky 
3 months ago
In the SQL Server seminar do you cover how to deal with attachments? Currently I have many files stored on a shared folder on the network which users can click a button to open lets say a scanned application of a customer, how do I do this with the backend hosted online?
Richard Rost
3 months ago
I do not cover this, but it's a good question. For something like this, I would set up a shared cloud drive. Personally I like Google Drive. You can install the Backup and Sync software and map everyone a drive letter for shared files. I show how to do this in my New PC Setup video.
Alex Hedley
3 months ago
You could host them on IIS but secure them somehow
Add a Reply
SQL Server Express Upload Images   Link 
Scott Axton 
3 months ago
Richard -
Can you set up and use SQL Server Express using this?
I really don't have a specific use in mind for this Seminar per se from the stand point of having an application open to the world. Read More...
Alex Hedley
3 months ago
You can install Express on a server in your network and connect to it via Access or ASP (local web server), just have the connection string point to that one.
I have it setup locally on my laptop for dev, then it's easier enough to transfer any data or files over to your QA/PROD environment when you know it's working.
Richard Rost
3 months ago
Yes, as Alex pointed out you can certainly use a local SQL Server Express to do this. In fact, one of the things on my TO DO list is to make some SQL Server lessons. However, I would only do that if all of your clients are on the same LAN. Once you start dealing with a 2nd location, you might as well get a hosted solution. The cost is PEANUTS. You don't open your IN-HOUSE SERVER up to hackers by opening a port. You can now hit your data from anywhere - any office, on the road, etc. Just my two cents.
Add a Reply

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

10/10/2021Copy Text from Field
10/10/2021Access for Free
10/9/2021Birthdays
10/8/2021Label Resize
10/2/2021Criticisms of Access
10/1/2021Access Developer 32
10/1/2021Groups
9/29/2021DMin
9/28/2021Track Log On
9/26/2021Sliders
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access seminar sql server online ssms odbc dsn tsql, pass-thru pass-through querydef tabledef relink tables ado createobject adodb.recordset create unique index alter table  Page Tag: whatsnew  PermaLink