Connect to a database - AutoCAD Tutorial

- [Instructor] Connecting to databases is a extremely powerful way to get information into your drawing that you or your department does not even maintain. For example, you may want to attach billing information to your drawing directly from accounts receivables to each customer point in the map. Well let's look at attaching a database now. First, we're going to switch our map to map explore in the task pane. The reason we do that is because we're going to be attaching our database to data sources. That's a folder inside of map explorer. Now, if you don't see the task pane at all, just go to view and you can toggle the task pane using the map task pane off and on like that. It'll turn it off and turn it on again. If you don't see view task pane you can also use the command line. It's map W space and you just type that and that'll allow you to turn it on or off. You can say on or off like that. And I'll just repeat that command and get it back again. So right click. Repeat, map W space and I'll say on and it's back. And what I really want is the map explorer because again, I've got this data sources folder that I'll be working with and we're not going to be using the drawing or the query library or anything like that. We're just going to be using data sources and we're going to drag and drop in an access file. But before we do that, we have to make sure we either have access already installed or we install the access runtime. Now the access runtime is actually free for Microsoft. So if you Google Microsoft 365 access runtime, you'll find this at the Microsoft site where you can download and install the Microsoft 365 access runtime. This is free and you can download the 64 bit download in this case. And I downloaded it. And if I hadn't done this on this computer that I'm using today, I would not be able to drag and drop an access database in. So I've already done that. So now what I can do is I can go back to AutoCAD map, make sure I'm in the task pane and map explorer. And I'll just open up the folder where the access file is. This is an older access file. You can tell because it's got the extension MDB, the newer ones have the AC extension. So let's just drag and drop a zoning MDB into the gray area of my map explorer. It doesn't have to be right on the data sources folder. It could be anywhere in the gray area. I'm going to let go. And what will happen is that access file will be now connected to this particular DWG. This is a blank DWG, but now I've got an access file connected. This access file's very simple. There's no queries in it. There's only one single table, that's zoning with a big zed or big Z right here. And if I double click on zoning, you'll see it'll open up the table inside of AutoCAD map. I can now see all the data in Microsoft access, even though I really don't have a full version access running, I can actually use AutoCAD map to look at datasets. This will react the same, whether I'm connecting to Oracle or SQL server and so on. You don't drag and drop SQL server or Oracle because they're enterprise databases, but access files you can. So you'll have to manually create the current connections for those. And you just have to do that by right clicking on data sources and configuring a data source. But in this case, we're just dragging and dropping the access file directly in. Let's go back into the data view zoning table. And of course I can double click on any of these columns and reorganize. For example, I just sorted parcel ID up and down or I can double click on zoning and start from the highest to lowest. One zoning all the way to AB all the way up. I can filter this data too. Now there's a button here on the end called SQL filter. So say I only want the residential zoning so I can click the SQL filter. It'll give me a dialog box that allows me to filter just residential. So let's try that now. I'm in the table filter. It's already going to build SQL language for me. Select star from zoning, that's what's happening. And the column I'm filtering is, there's only three columns ID, parcel, and ID. So the one that I want is zoning. And I'll say the operator is like. I have all these ones equal, greater than, less than, and so on. But what I want is one that's like a particular value. In this case, I want it to start with a capital R and to say a wildcard or anything else that I want, I just have to put a percent sign. So now I have zoning like R percent sign. That means anything that begins with a capital R is fair game. So I'll click add. So now it'll say any zoning that has an R at the beginning. Let's look at it. So if I click okay right now, my data view zoning only shows those zoning values with a capital R at the beginning. Now if I scroll all the way to the bottom, you'll see I've got a few false positives. I want residential, but I have road and railroad. So let's filter out those two. Let's go back into the SQL filter. Oh, there's another filter right beside it called spatial filter. But we can't use this yet because this data is not connected to any graphics on the screen. In fact, this is a blank map, so there's no connectivity. We'll learn that in another lesson, but we're going to stick with the SQL filter right now. So let's filter out just the roads. Here we are. So to filter out the roads, meaning I don't want to see any of the roads, I'm going to say and the zoning equals... Now I can hit this dot dot dot. What this will do is it'll get every last zoning value, unique value from the database, and this may take a while. There's about 35,000 records in my database. It's going to take a while. Now if I had a million records, this would take a very long time. So be careful with the dot dot dot. But in this case, I'm going to scroll down to the word road. I could have typed it in, but I just wanted to pick it. There we are. So I'll say zoning equals road. Oh, I don't want that. I actually say where it's not equal to road and I'll click add. So now it's to get everything that begins with R and where zoning does not equal road. Let's hit okay. And it should filter out all the roads. It does. So let's scroll to the bottom see if we missed anything else. Aha, there's a couple railroads. There's R R space 1 and R R dash 1. Well, I just want to get anything that begins with RR and get that out of it. Let's go back into the SQL filter, and do one last filter on this particular dataset. So let's say and zoning is like railroads so RR and I'll put a percent sign so we catch all the different things with RR at the beginning. Oh, and I want it not equal to that. So I'll hit add. So now we'll get everything that begins with R but not equal the road and not like RR railroad. So this should get every type of zoning as long as it's not road or RR. Let's hit okay. And now it's even filtered further down. So I scroll to the end, you'll see the roads and RRs are gone. I've got just a pure zoning residential in my filter. Now I can just reset this. I can go back into my filter and clear the whole thing. So we'll do that. Or I can delete an individual value like this or I can clear all, and just wipe the entire filter altogether. I'll hit okay. And now I'm back to all 35,000 records. And of course I can always edit this data. So for example, at the very end, there's a WL. I could say WL L and you see the pencil beside that particular record? It means I'm editing it. Put my pick somewhere else, you can see the pencil disappears and that means it's been written back to the database and I'll go back in and I'll fix that. Put a WL and the pencil's there again and I pick somewhere else, the pencil goes away. It means it's now committed back to the database. Now this only works if you've got rights to edit that data. So for example, if I connect it to some big SQL server database and my username only had read only, I'd get a little error here saying I can't edit that data. I can only look at it and that's fine too. So depending on your user rights, you can connect to any database you want and be able to filter and look at it. You could even export it if you wish. But the bottom line is we have the ability to view and sort and filter data from any database and in this particular case, access. So now that you've seen how to attach the database, you might realize that you have other databases you wish to connect to at your organization. And now you can. So depending on your username and password, you may also be able to edit that data too.