How to Open Another MS Access Database from Your Database

i'm your host sean mckenzie thanks for joining me once again on my channel on data engineering in this episode we go back to our microsoft access playlist and we're going to look at how to open one database file from another database file that you have open already and this is very very handy when you have your application open and you want to actually open a different access database uh you don't want to link to it you know you don't want to use other methods you actually want to just open that in your file and make modifications today to the database or whatever so without further ado let's get to our open one access database from another need helper coaching on your project make sure to check out my patreon the link is in the description okay so i'm opening the same file we've used in the last few episodes it's this access data dot accdb and i've filtered the list on the left for the word database just because there's a lot of stuff in there so we won't see anything now and i've got this other database in the same folder that has this little table in it called more candy and and the database or the file is also called more candy more underscore candy and so i'm going to close that so that i can open it another way and we're going to sort of pretend that our app is open here that we're you know that's open for you know with our users or whatever and in this case we're going to have a function that's going to go open that other database and cycle through some records so that we can see what's in there so i'll call this this module open another database and and then we can start going so we're going to create a new uh subroutine here and uh you could also put this in a function if you wanted to but for now we'll just use a subroutine i'll call it open another database and and uh we'll we'll uh say that it opens another database put a comment in there and then we're going to create some variables um the first will be an application variable now this is going to be an early binding example um i can explore early bonding and late binding in another episode but for this example this is kind of the one of the most simple ways that you can do this so we'll we're going to dim a workspace object and we'll dim a database and a record set that'll be kind of like the you know the most basic example that we can do here and and so uh the record set will just be our usual i'll specify a dao record set and uh and that makes it so we're ready to go so we're gonna set the app uh variable equal to a new access dot application and that's going to sort of start a new access application in the background this is not the only way that you can do this you can also open up additional workspaces in the same in the same session and things like that but uh for this example i'm just going to open a new access application and we're going to set the workspace equal to the app to the database engine uh workspace zero which is sort of like the the default workspace for a an access application and then i'll set the database equal to we're going to use that workspace we're going to open a database and we'll put in the file path for it and you there's also some other options in there that you can look at if you wanted to um you know you can open it read only or you know and and that type of stuff so uh you just be aware there are some more options for you there and then now we can do kind of like we normally do when we're working with the with the data in in the current database we just just say you know rst equals db.open record set uh and then whatever it is i'll put a open snapshot on the end of it there to make it a read-only uh you know type of of record set and um and then so we're going to open it and then at the end of our procedure we're going to put you know we're going to close our record set we're going to set some variables equal to nothing to to release some resources and and at the very end of it we'll just put app.quit and that's actually going to close that access application that's running uh in our code there that's sort of hidden behind the scenes that open the file and so uh so then what we'll do is we'll use our record set we're gonna loop through it so we'll use a do until which i did in another video if you want to see how to use this do until or do while loops so we're going to loop through and make sure you always put a rst.move next so that your loop doesn't go forever or it'll sit forever on the first row um and then we'll just debug dot print our candy name from that table that's in more candy and we'll cycle through the records and we'll just output the candy names into our immediate window there so we've got our rst record set we have our our database variable which is the act db there our workspace our workspace object and our app odd application object and and so that's really the gist of it you don't really need to know more than that there are other ways of doing it make sure you close everything at the end uh so you've got you know your rst.close and then and then quit your application uh and uh and then we'll just hit go and uh and as you can see it did that it opened the access database in the background in another workspace and that's how it's done hope you enjoyed today's discussion on how to open one database file from another if you like what you saw today please make sure to give the video a thumbs up make sure to subscribe to the channel click the bell when you see the bell and if you have any questions or comments make sure to put those in the comment section below have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 215

Keywords: How to Open Another MS Access Database from Your Database, vba open accdb, vba open mdb, open one access database from another, vba application object, vba early binding example, open access using vba, sean mackenzie data analytics and data engineering

Id: hhPrBMLNiDs

Length: 6min 41sec (401 seconds)

Published: Tue Oct 26 2021