|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Access SQL Server Online Microsoft Access SQL-Server Online Seminar WelcomeWelcome 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. Not Sure?If you're not sure that SQL Server is the right move for you, watch the introduction and the first 2 lessons which are free. They will explain it what the seminar covers and how to set up your account at WinHost. ResourcesSeminar Goals
Topics CoveredWe 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-RequisitesIt 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. VersionI 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. NotesPlease 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. Using Azure?The SQL Server Online Seminar will definitely help you if you are connecting Microsoft Access to an Azure SQL Server. Azure SQL is basically Microsoft's cloud-based version of SQL Server. It uses the same core database engine, so once you have the connection set up, everything else works almost exactly the same as a regular on-premises SQL Server. You still use SQL Server Management Studio (SSMS) to manage your database, run queries, create tables, and so on. Azure also gives you a web-based portal to handle things like creating the server, setting up firewall rules, and monitoring performance, but for most of the actual database work, SSMS is still the best tool. In Access, once you set up your ODBC connection to Azure SQL, it works just like any other SQL Server back end. Your linked tables, pass-through queries, and VBA code all work the same way. There is really no difference in how you build or manage your Access front end whether your SQL Server is local, hosted on your network, or up in Azure. So yes, the information in my seminar definitely applies. The only extra steps you will need are for the initial Azure setup and security configuration, but after that, it is business as usual. Officially, I use WinHost for my SQL Server and website hosting. It is the only platform I recommend and support. However, I have had several students use the material from this seminar with Azure (and other web hosts) without problems. Note on Security and ComplianceThis seminar will teach you the tools and techniques to secure your Microsoft Access front-end and SQL Server back-end, including how to avoid the risks of permanently linked tables, how to connect dynamically in VBA, and how to lock down your database. These lessons will show you best practices for keeping your data safe and secure. Please understand, however, that I cannot guarantee compliance with any specific government, corporate, or industry regulations (HIPAA, GDPR, CCPA, PCI-DSS, etc.). Every organization has its own requirements. This seminar gives you the knowledge you need, but it is ultimately your responsibility to implement those practices properly and ensure they meet the standards of your particular situation. I also strongly recommend my Access Security Seminar as a supplement to this course. It includes more information on how to properly secure your Access front-end against intrusion. Enroll TodayEnroll 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. KeywordsHow 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
IntroIn this lesson you will learn how to connect a multi-user Microsoft Access database to an online SQL Server, allowing access either through Access itself or a web browser. We will walk through setting up hosting with Winhost.com, connecting Access to SQL Server, installing and using SQL Server Management Studio, and linking tables using ODBC and pass-through queries. The seminar covers managing connections with VBA and ADO recordsets, securing your database by locking down the front end, hiding objects, and disabling the bypass key. You will also see how to display data on the web with ASP and how to update your back end tables.TranscriptWelcome 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 can't get into your code. We will learn how to hide Access objects like our tables and queries, anything you don't want your end users seeing. We will learn how to edit the back end tables so once you've 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 Developers 16 if you want to learn the basics of recordsets before starting this class, although that's not a firm requirement. You don't have to do recordset programming to benefit from today's seminar. At the bare minimum, if you haven't taken Developer 1, watch my free Access VBA intro class. There's a link right there. I'll 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've got three different seminars that cover the basics of the SQL language: how to select queries, modify data, and so on. Again, this isn't required, but you'll 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 posted for this class, including updates. Sometimes I post update videos. Stop by my Access forum if you have any other Access-related questions. Now let's take a closer look at exactly what's 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'll talk about the pros and cons. Then we'll 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'll talk about setting up a new domain name or transferring an existing one if you have one already. We'll see how to use the Winhost Site Control Panel. We'll create our first online SQL server database and I'll show you how to get the connection string. In lesson three, we are going to connect our Access database to SQL Server. We're going to set up the SQL Server Management Studio. We're going to set up an ODBC data source file to connect to SQL Server. Then we're 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're going to link our remaining tables. We're going to talk about the differences between Access SQL and Transact-SQL, which is what SQL Server uses. We're going to learn about pass-through queries and why they're important for downloading the right amount of data from the server. We're going to learn about the QueryDefs collection and how to create a query on the fly using VBA. We'll learn how to store our connect string in TempVars. In lesson five, we're going to see how to relink our tables in VBA code using something called a TableDef. We'll learn about the TableDefs collection. Then we'll learn about ADO recordsets, which are very similar to DAO recordsets for those of you who have taken my developer classes. I'll show you how to change the database password on the server. Then we'll 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's a cool little trick we'll learn in this lesson. In lesson six, we're going to start talking about security for your database. We're going to lock down the database. We're 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 don't want your end user having, the navigation pane, and so on. Plus, we'll see how to disable the bypass key so they can't hold down the Shift key to get into your database and see the navigation pane. Then we'll make an ACCDE file to encrypt it and lock it up. Part two of security: we're 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're going to talk about a read-only problem with linked tables. I'll 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're going to create and test a distribution copy on the second PC. In lesson eight, we're going to learn how to query data directly in SSMS, the SQL Management Studio. I'll show you how to alter your tables. If you need to create new tables, it's best to create them in Access and publish them up like we did before. But you're going to want to make changes to your existing tables without having to re-upload them. I'll show you how to make alterations to your tables on the server using either SQL or the editor in SSMS. Then, of course, you'll have to relink for the table changes. We'll talk about some backup options, how to manually back up your database, and I'll show you their automated nightly backup at Winhost. In lesson nine, we're going to see how to connect to our SQL Server data on the web using ASP directly from our website. I'm going to talk about the EditPlus Web Editor, but you can use whatever Web Editor you're comfortable with. We are going to use ASP. We'll create our first ASP page, default.asp. We'll connect to SQL using ADO. We'll display a customer list from our customer table. We'll select a specific customer using a query string. I'll teach you about that, and we'll display the customer details that the user clicks on. In lesson ten, we're going to talk about security one more time. We're going to talk about how Access caches passwords for your connections to the server and that you don't have to store them in your queries once you've actually reestablished that connection. Then we'll talk one more time about the benefits of pass-through queries. Thanks for watching. QuizQ1. What is the main goal of this Microsoft Access SQL Server Online Seminar?A. To teach how to create reports in Access B. To connect multiple users to an online SQL Server database using Access or a Web browser as the front end C. To develop mobile applications with Access D. To use Excel as a database for Access Q2. Which hosting provider is demonstrated for Web and SQL Server hosting setup in this seminar? A. GoDaddy.com B. Bluehost.com C. Winhost.com D. HostGator.com Q3. What free tool from Microsoft will be installed for server maintenance in this seminar? A. Visual Studio B. Microsoft Edge C. SQL Server Management Studio (SSMS) D. Azure Data Studio Q4. What is emphasized as the advantage of pass-through queries? A. They make reports look better in Access B. They allow the server to process most of the work before sending data to Access C. They secure your Access database better D. They export data directly to Excel Q5. According to the seminar, what is needed to re-link tables in Access using code? A. A macro B. VBA code using TableDefs C. Excel formulas D. HTML code Q6. Which recordset model is mentioned as similar to ADO recordsets? A. DAO recordsets B. XML recordsets C. JSON recordsets D. ODBC recordsets Q7. How does the seminar propose to improve database security? A. Only using strong passwords B. Locking down the front end and disabling the Access bypass key C. Disabling SQL Server authentication D. Making backups only Q8. For which task is the Winhost Site Control Panel used in the seminar? A. Editing Access reports B. Creating a new domain and first online SQL Server database C. Running Access macros D. Designing HTML forms Q9. What is the difference discussed between Access SQL and SQL Server's language? A. Access SQL is more secure B. Transact-SQL (T-SQL) is used by SQL Server, which has differences from Access SQL C. They are exactly the same D. SQL Server can only run queries from Access Q10. When making modifications to existing tables on SQL Server, what is recommended in the seminar? A. Always delete the table first B. Use SSMS to alter the tables directly, then re-link them in Access C. Use Access VBA only D. Modify and re-export all tables from Access Q11. What is shown as a solution to the read-only problem with linked tables? A. Hiding the tables B. Creating indexes in those tables C. Disabling ODBC connections D. Using only queries, not tables Q12. How does the seminar demonstrate displaying SQL Server data on the web? A. Using PowerPoint B. With ASP active server pages and a Web browser C. Exporting to CSV for download D. Sending emails with data attached Q13. What prior knowledge is strongly recommended before taking this seminar? A. Advanced C++ programming B. Access beginner, expert, and developer-level classes, especially Developer 1 and SQL seminars C. Excel pivot tables D. Python for data science Q14. What backup solution does the seminar introduce for the SQL Server database? A. Manual copying to USB drive only B. Only using SQL scripts C. Automated nightly backups provided at Winhost D. Printing hard copies of tables Q15. The seminar explains that password caching for server connections in Access means: A. Passwords must always be re-entered for every data operation B. Users need multiple passwords for each session C. Passwords do not need to be stored in queries after connection is established D. Passwords can be disabled entirely Answers: 1-B; 2-C; 3-C; 4-B; 5-B; 6-A; 7-B; 8-B; 9-B; 10-B; 11-B; 12-B; 13-B; 14-C; 15-C 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. SummaryToday's video from Access Learning Zone is a comprehensive Microsoft Access SQL Server Online Seminar. In this seminar, I will guide you through the process of connecting multiple users to an online SQL Server database using either Microsoft Access or a web browser as your front end. You will learn to set up web and SQL server hosting with Winhost.com and connect your Access database to SQL Server. We'll also cover how to install the free SQL Server Management Studio from Microsoft, which will help you maintain your server.I will show you how to link your Access tables to SQL Server, retrieve data from the server using pass-through queries so the server handles most of the work before sending results to Access, and re-link tables using VBA code. Additionally, you'll learn how to connect directly to your back end tables using ADO recordsets. Security is an important focus of the seminar. I will demonstrate how to secure your database by locking down the front end and disabling the Access bypass key to prevent unauthorized users from viewing your code. We will look at techniques for hiding Access objects like tables and queries from users, and how to modify back end tables after they have been exported from Access to SQL Server. To round out the session, we will cover how to display your database data on the web. You will see how to use a simple web browser and ASP (Active Server Pages) to connect to your database and display information online. This is a developer-level seminar. I strongly recommend that you have already taken my beginner and expert classes, as well as Developer Level 1, so you understand the basics of VBA. Developer 16 is also helpful if you want a good foundation in recordsets, although it is not required. Even if you do not plan on doing any recordset programming, you will still find the content of this seminar useful. At the very least, if you have not completed Developer 1, please watch my free Access VBA intro class, which is linked below. For the best experience with Access and SQL Server, I suggest taking my SQL language seminars which cover the essentials of selecting and modifying data in SQL. Again, these courses are not absolutely required, but the content in this seminar will make more sense if you already understand SQL fundamentals. I also recommend my security seminar if you plan on setting up user-level security with different permissions for different users and groups. In this course, I will only be showing you how to add a simple single password to the database, which grants access to anyone who knows it. For more advanced security, see my Access Security seminar. Throughout the seminar, I will be using Access 2019 as part of my Microsoft 365 subscription. However, the techniques I demonstrate should work in most versions of Access going back to 2007. If you have questions on any of the material from this class, scroll down to the bottom of the page and post your questions there. Be sure to read through other submitted questions because your topic might already have been addressed. Remember to subscribe for notifications about future questions, comments, or updates, as sometimes I post update videos. Feel free to participate in my Access forum for any other Access questions you might have. Now, here's a detailed look at what we will be covering: In the first lesson, I will explain the benefits and drawbacks of connecting your Access database to an online SQL Server, along with what tools and resources you and your users need to get started. Lesson two features setting up an account at Winhost.com, registering a new domain name (or transferring an existing one), and working with the Winhost Site Control Panel. I will walk you through setting up your first online SQL Server database and obtaining the necessary connection string. In the third lesson, you will learn to connect Access to SQL Server. I'll go through installing SQL Server Management Studio, setting up an ODBC data source file, exporting tables like the Customer table to the server, deleting them from Access, and linking your Access database to the server-hosted versions. Lesson four is focused on how to properly query your server-hosted data. You will link the rest of your tables, understand the differences between Access SQL and Transact-SQL (which SQL Server uses), and see why pass-through queries are essential for efficient data transfer. I'll discuss the QueryDefs collection and show you how to generate queries on the fly using VBA, as well as how to store your connection string using TempVars. In lesson five, you'll learn to relink tables with VBA code through the TableDef object and how to work with the TableDefs collection. We'll cover ADO recordsets for those familiar with the DAO recordset model. I will also show you how to change the SQL Server database password and share tips for resolving common connection issues, such as making sure your database pings a pass-through query when it loads. Security takes center stage in lesson six. You will see how to lock down your database by building an admin menu with Lock Database and Unlock Database options. Locking the database removes features you do not want general users accessing, such as the navigation pane. I'll show you how to disable the bypass key and how to convert your Access project to an ACCDE file for extra encryption and security. The next section of security covers how to hide linked tables and pass-through queries to help prevent users from importing them elsewhere. We'll tackle the read-only issue that sometimes comes up with linked tables and how to fix it by creating indexes for read-write access. You'll also see how to create and test a distribution copy on a second computer. In lesson eight, I will cover querying data directly in SSMS. I'll show you how to alter existing tables without re-uploading them or creating new ones. You will learn about making changes using both SQL and the SSMS editor. Finally, I will discuss backup strategies such as manual backups and the automated nightly backups offered by Winhost. Lesson nine brings everything together by demonstrating how to access your SQL Server data on the web with ASP. I'll introduce you to the EditPlus Web Editor, but you can use any web editor you prefer. You'll build your first ASP page, use ADO to connect to your data, display a customer list, and let users select individual customers to view further details. In the tenth and final lesson, I'll return to security concerns, focusing on how Access temporarily caches passwords for server connections so you don't need to store passwords in your queries once the connection is established. We'll also review the benefits of pass-through queries once more. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListConnecting Access to SQL Server databaseSetting up web and SQL Server hosting on Winhost Using the Winhost Site Control Panel Creating an online SQL Server database Obtaining the SQL Server connection string Installing SQL Server Management Studio (SSMS) Setting up an ODBC data source for SQL Server Exporting Access tables to SQL Server Linking Access tables to SQL Server Comparing Access SQL and Transact-SQL Creating and using pass-through queries in Access Creating queries on the fly with VBA and QueryDefs Storing connection strings in TempVars Relinking tables using VBA TableDefs Using ADO recordsets to connect to SQL Server tables Changing server-side database passwords Fixing connection issues by pinging pass-through queries on load Locking down the Access database for security Building an admin menu for database locking Disabling the Access bypass key Creating ACCDE files for encryption Hiding linked tables and pass-through queries in Access Handling read-only issues with linked tables Creating indexes in SQL Server tables for write access Testing distribution copies on a second PC Querying and altering tables directly in SSMS Making schema changes to SQL Server tables Manually and automatically backing up SQL Server databases at Winhost Connecting to SQL Server data using ASP on the web Creating and editing ASP pages with EditPlus or another editor Connecting to SQL Server with ADO in ASP Displaying customer lists and details using query strings in ASP Managing Access password caching for SQL Server connections |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| 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 |
