Access on Azure SQL - How to create an online Access application using Azure SQL and ODBC

so how can i put my access database online or more specifically how can i design my access database so that it works well and goes online so that my users can access it from anywhere with an internet connection and microsoft access installed i'm your host sean mckenzie and today that's exactly what we're going to talk about we're going to talk about how to design our access databases so that they work well on azure we're going to use an azure database today and azure sql database i should say and we're going to talk about design considerations and i'm going to show you one or two gotchas that you have to remember when you're using your code on your you know your dao objects and stuff like that and we're gonna go through the entire process of creating a database uh creating some tables on the database using sql server management studio and then we're going to take our access front end and we're going to code a simple form with a couple of functions to sort of demonstrate how to get past the gotchas and get your access database online so without further ado let's get to our access on azure sql looking to hire resources for your project make sure to check out the links in the description okay so in order to uh complete this project you're you are going to need an azure an account and uh once you get yourself an azure account uh you'll see some options here and uh the one that we're going to look for is sql databases and we're going to hit that create button there and that's going to start us on our process of creating a database on azure that we can use for our project now what i'm going to do is i want to sort of do everything here because you'll probably go through each of these you're going to create a new resource group i created a test one there but i'm going to create another resource group and the resource group is kind of like where you put everything that your project is going to use together in one spot that's your resource group and then after we've created our new resource group there as part of this we're going to specify a database name and i'm just going to put our name in as project timesheet here and now you'll note i did select new server there because we do need to create a server for our database because the server is going to hold your database plus any other databases that you might want to add to that server so you can have many many databases on your server and so we're going to create a database server at the same time as part of this sort of creation process and it's going to create this entire it'll create the server and then the database on the server and then you can add more databases later without creating a new server each time now you'll be asked to select a location for for your server and in my case i can choose us west 2 and now there's many different ways here i might go in further detail in in you know upcoming videos but today we're going to use just the sql authentication which is the simplest of them it's just a basically a username and password and we'll take a look at the other ones and in other videos where you have more advanced security that you might want to have you could use as your rescue or pardon me azure active directory and stuff like that so now you can see we're creating our database server we created a login for the server our our server admin login and as part of that the database is going to get created as well and our pardon me the server got created and now we can finish up the sql database part of it so now we've got a server and and now we're saying create our project timesheets database on that server and we can configure that now you should take a look at the compute and storage you don't really need a last pool to start i think and probably if you're looking for the cheapest possible option to get started it's going to be serverless which will be you know a certain amount per month but it won't it won't be massive unless you've got huge throughput on your database um so this is a pretty small one and uh see some more detail about it here if you go into the uh configuration part of it and but basically if you choose the serverless it'll it will uh scale automatically you can see this is uh you know six bucks a month canadian and uh you know it's got a pretty low um you know amount of power on it but it's probably more than enough for for hours you can see the uh auto pause there if you want it to pause um processing when nobody access accesses it for a long time you can set that and and some other settings and now for network connectivity in this case you can say no access and connection policy just use the default um and then uh leave the defaults for for that stuff if you want defender you can get it but basically this is all pretty much defaults now and you can add some tags and things if you want to on the next screen there generally speaking the you know the collation will be the same and so you get to the end of it here and it's sort of like review you know all of your options and uh and then you can go ahead and create that database and that's going to get us started um so this is great so everything looks good we'll hit create and uh that's gonna give us a deployment in progress message which will go on for a while um i've called this one like i said before it's access demo server and it's creating that it's deploying that server you know as we speak and it sometimes it might take a while for it to get deployed but generally speaking you uh you'll get it pretty quick and so you'll see some messages in the top right usually popping up oh there we go it's uh deployment is complete and so once you're done there and your database has been deployed you've got a server you've got a database you'll see it in your messages if you click on the bell that it was deployed and you can click on go to resource and that's going to take you to your database because there's a few things that we need to do before we we start creating tables and stuff like that because we want to access this using our sql server management studio so we're going to go to the server name and click on it in the when we're in our database there and what we want to do we can see our database there the project timesheets is the database and we've sort of clicked on the server so now we're looking at the server over all of you and we're going to click on show firewall settings and what we want to do here now if you had some other stuff like uh you know an an azure app service you know an asp thing you could you would click on on the allow azure services and stuff like that but we don't need to do that the only thing we're going to do here is we're going to we're going to create a rule in our firewall and you need to find your your ip address because this is where you can allow your ip through and anybody else is going to use your database so you can open it up pretty wide as as wide as you want but generally um you know in case when you're first getting started just open up your own ip address and say save and then once you save it you'll get a message back like you just saw there and now your ip address has access to the azure sql database and once you've got that one the next thing we're going to do is we're going to go to our database again now if you ever get lost you can check that that tree at the top and you can go up and down the tree it's like a map to where you are so in this case now we're going to look at our database and now we're going to go show database connection string and what that's going to do is it's going to give us the methods of connecting that one's for ado.net jdbc odbc php and go and we're going to use odbc because microsoft access works really awesome with odbc so you can use the little copy icon there to get your connection string into the clipboard and then you can paste it into notepad or whatever because we're gonna we're gonna use that here very soon and i've got sql server management studio going here but just before i get started i'll grab notepad and i'll just paste that in so i don't forget our connection string so there you go that's our connection string this one specifying odbc driver 13 and you can see it's got all different kinds of information in this case we can get our our server name out of there which is the access demos dataserver.database.windows.net and so we're going to copy that because we're going to use that in sql server management studio which you can download for free that's what you see right now and when you open that server you're going to see this connect server pop-up box and you can put in your server name and your demo admin because we created that user when we created the database the database and your password and then what you're going to see is you'll see something like this and this is a tree in the top left here and this is what sql server management studio looks like it's very very handy now you can see if i open up my database you'll see there's there's no tables in there yet and but we're going to remedy we're going to remedy that by adding some very simple tables now you'll click on new query on the on the toolbar but make sure that before you do that first you click on project timesheets in the tree and then click on new query so that it puts you in the right context and now sql server management studio has sort of a context thing there where um you need to make sure that you're running your sql on the right database and stuff so always uh make sure you click on your database first and then open your query you can also change it in the top left you see where it says project timesheets you can change which database that you're looking at but make sure that you know where you're you're doing it so what we're going to do here is we're going to create some tables and this is part of the process and what we want to do is we want to create tables that are really good at working with access now now i have a certain way that i i can see how it works well with access and particularly with dao so we're going to create some sql server tables and we'll use the create table statement like you can see here create table person and then inside the brackets we put all of our column names and we're going to use that person id integer not null that you see on the top and it's and then it specifies identity one one which means that it's an auto number that's the it's like an access auto number um and then the primary key clustered means that all the the key is going to stay in the same place and it's going to be ordered right where it you know sort of like where it's stored and so that is exactly how access auto number works and so that's why this is why i use this one because when we do our linked tables as you'll see later in the project that key is going to be very important because that's very very similar to using auto numbers as as our synthetic key in microsoft access dao like our normal projects and so we can use our our envar car data type that's your short text if you do an envar car max that's your your memo because when when we link our tables the data types that you see here are going to be translated to access data types and it works see almost seamlessly it's really awesome so we'll create another table for project we'll create the the auto number key just the same way as we did in the person example and then we'll create a table for our project sort of time where we're going to record people's time on on projects this is a very simple example so this is kind of like a junction table where we're going to uh you know record all of the different times that people worked on different projects and this is we're doing it on azure sql so that it works very well with our access database front end so you can also create you know if you want to you can create foreign keys and and cascading updates and deletes and all that other stuff too that's a little advanced for today if we're just trying to create something from scratch um so what we'll do is we're just gonna make a very simple set of tables um as you can see in project time i've got a time id again it's our auto number i'll grab our person id which is the integer of the person id of the person in the first table i'll put the project id as the second foreign key there and then i will do like a date worked you know when did they work and uh you know the hours worked we'll just put that in as a decimal and i'll just put say i don't know 17 2 that's probably way more than they could work in a day but anyway that'll work for now and you'll see how that gets translated when we link the tables later so that decimal will become an access decimal and then our work comment again if you want to have a memo field you use that invarcar max and if you like you can put a go at the end of your at the end of your script and you hit f5 and that's going to create all those tables on your server now you'll see they don't show up here so you're wondering what's going on you have to right click and refresh in sql server management studio every time you make changes to your tables and all that stuff so make sure you right click and refresh so that you get the latest list and you can see those three tables were created just fine and then once our tables are created we can jump over to microsoft access and we'll create a blank database here and i'll just call it timesheets or whatever um and then you hit your create button and just like whenever you start access you've got a new table there and you can get rid of that and what we're going to do is we're going to we're going to go create ribbon and then module and then that'll give us you'll see this little uh ide here this development environment and i'll just call i'll hit save and we'll call this mod main now all i'm going to put in here is i'm just going to put a little procedure that's going to link our our database tables directly to the azure sql backend without using a dsn needing or needing a dsn file because i prefer not to do do it that way so i'm going to grab that whole string that we saved in our notepad file there i'm just going to paste that as a new variable we we created a string for connection and as you can see there's our connection you know it's got a bunch of stuff in there you can see this here now it's not recommended to put your password in to the string you can which will make it seamless and it won't prompt users when you first start up the application but it can be fished out the password can be fished out so you might want to leave that blank there but basically what what we'll do today is we'll show how to create the linked tables automatically here and if you make any changes to your you know sql server tables you can delete them out of access here because those are just links they're not actual tables when you see them in access and then you can just run this little procedure again and it'll just re-link them with all your changes so here's your we're going to use transfer database so we'll go do command transfer database ac link odbc is the type we'll put in the connection the strcnn for the connection that's that big one up above and then we got our our table names it's the source and the destination in this case we want them to be the same in you know the same name as they are on the server when we look at them in access although you can change it you could give it an alias if you want by changing that second project or person there as you can see and what we're going to do on our connection string is we're going to add odbc and to the beginning of that that's actually a translation for microsoft access it likes to see that odbc as part of the arguments and you'll see when i hit run on that you'll see it gives me a login failed because i didn't specify the password so it's going to ask me three times once for each of the tables and only does this when at the time that you link it so yeah i'll have to put in my password because i said hey let me into this server using demo admin but without a password so it says what is the password and so so now we've got three tables linked in our our project and we don't have any dsn so we could just give this we could give this project or this access database to somebody else they don't have to have the dsn files on the computer and all that stuff and as you can see there's three empty tables and the beautiful thing is those tables act like uh native dao tables so you know when you open them and you you know you want to add some information into them it's just like you're looking at access tables they work almost identically in the access interface to your access tables which means that you can create your project once you've created all your tables and stuff you can create your project just as you would as if you're programming it on native access now database tables except you're programming it on azure tables and you know and they're designed in a way that works really well with access you'll see i can just type start typing into that row there and i can you know it recognizes that it's a date field just like you know in access and i can type some stuff in there and uh and and that's very very handy and it works just the way that you would expect it to and there is a gotcha which is coming up that uh if you're if you have any code in your in your solution um then i'm gonna give you uh a little heads up on um on what you need to do in order to make your code it's very very simple there's one thing that you need to do uh when you open and modify data uh using your you know your record sets and and stuff like that so so what i'll do is i'll i'll just use a wizard here and on our project timetable that's our our our junction table you know where we record people against time and it's many to many so i'll just create a tabular form and and i'll go ahead and i'll just keep the default options and i'll just call it project time form and uh and then i'll say finish and now you can see wow okay now i've got now i've got a form that's working on an azure database and um and i'm going to be able to go in and make some design changes now these are ids if you remember correctly because of you know we were using that synthetic id for person id so i'll just go ahead and i'll change that to a combo box so that it gets the id number but it looks you know when you look at it it looks at the person's name and so we'll click the ellipsis beside beside the record source there or the row source and then i'll just grab last name i guess i'll make it a concatenation and i'll say you know uh person and and then it'll be last name ampersand comma first name something like that and then we'll also uh we'll grab that person id and we'll drag it down to be the first column so it'll it'll give us the id and then our our name so that we can use that in our in our drop down box so i'll do that i'll close that you say yes to save it now we've got an id and a and a name and what we'll do is we'll check that uh bound column is is one that's the id so that's where what the data is going to be that gets out of this combo box and then we'll we'll say column count two and we'll put you know column widths zero and and two and then uh you know um and i guess i'll say three might be a little bit wider than that so i'll just say three centimeters and and then i'll make the list with three centimeters just for demonstration here and um and then i'll do the same thing for a project id i'll go in uh we'll we'll grab that um we'll grab that first we'll change it to a combo box and then we'll we'll get that row source and we'll go into the ellipsis and grab the project just like we did on our previous one and uh well just this one's easy just grab the project id and the project name and we'll sort it ascending by project and we'll save that and now we've got two combo boxes that have you know the data that we want in them uh we'll sort the last name ascending last name and first name and uh and then now if we if we go ahead and check that out on our form view we can we should see some values in there so there's our names and then oh oh that looks like our project as our project id so that's our bound column so it's looking at the ids and not the and not the name so bound column is one and we want to set that to two columns and then we need to set some widths for the columns so we'll say zero for the first one because that's you know it's just a number that doesn't mean anything to the user and then we'll say 3 centimeters and make the list with 3 centimeters and and and then we can go back to our view here so now you can see there we go we can choose the bridge project and it's going to put the id into the table instead of the name which is really good because if the bridge project ever gets changed to the crossing river project name you don't have to change the id or go back and change all your records you can just change it in one table and that's one of the beautiful things of normalizing your data you can just change it in one place now as you can see this is really great because now we've got an online database i could give this data file to anybody and they could be working on it concurrently you know throwing in records and things like that and making changes and the changes if there are conflicts those will get you know those will get um no they'll be propagated out to the client and so you'll have to resolve conflicts if two people change the record at the same time that's all handled via odbc and so i'll change this uh you know make this look a little bit better i'll move my my uh title up and then say i wanted to get some more space well i could grab you know grab those move those over and i could you know expand the uh you know the the fields that have some select data in them and i can move those over and you know they might look a little bit you could show a bit more or you could you could stack them put one on top of the other in that empty space that you see in the bottom left there but but you could spend hours doing all that kind of stuff and but the main thing is is that this form is now working on azure and which is really great and it's going to allow us to deploy the solution you know out to as many users as we want we can you can create all kinds of stuff you can lock it down according to however you lock your access databases down and you can use azure active directory so that you know those kinds of things so you can have more advanced security if you want [Music] but this works just like dao which is awesome dao being you know most of the stuff in access you know all the forms and everything and your record sets generally are using dao and so this works pretty much natively on these three tables that you see here now if you are using ado you probably already know how to use connection strings and things like that but your azure database will also work with ado record sets just you just use it as if you were connecting to sql server and that'll give you your record sets and so now what we can do is if we wanted to show some some coding um what we could do is we'll go to the design of our of our form here and i'm just going to throw on a button here because we're going to do a little bit of coding i guess i'll put it over on the right here i'll just cancel that and then we'll give it a name i'll put new project on there and then i'll i'll give it a name on in the other tab on the properties i'll call it tx or pardon me cmd new project so if i click on that instead of command 11 i'll say cmd new project and and then we'll also add i'll go ahead and add a i'm going to add a text box that we can use just for just for a demonstration here for a new say a new project name so this is because what we want to do is we want to show how your coding is going to work because when you open your dao record sets in vba um when you're using azure sql on the back end there's a couple little changes you need to make compared to how you you usually would open those record sets and you know do things like add and update and so what we'll do here is uh we'll click on our new project button and we'll go to the event tab and we're going to click on on click and then we're going to select the code builder there and then go ok and that's going to give us uh a a an event that we can code for the click and so what we'll do is we'll go we'll add a comment at the top just saying checks for and then adds a new project and then we can just say dim db as database we'll get a dao record set as rst and and then we'll get our project which we're going to grab from that text box i suppose and that's on the form and then we'll just say our project is equal to uh you know an empty string concatenated with what's in there so if there's a null in there it'll it'll make it into an empty string for you and then we'll say if the length of the project is is equal to 0 then well that's not equal let's go back and is equal to 0 then just get out of this subroutine so we'll just say exit sub so what that'll do is if if it's empty then it won't do anything but if we put an entry in what we're going to do here is we're going to open a record set and we're going to we're going to do a find first on the record set to say hey uh is this guy in here already or this project in here already and then if we don't find a match then we're going to insert that or we're going to use our record set and we're going to do the use the add method and it's going to add it to the record set and so what we're going to do is we're going to open this record set we're just going to open it on the table and as you can see i've added this argument on the end called dbc changes which is very very important for when you're coding for your for your new your new access database or if you're converting your your existing access database over to use as your sql tables because uh the dbc changes will allow your record sets to see the changes as they happen you know that has to do with all with the you know the uh the id columns that are auto generated and all those kind of things so the dyna set needs it you can get away without using dbc changes on your snapshots now the snapshot type is db open snapshot and that is a non-updatable record set which might actually be a little bit faster if you're looking things up in the database but just be aware of that dbc changes so what we'll do is we'll say rst find first and then we're going to oh yeah we don't need brackets in i'm used to my net coding so we're going to say rst.find first project name equals and then our project and then we'll say if rst.no match then we'll we'll add we'll go ahead and add new so we'll we'll do an rst.add new and then we'll say rst exclamation for referencing the field uh we'll say project name is equal to the project on our new record set and then we'll say rst.update and and as you know that auto number in the background we don't have to insert the id key because it's automatically generated and we used a data type that's like the type that we use in our auto numbers and access works really well with that now i'm gonna this is just me i'll put a a with an end with around this to make it a little bit neater so you can use with so you can say with rst and then if you if you have code that has you know a million you use the same variable name a million times you can reduce the amount by using the with statement and so there we go with rst fine first if you don't find it you know put it into the table we're using our dbc changes so that's going to work for us [Music] and then we're at using an add new and update and what we could do is we'll we could add a message box saying uh you know added so there we go our our we have some feedback and then we could do else so if it's not dot no match which means that it did find a match then we could say you know message box this project already exists or something like that and then that's going to uh to give some feedback to the user to say hey you need to go and find that you know that project already exists so don't try to add that again and uh that will be very handy so now if i open our form here and i type in you know if i type in a project and i go you know i type in baseball field project and then you know if i go new project and know that i broke it already so let's go see so you hit debug and then i can see it looks like the i'll stop it and it looks like i used the wrong table name so we're actually adding to the project table and not the project timetable so i'll i'll minimize that and now if i hit new new project there you go it says added and there we go so now if i try to hit it again for that it says well that already exists because i just put that in and there you go that project already exists and so so what that demonstrates is that change with using dbc changes you do want to start to use your or specifying db open snapshot and db open dynaset for dyna set is for updatable record sets snapshots is for things that you don't need to update you should if you're not using those now you should start to use those and those will definitely make your your database more efficient and dbc changes works works very well with opendynaset and that's one of the things you need to know for getting your database online with azure and so now what you can see is you know i've got this uh great little form here that you know i could spend a whole bunch of time to make it look pretty and maybe a little bit nicer for the user but the idea is is that now there's this form it's working as an access database it's online it can be accessed from just about anywhere and and uh you know it's pretty much exactly the same as my other you know access projects it works in the same way and so it's very comfortable for coding you know using dao and all that stuff but what if i reopen the database you know what does it look like and so you can see i reopened it there and it gave me the warning and if i double click this time form you can see oh it's it's kind of remembered part of it and so we we do need to type in our database username and password but we only have to do it once and then for the entire time we're using the database with however many tables you have it's going to work just fine and and that is sort of how that light security works now there is a way that you can put the password into the you know if i hover over this you can see it's got a the connection string in there and there is a way to do the dsn less connection and you can actually save the password into the string so that it's seamless and it just opens for the user with no problem but be aware that that can be hacked you can fish out that that password and so it might not be as secure as if you you know you as if you you know just let them login or if you you know use azure sql or something like that or you might have some other ways that you secure your databases because you can look at the connection string here we don't have a we don't have the username and password in here but if it was stored in here then i could actually retrieve it from this string here in this case it's not stored in there and so that's fine and that is how you can design your database to work well with microsoft azure sql so that your database can be online and uh available for all of your users from anywhere that they have an internet internet connection and microsoft access hope you enjoyed today's discussion on how to get your access database onto azure sql so that your users can access it from anywhere if you like what you saw today please make sure to like the video and also subscribe to the channel if you haven't subscribed yet click the bell when you see the bell and if you have any questions or comments put those in the comment section below have a great day have a safe day and i'll catch you next time need help or coaching on your project make sure to check out my patreon the link is in the description

Channel: Sean MacKenzie Data Engineering

Views: 277

Keywords: access on azure sql, how to create an online access database, access database online, online access database, ms access on azure, azure db access, access dao azure, vba recordset azure, vba azure database, dbseechanges, dbopendynaset, dbopensnapshot, azure sql database, sean mackenzie data engineering

Id: vnlqZcgRLm8

Length: 38min 30sec (2310 seconds)

Published: Tue Dec 07 2021