Beyond the Basics: Database Connectivity in AutoCAD 2017 WEBINAR | AutoCAD

hello everybody welcome to build your AutoCAD IQ webinar series my name is Volker and happy to see all of you here today and hopefully we were able to show you some good stuff today I'm hoping I like this stuff we're doing here and I hope you do as well so for those of you returning welcome back for those of you who are new welcome today's webinar is going to be beyond the basics DB connect for AutoCAD basically database connectivity with AutoCAD and before I go any further I'm actually going to go ahead and throw out those polls right now because I am curious about our third poll but we're gonna start with the first one obviously and that first one is is this your first AutoCAD IQ webinar we always like to know how many of you are new to the show how many are returning again for those who are returning always good to see you back it's always great seeing those smiling emoticons and emojis so yeah welcome back for those who are new hopefully this will be a great experience for you alright looks like about wow 97% of you have been here before so that's uh welcome back and again welcome to all of you who knew all right so let's go ahead and got two more here so the second one which AutoCAD based application do you use so we have AutoCAD AutoCAD LT and a lot of verticals as well as third-party of course are out there they're available also applications in the Revit or BIM world so you might be one of those one percenters so that I've selected that so far looks like 48% of you are AutoCAD users slipped me go ahead and close that poll and share it for you while I talk about one thing typically we tend to do these build your AutoCAD IQ webinars and tailor them to both the AutoCAD and AutoCAD LT users and of course then it's applicable to all of the vertical applications such as AutoCAD architecture civil 3d and so forth I will say this right now so don't waste anybody's time DB connect for AutoCAD is only available in AutoCAD and the verticals it is not available for AutoCAD LT so I mean I encourage you to watch the show learn about the new features in AutoCAD but you probably wouldn't be able to apply this not a get out T actually you wouldn't be able to what am I saying all right so one more quick poll and we'll get moving and this is the one I'm curious about have you worked with AutoCAD tables and linking those with say Microsoft Excel and right now it's pretty much a split well ok going in another direction right now the reason I'm asking is because this is another way of working with external data using DB Connect so it looks like about 55% have not used tables or out linking with tables and that numbers changed a little bit but here you go just so you can check it out so yeah it's a pretty good split here so hopefully we'll be showing you some another way of doing things or maybe known about the DB connect tool in AutoCAD but of never known how it or what to do with it for that matter maybe we'll try and clear some of that up for you here alright so let's take a look at who we are again I'm Volker bokor Coco and with me is Ryan bales who will be attending via our chat window answering questions and this is the only known public picture we have of him right now and of course the ever most intelligent naman Maiasaura wallah who is joining us from the Autodesk expert elite program it's good to be back with you naman oh it's good to have you here as well so let us move on to our next slide and in this slide here we'll talk a little bit about what's coming up first thing we are not going to have a webinar next week due to the holiday break okay so gives us all a chance to rest up I know I need it and then followed by a third dimension webinar solid modeling followed by a tips and trick webinar and a back to basics per we have an introduction to imitative objects so be sure to tune in on those as always you can download the data set from our web box account and you should have that in the email reminder which you received if you do need to Lanka and naman or Brian can plop those into the chat window if necessary and always remember we have these webinars recorded on our YouTube playlist feel free to send us feedback at any time and also visit our forums and if you really want to get involved with the had our development of AutoCAD give your input test new betas as they come out new features or just want to interact with the product group during the AutoCAD customer cancel there's a couple lengths there for you to do so and before we get started feel free to leave questions in the chat window we'll answer as many of those as we can will if time allows we will answer more questions that after the presentation is over and that of course session is going to be recorded and as I explained the links are in the email reminder and Victoria will also be sending out an email after the webinar with a reminder as to where you can find all this information here in product support we do like to point out the Autodesk knowledge network there are a lot of featured articles there as well as downloads which are available to use for updates hot fixes offline help file and also some of the top support issues that we receive we've we write up articles for those and it would be worth your while to check out the knowledge network when looking for information about updates or troubleshooting so it's good stuff there good stuff all right hey big surprise I just found out about this myself but for all of you Revit users actually BIM folk all around we are going to be starting a series for building your Revit IQ this begins August 24th and the first session is a beyond the basics what's new in Revit 2017 and then back to basics from AutoCAD to Revit yeah I know it's a little backwards there okay but it makes sense really anyway join us for that the link here is going to be to the landing page where you can sign up for those webinars also Ryan or naman can paste the link into the chat window if you want that right now alright so we're going to talk about DB connect and I just realized I used an older image here it doesn't really apply to DB Connect okay what does DB connect its database connectivity it's a way for AutoCAD to work with data that is external to AutoCAD this was around a long long time before tables were ever introduced in AutoCAD in fact DB Connect was introduced in AutoCAD mm and the DB connect dialogues and editors will reflect the fact that it is from AutoCAD mm it's it looks a little antiquated but it does work very well it is a kind of like a subset of the AutoCAD map database connectivity it doesn't have quite all that functionality but it does allow you to connect to say a Microsoft Access database and Oracle sequel server if you're using dBase even Microsoft Excel spreadsheets so this is a way to tie in and work with your data and view that data in a what's called a data viewer or a table in an AutoCAD drawing so what became working with a database I'm created one that'll be part of the data set that's available for you for those who don't know too much about databases it's a very simple database with one table we'll talk a little bit about tables I've already created them so we're not going to create them in this session but we will create a data connection as between AutoCAD and that Access database and we'll take a look at the AutoCAD data view table which is basically the access table in AutoCAD and how you can link and even label objects in the AutoCAD drawing using this utility I've also added some additional resources in the slide deck for when you download it talking a little bit more about the DB connect manager and the first or the second and third link accessing external databases with AutoCAD and AutoCAD database connectivity I just want to point those out those are links to two books which are available I will say that I believe both of these books were written for AutoCAD mm one of them may have been written for 2004 but there's not much written literature about DB Connect especially since we now have the tables with linking to excel not of yet so it's been kind of neglected but it is a powerful tool so additional information on the additional resources page oops we don't want to say goodbye yet just go ahead and take a look at the AutoCAD environment real quick let's maximize this there we go all right so DB connect like I said earlier is a utility that was introduced in AutoCAD 2002 bars you're not going to find DB connect on the ribbon okay it's not going to be a pallet listed with all the other palettes it is a pallet but it's just not available there it is available on the tools toolbar an AutoCAD and don't have the toolbars enabled here so I am going to type in DB c4 DB Connect which brings up this palette here and I can dock that just like an AutoCAD any other AutoCAD palette but as you can see it's it's kind of an older style palette it does have some of the functionality of the newer palettes but for the most part the icons and such actually look a little old-fashioned compared to the newer ones we have available having done that just to show you where that command is as a command prompt and what the dialog looks like let's now take a look at the access microsoft access portion of this I'm using Microsoft Access 2013 and I think 2016 is now the the newest version but what I've done is I've created a table here and it's just a very simple one I could have gone into creating multiple tables creating relationships but this isn't about this being an access class okay it's more about AutoCAD and working with that access data and here I have some PC models work somewhere workstations some are laptops the manufacturer PC cost I could have maybe put in another column here which would be quantity or something which could be used with the DB connect utility but I didn't and I didn't put more information in here either because that's just a lot of typing and so we'll let it go at that all right so this is the table we'll be working with the one that you will see that right now now the way it works with AutoCAD and Microsoft Access is that in order for them to communicate we need to actually create a connection a way for those two applications to communicate one of these is to we need to create a data for use a data provider to speak to the Access database and the way we do this is well there are a couple of different ways you could go through control panel I typically tend to just type in ODBC whoops ODBC at the in the search field and under programs here we have listed data sources ODBC one thing I want to point out is I is that I am using Microsoft Office 2013 it's a 64 bit office I'm also using a 64 bit version of AutoCAD in order for these applications to talk to each other I had to download 64-bit drivers ok so in this case i download loaded the the 64 bit Microsoft Access 2013 runtime which is easily available from the Microsoft website I've put a link into the script as well as that slide I showed you I'm not going to recreate the data connection here that the provider is installed and I've called this by clicking on add here ok I have selected the Micra soft access driver now cancel out of that once I did that it had me provide a name for that which is the DB connect or a b2b connect name that I used here okay then the second thing I did was I had to tell how to get where what this driver was going to be speaking to so what database so all I had to do was go through here and browse to my database location and select that again these dialogues are a little bit older and it does make it hard to work with the longer file names we have nowadays but I can live with that anyway so that is creating the OD ODBC data source and that's all we really had to do there was just quickly connect that and I've in the script I've for those who have never done this before I provided step by steps once I did that I'm going to go ahead and open up my working drawing right now once I did that in the data base connection manager DB connect I had to tell AutoCAD look this is the provider I'm working with and I need to configure that data source and what we're going to do is we're going to create what's called a UDL file okay this is a um it's actually a Microsoft data link file why why it's called UDL only the people who threw this together know actually at one time I knew but that was years ago and I forget so we're going to go ahead and just give this here a name you could call it anything Fred if you wanted to but I'm going to be a little more specific and I'm going to call it maybe PC data and I'll click OK now once I do that I need to select the Microsoft oledb provider for ODBC drivers ok and depending on what kind of a provider data source and providers you have available what kind of a database you're working with you're gonna have to select different provider but this is the one that works well for the scenario that I'm working with now I can either click Next or click on the connection tab either one takes me to that connection tab and then under used data source name I'm going to select the b2b connect and all I have to do right now is make sure that the connection works if the database for password protected I'd want to add that here or be prompted every time I use the tool I'm gonna click test connection and it succeeded so I'll click OK and I'm done with that you'll see that now under data sources we have a new UDL and this is DB PC data I'm going to pause for a moment I'm gonna click options and I just want to show you quickly that 100 the files tab support file search path name we have this data source location this you can redirect this you can also just knowing where this is at you could open up that folder and actually just delete those UDL files or copy new ones in there that you've created elsewhere of course there's still gonna be some configuring but the naming convention will be the same so just for what it's worth it's right there alright so we've created a way for AutoCAD to speak with the Microsoft Access database and now we're going to have an in quick introduction to the data viewer in AutoCAD BB connect and right now you'll see a little I know it's probably a little harder to see but there's a little X going through the database symbol here and that just means that yeah everything set up but nothing's talking right now I'm going to all I have to do though is double click on that and I've made that connection I've opened up the communication and obviously obviously just taking a look it shows that there's one table in that database and that also is a good indication that everything is communicating as it should I have options here I can right click to view or edit this table create a link template or a label template or even create a query and the same goes for the icons up here and they pretty much do the same thing edit view new query new link template new label template hmm like I said they're kind of old-fashioned icons but they still do the job anyway I am just going to double click on this table and it opens up the data view and again some of the older icons here we can create or both link and label templates here we can view linked objects in the drawing view linked to records in the debt of your by selecting an object in the drawing out of you will actually zoom in or or hit well it will show you where the object is in the drawing there's some settings will change later which will allow us to automatically zoom in on those objects so it's kind of a cool functionality there as well we can create a query we can print this data view and we and change some options here right now anything I do in this table for example if I want to add another PC to this table and I'm going to do that I'll call this the TR TL 100 and it'll be by Acme computing or electronics I think I put in the script and in this case will be a tablet not a computer and we'll just make up a number like 299 dollars and we have added that to this table so what happens here is that I'm going to close this and actually I'll just go ahead and close AutoCAD we do need to do that because if I go into excess you will see that we have made those changes to the Access database table so anything we do in AutoCAD deleting records or adding records to the table are going to be automatically committed to the Access database and will go ahead and cruise right back in the AutoCAD and I was just thinking as this starts up that I should probably go ahead and activate it one day I'll probably wait till the last minute but either way we've gone ahead and taking a look to see what the dead of year does with the excess database table so be careful be careful about that if you're doing this you could be very productive or very counterproductive databases tend to commit or save the moment you add information to them all right I'm going to go ahead now and we're going to talk a little bit about what are called link templates and label templates so we're going to create some links and associate them with an object in the drawing and label those objects as well okay the first thing I'm going to do is create what is called a link template this defines what field in the Access database is going to be associated with the object now right-clicking over table PC table in the datasource I'm going to select new link template and this here goes works the way it typically did in databases where we cannot have spaces in a name okay so we're just going to call this here I think I called it PC link and you'll notice if I put a space there actually and start typing let's see link the continue button craze out so in case that straight out it's because you've got a space in there so I'm gonna call this PC link and I'm going to click continue I could have also started with an existing template which I don't have when I click continue and in this link template dialog I'm going to choose what field I want to associate with that object in the drawing in this case I'm going to use PC model now I'm going to click OK and we've created a link template which now appears in the TV connect manager all right mouse click over that link and you'll see here we can view or edit the table again we can delete this link template we can modify it and we need to change the name or choose a different field we can also create a copy of it to use with a different field in the database as well as some other options down here so we'll go ahead and get out of that let's go ahead and create a new label template works the same way we're going to call this PC label when I click continue and again this is older technology if anybody remembers the earlier end text editor this should be a blast from the past ok because this is basically what that have text editor looked like there's some different functionality here though first of all I'm going to go ahead and go to the character tab here to assign a text height for my label in this case I'm just going to type 3 inches and I think I'll leave it at that as you can see there are other options here we did add a some symbology including anything from the windows character map any kind of symbols within that font change the color underline bold acceptor etcetera just like in any other text editor we also have properties here where we can change justification rotation with etc a Find and Replace option and we have label fields and label offset so the label offset will I'll talk more about that as we play some labels in the drawing how this might be important to you right now I'm going to choose label fields and here we have this drop-down list for all the different fields within that database and again I'm going to choose PC model and I'm going to click Add and you could append or put a suffix on this text just by typing in this editor I'm not going to do that I'm just going to leave it as it is and click OK we have successfully created a PC label for our project here all right so let's put that stuff to use let's go ahead and link some objects here and see how that works and add some labels as well so I am going to open up the data viewer by double clicking on it and for right now go ahead and just kind of drag this off to the side and docket this data viewer does not dock or anything so just be aware of that but one of the things that first thing I'll show you here actually is how we can assign a link there are ants three settings for creating links okay one is just to create a link and basically unless you were to select the object or do some kind of a query or use the data viewer you would not know that an object is linked unless you select it and right mouse click you would then in the context menu see that a link was assigned and you could manage it through that you can also create an attached label using the template we just created or a free-standing label I'm going to go ahead and just keep it on create links I'm then going to select the same row and I'm going to select link and we'll go ahead and use this guy here as our guinea pig and having done that it highlights the role or the record that was linked to an object we can change that color but again I can't tell that anything has been linked here unless I'm right mouse click and here I can either export that link delete the link or going to the link manager I'm going to go ahead though right now and label this link and it lists the link template that we have or the label template I should say and I'm just going to say okay and it creates this label so you need to think I'm going to get to here is one you need to think about your text height also the labels themselves it's kind of clunky can't say more than that about it it's kind of clunky it it makes it a little hard to adjust if I just select the text though it's going to move the text as well as the leader assigned to it if I were to select the leader then it just moves the leader and leaves the text in place okay so you may need to do some adjusting if you want to see the label here the this is where in the label editor when you created that if you were to go to the that last tab and now I forget that title on it but in that M text editor you can change the offset and and the spacing between the the objects when using a leader label alright having done this I'm going to go ahead and select the object again right mouse click link and I'm going to delete the link and that removes everything so it's no longer linked in the database and it has removed all the text and the leader as well you know let that data viewer again and I'm going to go ahead and and yeah I'll use a different one this come on it's the alpha zero one and this time I'm just going to go ahead and create an attached label I'll go ahead and say link that and it creates this now here's a cool little thing if I select the object I'm just going to right mouse click and drag this for a cop to make a copy all right copy that here it also copies the existing link so it's route it's not a big deal just to link one object then make copies of it and place it elsewhere in the drawing and once you've done that you would select your new object let's go ahead and close this you would select your new object and go into the link right mouse click link link manager and what you can do now is assign this new object to a new different model of computers so selecting under the value field in the link manager there's a little button once you click in that field that brings up some column values for the PC model all the values in that column and here I can select sandhya the CX 1000 and click OK now here's the thing it did not update kind of like an attribute you know and we have to synchronize the attribute in this case we don't have to go quite that far if it doesn't updates select that object again go into label right mouse click go into label and just reload and it'll update we're going to take look at that and this time I'm going to go ahead and zoom out a little ways here just so we can see the other computers we have and I'm going to go ahead and pretty much do the same thing I just did a moment ago I'm going to go ahead and link with attached labels link and yeah awkward moment here and select link there we go all right pick pick pick pick so I can also do it this way and then again I would want to go ahead and probably change that and let's go ahead and just go into link manager and this is actually the longer way of doing it but it's the choice I made and I'm gonna have to live with it and so are you unless of course you don't want to you could pray for your book I guess alright so let's go ahead and just change or label here whoops I want a link manager didn't I am alright change one warned then we'll leave it be let's go with the turbo seven all right and we've reload that so you should all know how to do that by now so here's a nice little thing now going back into that data viewer we're going to go ahead and select one of these and we can do something like view linked object so where are all the halfa zero ones okay so I'm going to select view linked object and it has sumed me in to where that the object is with that particular link assigned to it now it doesn't do this by default all right you need to go into the and I clicked on this little button right here which is the data view and query options I clicked on that and I had to enable by default this is set to something like this unchecked unselected for automatic leap and drawing and unchecked for automatically zoom drawing so we do want to make sure those are checked and then every time you choose to view the record it'll zoom in as best as it can if there's more than one object it's only going to zoom out so far needs to show all those objects so let's try that with the zx100 in fact and see it had to zoom out but it's showing me all three of those okay one more actually two more items if we have time here one more if we don't and up we already did that so we're going to go to the last item that I wanted to talk about again keep in mind this is a glossy overview of the DP Connect utility there's a lot of things you can do with this that we don't have time for in this presentation and one of those is the ability to create queries this again it's not a database class but I do want to show this bit of functionality here even if you don't know too much about queries you can crew click on the new query button in the database viewer or you could right-click over the table in the DB connect manager and select new query either way it brings up the new query dialog you can give it a name I'm just gonna leave the default for right now because I'm kind of ad-libbing this one here wanting to show it if I had time and I do I don't have any existing queries to work with so I'm just going to click continue and what's nice about this query editor I mean so query queries are created using what's called sequel or structured query language you can get pretty ding-dang complex with these queries you could actually build an entire sequel database with a query ok we're not going to go anywhere near that I just wanted to show the functionality here and kind of show you a nice thing for people who aren't familiar with queries you really don't want to know that much about it there's this quick query builder here I can choose a field PC model or PC type I'll do that I'm that's totally ad-libbing right now and I'm gonna say I'm gonna equal to and since I can't recall what all the values where I'm just gonna click on lookup values and I'll say ok find all the PC types that are a workstation and it doesn't matter what tag I or I field I used in my link template this is completely different here I'm gonna click OK and then I'm gonna well actually had run execute but let's take a look I had a couple of the tabs here we can add additional parameters using the range query using a query builder which is kind of like a little wizard that allows you to build on top of this quick query or if you are into writing sequel alright this is what we told by clicking on the sequel query tab this is what I told it the quick query I said select from the tea-table PC where the PC type is equal to workstation ok those are the computers or objects I want to see in this drawing when I click execute and I could add on to this I could say and PC model is the ZX 1000 or whatever either way I'm gonna click execute and it selects all of those which are workstations and in the data view it shows me what PC models those are and PC types so again it can be a very complex query it can be a very quick query and there's a lot more you can do with these link templates and different methods to synchronize different ways to work with the objects in your drawings but this gives you a taste of of what DB Connect is all about the resources on the power point that I could and providing those will help you if you want to explore this any further speaking of power points I'm going to go ahead and go back to our slides here real quick this is the resource page I referred to earlier so before we quit here I got have two things I'd like to do one just always want to thank you for joining us we're going to do some Q&A here in a moment don't forget you can email us if you'd like to if you have questions or requests and be sure to put built your AutoCAD IQ in the subject line otherwise we don't know if you're talking about Revit or licensing and installing or fusion 360 or whatever else we have webinars on you're also welcome to leave feedback in our forum on our feedback page our landing page right now one last poll just out of curiosity did you learn something new in today's session this is where we find out if these things are worthwhile or not and it's looking like about 3% are saying no and 97% are saying yes 98% yes 96% yes 4% no so it's switching back and forth so we're talking a little bit over 95% of you have learned something new and I'm going to go ahead and close that and just share it real quick and let's go ahead and share there we go yeah there we go so um first of all for those of you who learned something that makes it worthwhile for us to do this it's always good to hear you did learn something and hopefully we can provide you with more learning opportunities this the week's go by for those of you who didn't learn so much or maybe just you picked up something you forgot about you know we still are very grateful that you've joined us and hopefully we didn't bore you too much I think I only had one awkward moment today as I was doing pretty good and let us just kind of move on to the Q & A all right so check with and Ryan see if there's anything that needs to be answered well could somebody had asked about the text can you have a leader or you can you turn off the leader or somehow oh yeah I the one thing I did not demonstrate was the freestanding text so the freestanding label and the data link so let's see if we can just do that real quick add living on this one so if I just go ahead and I'll zoom in on this link here and we'll go ahead and go label and detach label keep link and it should do it yeah okay so that keeps the link name I can't remember if this one will do it or not no I'm gonna have to remove that link so that needs to be done initially and I've just never really worried about it that much I always use the label but hey it's been years since we used this link create link create freestanding labels and select link so it me for a location for the label I already had this selected and there we go and of course after that you can move that label wherever you want hopefully that was the question yes that was the question I had posted to help file link to it as well that explains how those labels work great great thank you so people are not frustrated Walker one more can you somebody have pointed out about the 32-bit of office 365 or access and on a 64-bit machine and they had mentioned about a driver that you can get make sure you get the 64-bit drivers can you touch on that again right so Microsoft typically want people to install the Microsoft Office even if they're running on a windows 64-bit machine chances are they've installed their Microsoft Office says the windows it's a Microsoft 32-bit office and if you're going to work with AutoCAD 64-bit and 32-bit office then you're not going to be able to connect to say Microsoft Access in fact when AutoCAD 2011 AutoCAD math 2011 came out it came with the 64-bit drivers I think or maybe was the other way yeah 64-bit drivers but office was had the 32-bit drivers and people weren't able to make connections to their data you know something like that the scenario was anyway so what you need to do in order to communicate you need to be on the same platform okay so 64 bit AutoCAD would require if you're using access anyway you would require you to have the 64 bit office and then you would go and find that link here then you would want to go to this link here for the Microsoft download page for the Microsoft Access 2013 runtime when you download it it'll prompt you whether you want to download the 32 bit or 64 bit runtime engine I am not sure if that clarifies it or not and bottom line is you need the same numbers 32-bit or 64-bit applications are going to have to be running the same architecture I don't know of any Bryant II know of any connectivity that would allow for 32-bit to 64-bit I don't know I don't I don't know if any you don't run into the same problem if you try to use datalink manager - because it uses Excel right so yeah it's just something you have to come you have to be aware of what what architecture your applications are and your operating system and yeah so be aware those type of things and access is the only one I'm aware of where you can download these drivers I would imagine sequel server oracle and so on they I think they all install everything that's needed for a connection does that help at all I think you answered the question well it wasn't a question was a statement and just morning--i clarification on that if somebody had also asked is there any way to see that what objects are connected via a link template or label template well that is actually when you're in the in here this view linked objects is going to show you every object that is linked to this particular record you can also let's see think here yeah yeah that was that was it right there so basically in the DAT of you had to get up to speed on this again it was really fun working with this again so a few linked objects and and you know you can you can select see more than one record to view linked objects and it's going to show you the different objects that were selected there's a turbo Saturn there's the forget what I called that one there SL z gk 0 i forget what that was for slow go or something I don't know but yeah so you can select you thing the data view table you can see what objects are linked in the drawing so if you don't have a label you know this is how you would do that perfect thanks for that all right well I guess we'll call it a day if that's about it I'm again I know your time is valuable so we all know that on our team so we are we're always happy to that you show up and we don't take you for granted okay and hopefully we'll continue to provide you with some good stuff as the week's move on which you guys a happy and safe fourth of July holiday be careful because we do want to see you come back take care

Channel: AutoCAD

Views: 17,989

Keywords: autocad, autodesk, autocad tutorial, autocad 2017, build your autocad iq, autocad 2017 webinar, autocad 2017 tutorial, autocad 2017 database, db connect, autocad data connectivity, dbase, oracle, autocad database, autocad webinar, cad design, modeling tutorial, autocad 2d, drafting, auto cad, autodesk subscription, technical drawing, autocad tips and tricks, software, cad, tool, tutorial, how to, ADSK_Webinar, Beyond the Basics, Microsoft Access

Id: hlN-7SfMRGo

Length: 56min 59sec (3419 seconds)

Published: Thu Jun 30 2016