How to Configure ODBC to Access a Microsoft SQL Server

okay so we're going to cover how to configure ODBC to connect to a back-end Microsoft sequel server okay for this example I have two shortcut icons on the desktop and one is the Microsoft Access 2010 icon and the other is the data sources ODBC icon I'm going to be using Microsoft Access to access the data once the ODBC connection has been made so the first step is to go ahead and launch the data sources control panel applet and from this source administrator window you basically have multiple tabs the two most the two most common tabs you'll be using is easier the user DSN or the system DSN both of these tabs can be used to connect to a back-end system the difference is that the user DSN tab will expose these data sources to the currently logged on user versus the system DSN where if you create any here they'll be available to any user that's logged down to this computer for this example i'll go ahead and create a user DSN and I'll go by starting by clicking the Add button and we want to connect to a sequel server so I'll select that okay we've got to give this connection a name I'll just call it on itg sequel test you can give it a description if you like and need to specify the sequel server name click Next okay so you can connect to the sequel server by either using your current logon credentials which is the first option here using Windows NT at that occasion or you can specify user name and password to connect to the back-end sequel server I already have a user account and password defined on sequel so I'll go ahead and choose that option and I'll type in my credentials here click Next I'm going to go ahead and change the default database rather than connected to master I'll connect to a test database that I've already created no other options need to be changed I'll click Next and on this screen unless you want to save queries and statistics to a log file go ahead and just click on finish if you like you can review your configuration and test the data source by clicking on this button you'll notice that the test completed successfully which means that I was able to make a successful connection to the sequel server click OK you'll notice that our connection is now listed as a user data source and hit OK to finish ok so we're going to go ahead and launch Microsoft Access 2010 so we can access our data on the sequel server and we'll go ahead and create a blank database now I'm not going to store any data in this local access file so going close this table and we need to go ahead and get to our external data so we'll click on the external data tab and since this is an old DBC connection we'll launch that wizard now once we launch the wizard we have two options either we can import the data into a table into this Access database or just go ahead and link to the data source in the backend without storing local data that's what I want to choose right ok ok go ahead and select your data source we're going to go on to the machine data source tab and I'm going to select the data source that I just created in the previous steps click OK type in your credentials click okay okay once I make the connection sequel server will provide me with list of all the objects that this user has access to in this case I have a lot of objects presented to me because this test user was created with vast amounts of rights and privileges but for this is for this example I'll go ahead and just link to the customers table and click OK and you'll notice that once I connect it shows an externally linked table called DBA consumers and if I double click that I'll have access to the table on the back-end server now depending on the rights that this user has you can either just select data from the table you can insert new data you can delete data or you can just update existing data for instance I want to add a record to this database I can let's go ahead and add some data some system test information here so this using particular aside from being able to insert data can also delete data so I can right click for instance a row and delete record so again this data is actually the changes to the data are actually been made on the sequel server itself on none of this data is being stored in this local Access database file I'm just using access to interface with sequel that's it for this demonstration of configuring ODBC to connect to a sequel server thank you for watching

Channel: IT Geared

Views: 504,950

Keywords: ODBC, How-to, Microsoft Access, Technology, Data, Software, SQL, Software Tutorial, System, Access, Express, Connection, Training, Systems, Information, Database

Id: tUiaK5fRH7k

Length: 5min 47sec (347 seconds)

Published: Wed Nov 09 2011