Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > Courses > Access > Seminars > SQL Server Online >
Access SQL Server Online

Microsoft Access SQL-Server Online Seminar


 S  M  L  XL  FS  |  Slo  Reg  Fast  2x  |  Bookmark 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.

To be clear, this seminar does NOT cover setup of the SQL Server software itself, nor is this a class on database theory, cloud computing concepts, etc. This seminar has a very specific set of goals which are outlined in detail above. Those primarily include teaching you how to configure an online SQL Server at WinHost, and to connect to that from Microsoft Access and/or a web site using ASP. This is not a general SQL Server course. I have plans to make one of those in the future. This is not that.

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. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.

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

 

Comments for Access SQL Server Online
 
Age Subject From
3 monthsQuery Text FieldElad Boro
4 monthsIs Compact and Repair NeededMargaret Simcoe
16 monthsDealing with AttachmentsYecheskel Krishevsky
16 monthsSQL Server ExpressScott Axton

 

Start a NEW Conversation
 
Only students may post on this page. Click here for more information on how you can set up an account. If you are a student, please Log On first. Non-students may only post in the Visitor Forum.
 
Subscribe
Subscribe to Access SQL Server Online
Get notifications when this page is updated
 
 

Check out these other pages that may be of interest to you:

11/25/2022Application Title
11/24/2022Full HTML Editor in Access
11/24/2022Change Database Title
11/23/2022Other Names
11/21/2022Check Icon File
11/21/2022Check Icon File
11/18/2022Archive Records
11/17/2022InStr Function
11/16/2022Numbers As Text
11/15/2022Merge Tables
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
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
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
amicron@gmail.com
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  Microsoft Access SQL Server Online