Access on Azure SQL - How to create an online Access application using Azure SQL and ODBC
HTML-код
- Опубликовано: 8 июн 2024
- In this episode, we're going to explore how to design your database in order to go online instead of with an Access backend for data storage. I'll show you how to create your Azure SQL database, create the tables you will use, and then link to those tables using Access linked tables. From there, I'll show you how to build a form for data entry, and then some VBA code with slight changes in recordset handling that you'll need when you're working with Azure as a backend. Many thanks for this excellent viewer requested topic!
Related Videos:
Access on Azure SQL - How to create an online Access application using Azure SQL and ODBC
You are watching this video now!
Online MS Access Databases on Azure - Enhanced security using Azure Active Directory accounts.
• Online MS Access Datab...
Microsoft Access on Azure SQL: ODBC Overview with DAO
• Microsoft Access on Az...
How to Migrate Slow Queries to Azure for Speed in Your Azure-Enabled Microsoft Access Application
• How to Migrate Slow Qu...
For developers looking for a new role, check out and sign up:
www.toptal.com/qKaO2b/worlds-...
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.com/posts/how-to-...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a RUclips Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
#access #azuresql #dataengineering
• Access on Azure SQL - ... 
Наука
UPDATE: YOU CAN ADD ADDITIONAL USERS VIA SSMS. Watch this update: ruclips.net/video/6xotpT7Vhso/видео.html You can add read only/read-write, and assign all kinds of other limitations or permissions. For example, create a user rsmith as a read-only user with no admin permissions.
Hi Sean, perfect video as always! Please stay more on this subject of Access - Azure connectivity, as sooner or later we will all gonna have to go this way.
Sounds good! I'll be adding more in this area.. stay tuned!
@@seanmackenziedataengineering pls always give us better approach to ms access to SQL azure online, is what I have been looking for. Pls support
It was very easy to understand from beginning to end, and I appreciate the wonderful material! thank you very much!
Glad it was helpful!
Thank u very much i have been trying to connect my database to azure for years and its the first time it works for me❤️❤️❤️
That's awesome! Good work, I'm glad you got it to connect.
Señor Sean, Excelente explicación, muchas gracias por su generosidad al compartir sus conocimientos.
¡Bienvenido y gracias por ver mi canal!
Hi Sean - once again revisiting this video. I do not know how long it would’ve taken me to achieve the connection if it were not for your channel. Thank you so much for all you do
Thanks for sharing!
Excellent video and instructions. Thanks so much for putting this together!
You're very welcome! Thanks for watching
did you know your 1 lesson solved my 10 problems. i always waiting for you.
Glad it could help!
Sir, I want to export only one table/query from my existing database online.
i am very excited to see your another valuable lesson addition.
More episodes coming! cheers
Great video, highly appreciated!
Glad you enjoyed it!
This was a big help - thanks! I was having a rough time trying to figure out how to connect to Azure SQL through MS Access on an Azure VM on the tenant. Things connected fine from Access on other PCs, even worked using SSMS on the Azure VM. I just could not get a connection through Access on the VM. After viewing this video, I found that it needed to have the server piece of the connection string, the "tcp:" prefix, included. Only for Access running on the Azure VM; no other methods of connection required that "tcp:" prefix.
Cool, glad that worked out! Also make sure to check my recent follow up on this one ruclips.net/video/6xotpT7Vhso/видео.html that shows how to add more users and some other updates.
Many thanks from Kenya. Wonna use this for a school that has over 3,500 kids in 5 campuses. 👍🏽
Cool project!
@@seanmackenziedataengineering Thanks again. Been using Access since 1995. Bro wanted me to ditch it for C# yet the client just wants his reports. He doesn't know and doesn't care about the code. Client now has many locations so we needed to go cloud.
Exelent work sir!
Thank you kindly!
Thanks dude
No problem!
excellent tutorial.
Glad you liked it!
Hi Sean, this specific video and your MS playlist in general has been incredibly helpful. My question is: Troubleshooting best practices for not being able to see your databse object in MSS? I have tried to create a new database and refreshed/logged out - I still am not seeing the equivalent of your “Project Timelines” database - although I do see it as live on the Azure portal.
Thank you,
Miles
In Access you'll just need to do a linked table to the new database/table in Azure. Once it is created in Azure, you can just "connect" to it using SSMS, where you can do your design work etc. Is that what you mean? If the database is live in Azure, there is no need to create another database. Just connect using Access and SSMS.
Thanks Sean, this response helped me and I am off to the races
Great video as always Sean. Could you pls elaborate on how Azure manages/resolves conflicts when entering data concurrently? Is it like the "red x" in OneDrive where the user picks one version or the other? What happens if I have a multi-user environment where two users are attempting to enter a new order at the same time (with OrderID being my primary key)?
Yves, that is a great question.. and an important one! The key thing to know here is that Azure SQL is essentially SQL Server in the cloud. SQL Server is a full relational database management system (RDBMS) that handles concurrency very well. ODBC provides the magic between your app and the Azure SQL database, and ODBC is very good at concurrency as well. So, if you have two (or 10) orders being entered at the same time, ODBC will manage the concurrency of data entry and assign (if you have autonumber/identity keys) the right keys in sequence as it needs to. If one user and another open the same record for editing, they will receive record lock/conflict messages that can be handled by the user. The link through ODBC manages all of that, so you don't have to worry about concurrency. You can literally scale your app up to many, many users and Azure/ODBC will handle the workload just fine, so long as you configure the instance with enough juice.
@@seanmackenziedataengineering thanks Sean. I need to learn more about ODBC!
Thank you for the video. I needed to use ODBC;DRIVER={ODBC Driver 17 for SQL Server} otherwise I was getting the "Could not find installable ISAM" error.
Thanks for sharing! For others getting this error, make sure to check your driver string!
Hello Sean! Amazing video! I have a question about the connection technique you used for link tables in access. How do you protect the password from not being seen by "advanced" users? I mean, in the Field "Connect" on system table "Msysobjects" Msaccess will save the connection string (including credentials) and I think that's not encrypted at all. Any solution?
This is true; If you have advanced users and an app that requires more advanced security, you should use the Active Directory approach which I show here: ruclips.net/video/ubGiyQAdGYQ/видео.html Active Directory will use real single sign-on capabilities for any of your users with a Microsoft login (Work or Personal).
Make sure to read the description and pinned comments for updates that make it run more smoothly. Thanks for the feedback!
Hi Sean
Thanks for the very informative video. Just wanted to know whether we can connect using Azure Key Vault Secret instead of giving the server and login details in Acccess?
I believe you can - this is a great topic for a video. I'm going to put that on my list! Thanks
@@seanmackenziedataengineering Thanks. Looking forward to it!
This is really helpful, I’m about to migrate our back end data from SharePoint to an azure back end, we have some tables that have attachments, do you know if these are able to be added as a field, they can be a pdf, doc or picture etc?
Hey Terri, yes there is a way to store files - you can use BLOB fields for this purpose.
@@seanmackenziedataengineering Thanks Sean, don’t suppose you have any instructional videos on this, I’m looking everywhere but can’t find anything on how to set this up. The migration is going really well just from this video alone I was able to learn enough to get it started so thank you :)
Great video...out of interest sake can the same access connection be achieved to a Azure server without SQL but with SQL Express instead?
Yes, you can use SQL Express instead of Azure SQL for the backend database. In fact, the process is very similar. Only your connection string is different for a connection to SQL Express because it is on your local computer instead of the cloud. You still use the same tools like SSMS to build the database etc. I'm sure you can also connect to Azure using SQL Express, if you wanted to, since SQL Express, Azure SQL, and SQL Server are essentially just SQL Servers.
Hi Sean. Thank you for video! Very clear! Question: Once you entered password 3 times for ODBC connection, when you give database file to the user and user will install it on another machine, will user have to put password 3 times again, or connection is already saved inside access file and it will work right away?
Make sure to watch this more current video where I talk about this: ruclips.net/video/6xotpT7Vhso/видео.html
You will create users as I show, link the tables during design, give them the database file and then when they open the database, they only need to log in *one time* and it will connect all of their tables. When they close it, connection is closed. When they open the database again the next day or whatever, they log in only one time and all tables are available again, etc.
Make sure to use the most current driver that you can. I would recommend at least "ODBC Driver 18 for SQL Server", because it will better connect to Azure. Free download from MS.
@@seanmackenziedataengineering Thank you!
thanks
You're welcome! Glad you enjoyed it!
Hello Sean, You're always helpful. Now, what's the work around with Public IP addresses? I keep switching ISPs and thus my IP address changes quite often. Since the app am building might be used by several people whose IP addresses i have not knowledge of, what's the work around so I don't have to add new IP address rules in Azure?
If you know that the users are in a particular area or an ISP uses a range, you could put the range into Azure and allow access to the range. It is not as secure but will block most of the internet while leaving some lanes open. Other than that, you can just use AD access which I describe in another video, where your active directory/windows account gives access to the database.
@@seanmackenziedataengineering Thanks Sean... Would you be kind and share the link to that other video where you describe use of AD access?
@@luyonjostephen8746 you bet, ruclips.net/video/ubGiyQAdGYQ/видео.html
@@seanmackenziedataengineering thanks Sean...
First of all .. Tanks For This Video !!
The Way you Did it just works with small and simple programs not for heavy or complicated databases with a lot of queries and functions and VBA's
Is There Any Solutions For This Types Of Databases Plz .... !?
That's a great question! Actually, you can use this method for a really complicated database with lots of queries and VBA. No problem. If you need a higher level of security by user etc., you can also use Azure security: ruclips.net/video/ubGiyQAdGYQ/видео.html
@@seanmackenziedataengineering Thanks Man for this speedy reply .. I Appreciate That ..
In the fact i tried to do it once then became the program slower than the turtle .. i don't know where is the problem
@@seanmackenziedataengineering Sur .. is there any way to contact with you to discuss some details Plz
@@brahimaoumeur4296 you can email me at info@mackenziemackenzie.com
Hi, thanks for your great video
You mean just back end convert it to SQL Azure and the interface is access file ?
my question is : I would like to share access database over internet like google and used by multi user over internet
what is the best solution to do that?
Regars
You're right, this is for converting the back-end to Azure SQL, then using your existing Access database as the front-end, after linking the tables. It works awesome - this will work for your situation.
@@seanmackenziedataengineering ok you are right, if the back end is sql azure so what about the interface, its just run on a single pc or my clients can used over internet ?
@@ballaomer6738 You can distribute your Access file to everyone who will use it. They will need MS Access on their computer (or free MS Access runtime edition, google it). If you have many people, the single user/password model may not work, so you can use Azure AD instead ruclips.net/video/ubGiyQAdGYQ/видео.html This allows them to log in using Microsoft single sign-on (live, hotmail, onmicrosoft, or corporate accounts etc).
Pls sir I have main form with subform, I want the value of (1200) on field(txtAmount) on the mainform to also appeared on the subform field (tztAmount). I have a button on main form , so which VBA code will do it.
Great question! You can see it here: ruclips.net/video/7x06xjEjQ78/видео.html
Hello Sean... Have you encountered an error trying to export an MS Access table that has a Date/time field to ODBC? What's the remedy?
Yes, this is pretty common when using databases like MySQL or Oracle with Access via ODBC. Once you get it worked out, it will be fine but it will give you pain until then. Try changing your date formats in Access. You might even try changing your Regional Settings so that the order of the m/d/y becomes y/m/d.
Hi Sean
Please can you access an already made database on azure or you need to create the server first before you can access it online?
Good question! You can access another database that was already created. You need to navigate to get the connection string from it as I mention in the video. Then use the string to connect your app.
@@seanmackenziedataengineering thank you.
Hi Sean... I've noticed that your linked tables load very fast and yet on my end it's very slow. Do you think that's because of internet speed or my subscription package with Azure? (am currently using the cheapest package)
I'm not sure.. it could be which region you put your project. I notice if I use East US and I'm on the west coast of North America it can slow down a little. Where are you?
@@seanmackenziedataengineering Am in Uganda, East Africa
Sean thanks for the video! When I run "LinkTables()" I get the error "Runtime Error '3170'. Could not find installable ISAM." There is a comment below regarding this error and your response was to change Dim db As Database to Dim db As DAO.Database. I am a little confused though because there is no Dim db As Database in the connection string to link the tables. Any help would be appreciated - thanks!
The Dim statement is in the VBA only. DAO is the part that handles the "linked" table, but not the connection itself. DAO allow you to open and use Access tables, queries, and other data objects. In this case, we open an ODBC linked table and use it as if we were just using an Access table, using DAO. Using DAO.Database is sometimes necessary, to specify exactly what kind of database. So, it is not related to the actual ODBC connection or its connection string.
Hello Sean, i'm getting the following error when trying to link my access tables to Azure. I get the following error " Run-time error '3146'' ODBC--call failed. [Microsoft][OBDC Driver Manager] Data sourcer name not found and no default driver specifiied (#0). I've checked my networking rules in azure and the ip is listed. Any idea as to what my issue could be?
Check that your connection string has the driver name listed EXACTLY as it is on your machine. It could be that you are running 64-bit Access and trying to use a 32-bit SQL Server driver or vice-versa. You can check which drivers are installed by typing ODBC into your start menu. There will be 2 apps, one for 64 bit and one for 32. Does it match what you're building? Check and see that the driver is there.
Hi Sean - it's really an achievement, many thanks. But still need your help figure out the reason on "why receiving Run-time error '3146': ODBC-call failed, because the Data source name not found and no default driver specified (#0)" - appreciate your response, I do need your help on getting this issue resolved - Thanks
It is possible that you need to install the ODBC driver. This message is always about the driver not being installed, the driver name with wrong spelling in the connection string, or the wrong bit version used. You can message me if you like. Click on my channel and then my homepage and you can see how to contact.
Have the same issue. Can you please let me know how to fix this error?
Please explain how to add ODBC link.
This video uses ODBC links! Try in your project! What database are you using?
Hello Sean... Thanks for this elaborate explanation. My problem is when I try linking Access to the online database using the vba code, it returns and error but 3151: ODBC connection failed and does not provide the option of putting in the password. How can I go about this?
Make sure you have the right driver. Your connection string should say something like ODBC Driver 17 for SQL Server. If it is too old, it will not support OAuth or other Azure features. Check it in ODBC admin, Start > Search ODBC > ODBC Admin 32 or 64 > Drivers tab. Check the version that matches your bit version of Office (32 or 64)
@@seanmackenziedataengineering Thanks for replying. Allow me to disturb you further. My connection string is "ODBC Driver 18 for SQL Server...". When I check the ODBC drivers, the SQL Server version is 10.00.19041.2486 whose date is Jan 23. Does this present incompatibility? Do I have to look for another version of MS Access or download another version of SQL? Please help am not a programming geek
@@seanmackenziedataengineering The ODBC driver I have is ODBC Driver 17 for SQL Server. I guess I need to look for driver 18. Right?
Hi! Works with other service of MySQL?
Yes, the idea is the same. Use MySQL driver for online instance.
Hi Sean, I am fairly new with MS Access and I am having an error 3170 Could not find installable ISAM. What could be the issue here? thank you!
This one can be several things. First, try changing the line:
Dim db As Database
to
Dim db As DAO.Database
Rarely, there can be a conflict. Let me know how it goes.
How can I use Entra instead of giving away my sql admin password or annoying users with that popup everytime they open my application?
You can actually add downgraded users using SSMS. You can assign permissions like read only etc when you create the users. If you want to use entra (AAD): ruclips.net/video/ubGiyQAdGYQ/видео.html
10/10
Thanks!
Do i need to install the ODBC driver? I get a message saying [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (#0)
Yes, it is a good idea to install the ODBC driver. I highly recommend getting at least Driver 17 or higher, since these drivers have great support for Azure. Free download, just google it.
@@seanmackenziedataengineering yeah I got the driver from Microsoft but you need to be admin to install it
@@seanmackenziedataengineering I forgot to add, despite my troubles, great video, nicely paced, very informative but unfortunately you don't work in my IT department!
@@RichardJones73 No worries! One thing you can do is to go Start > search ODBC > click on the ODBC Admin for your version of Office, either 32 or 64 > look on the Drivers tab > make a note of which SQL Drivers you have. Lots of companies have some stock ones installed in there. You just need to match exactly the driver name in your connection string. Maybe your company has a little bit older driver and that's ok, I would try it. It will be something like "ODBC Driver 17 for SQL Server".
@@seanmackenziedataengineering My IT are so behind the curve as it's version 10!
When I use Linked Table manager and paste in all the connection info, all I get are SYS or INFORMATION_SCHEMA tables - what do I have to do to get the AdventureWorksLT database tables (or any table I create)? I'm using the SA password to connect via ODBC but its not giving access to any of my DB tables.
The LT manager will show a bunch of system tables, but usually in addition to your db tables (hunt through and find them), unless you didn't specify the database and ended up lookin at the Master database. You need to specify your database in your connection string. Watch my more detailed recent update on connecting here: ruclips.net/video/6xotpT7Vhso/видео.html
@@seanmackenziedataengineering yes the actual DB is specifically stated in the connection string which is why I’m confused none of the other tables are showing up, esp the AdventureWorks db. I am using the connection string suggested in Azure.
@@BryanDoesCinema iirc AdventureWorks does create its own Schema so you may need to specify that as well. I think it was called SalesLT or something like that. So it will be SalesLT.Product, SalesLT.Customer etc
@@seanmackenziedataengineering OK so I basically make AdventureWorks the default schema for the user/login I'm using? I wonder if I should make a separate login/user for just Access users? I'm using the SA credentials I used when creating the account in the connection string currently.
@@seanmackenziedataengineering oh and yeah I don't see a SalesLT anywhere in the list, just the basic sys and information schemas. I do see 3 dbo tables, which is where I normally see tables. I created my own tbl called tblPerson just to see if that shows up and it does not show up either. I feel like I'm missing something very basic here.
Oh and I forgot to mention that when I do the DoCmd.TransferDatabase VBA they DO connect! They just aren't showing up in the Linked Table Manager window which is where I need them setup.
Hi Sean, sorry for asking but i am new on this, in which way another user will be able to have access in the database?
You can give them a copy of your application file. They open it on their computer and then they can use it at the same time as you. You may need to open their IP address as I describe in the video. Good luck!
@@seanmackenziedataengineering thank you!
No problem!
@@seanmackenziedataengineering Hello, thank you for your videos, and the music, ehehhe. So, in this case, the second user will be using the same login and password as the admin in the example, correct? I was trying to test the "northwind" database but I think it was too complex to migrate all the tables. Thank you!
@@Victor-de3rt Yes, this example is for a simple scenario with trusted users sharing one username and password. The second, third user will use the same username and password. If you want to do more complex authentication, you can use AD like I do here:
ruclips.net/video/ubGiyQAdGYQ/видео.html
How can i make the database connect automatically to azure without entering user and pass every time, i entered the password in modmain but it keeps asking everytime
If you delete/relink your table defs at startup, it may get around this. Another way is to use vbScript ruclips.net/video/kJIZ-b9idrg/видео.html
@@seanmackenziedataengineering thank u very much i will give it a try tomorrow
Regards 🌺
This is not an "Online Application". Only the data is on the Azure cloud, but the MS Access is local.
I was expecting to see a real "web/online" MS Access application. Disappointing.
Please give a title that is not misleading.
You don't think Azure is online?