Importing Data into MS Access with ODBC

Microsoft Access allows the user to import data originating from a large number of different kinds of data sources. Access can establish a live link to the data, acting like a front end using data from the back end. This tutorial takes the reader step-by-step through the process of linking to ODBC databases so that the data can be manipulated in MS Access for the benefit of the end user.

Converting any form of data to a format to fit the rows and columns of a table is at the core of RDBMS. Legacy data in flat files have the inherent limitations of data corruption, problems of maintenance, size, and so forth. When they get moved to the RDBMS, the individual pieces of data find themselves in well structured tables, and by establishing relationships of different kinds between these tables, finding data, and maintenance of data becomes a lot easier and a lot faster.

One of MS Access’s strength lies in its capability to import data originating from a large number of different kinds of data sources, text files, spread sheet files, and so on. In importing data, it has two different capabilities. It can import a table outright into the MS Access database, or it can establish a live link to the tables. In this second capacity, the data can stay where it is and the Access application acts as a front end using data from the backend. However, since it is file based, it functions more like a file server type of application. This makes Access well suited for multi-user scenarios, where several applications can feed off the single, main database.

This tutorial takes the reader step-by-step through the process of linking to ODBC databases so that the data can be manipulated in MS Access for the benefit of the end user. As previously mentioned the data can also be imported, and this will be considered in a future tutorial. It is assumed that MS Access is installed (this tutorial used the following version: MS Access 2002 (10.2627)-SP1 on windows XP-professional platform) on the desktop, which has access to the other databases, files that needs to be linked.

{mospagebreak title=Establishing a link to a database file}

Create a blank mdb file. The new database will have a default name, db1.mdb and it is recommended that you change this name to reflect your application. Here, it is called LinkToImport.mdb. The database is created in the default directory, C:My Documents. At this point there are no user created objects, except for several utilities needed to create various objects, such as tables, queries, forms, reports, and so forth, as shown in this picture.

blank database created new

Establishing a Link

Now click on File ->Get External Data-> Link Tables…as shown in this picture.

Import by linking

 

This opens up the My Documents folder for browsing, so that you can locate the file. It is quite possible that files may be located in other locations as well. Hence it is advisable that a preliminary search is made to determine the location of the various files. Clicking on the drop-down, Files of type: the various file types that Access can import into the database are displayed as shown.

Import fiel formats supported

The different file types supported are shown in the following list. In this tutorial we will be looking at the last item in the list, the ODBC databases.

  • Microsoft Access(*.mda; *.mdb; *.mde)
  • dBase III, IV and 5 (*.dbf)
  • Microsoft Excel (*.xls)
  • Exchange()
  • HTML documents(*.htm, *.html)
  • Outlook()
  • Paradox(*.db)
  • Text files with several extensions(*.csv, *.txt, *.asc, *.tab)
  • ODBC databases
{mospagebreak title=Configuring an ODBC Source}

In order to connect to a database we need to have what is called an ODBC datasource. This is created using the 32bit ODBC Manager installed when Office 2000 is installed, or gets installed with the operating system. In Window XP Professional, you can get to the ODBC Administrator by going through Start ->Control Panel -> Administrative Tools -> ODBC Sources Short-Cut ->ODBC Data Sources Administrator. Now click on ODBC Data Sources (the previous screen shot) to open the picture shown next.

This is a part of the ODBC Data Sources Administrator macro. There are two tabs, File Data Source and Machine data Source. By default, it opens up the File Data Source tab in the clicked position. By choosing the Machine Data Source tab all the users of this machine and the system can use the data source. There are a number of data sources already created and these are shown in window.

machine data source

User Data Source and ODBC Drivers

Now click on New… to open the next window, where you choose whether only you can access, or all the users of the machine can access this resource. Here, the User Data Source choice is made. Click Next.

User data source

This opens up the next window, where you make a choice as to the driver you may want to use. As you can see here, there are a large number of drivers for diverse kinds of data sources, such MS Access, MS Excel, FoxPro, Oracle, and so on. This window is important as you can see the version and dates for the various drivers. We further assume that we will link to a file on an MSDE (Microsoft SQL Desktop Edition) database on the machine. After choosing SQL Server we click next.

ODBC Drivers

You will be shown a short summary screen indicating the type of datasource you created: (User data Source) and the driver you have chosen (SQL Server). Click Finish to this screen. This opens up the next window where you need to configure certain features of the MSDE database.

{mospagebreak title=Configuring the New Data Source to MSDE}

This brings us to the next window, Create a New Data Source to SQL Server, where you type a name for the ODBC data source you are creating. Here it is called LinkTut. You may give a description; here it is the title of this tutorial. Now click on the drop-down after the label, Which SQL server do you want to connect to?:. This should show all the available servers, here the XPHTEK/Test instance of the MSDE database is chosen. Click Next.

MSDE Configuration

The window shown next pops up. In this window you need to choose the way you desire to get authenticated. There are two options, Windows integrated authentication, or SQL Server authentication. Whereas for the former you require a network logon, for the latter you require a login name and password recorded in the SQL Server to which you are connecting. Here the former is chosen. You may also place a check mark to use other default configuration settings. Click next.

Database authentication

In this screen, the default database master is brought up, but you can alter the default database. Here a database called myPubs has been chosen. You may accept the other defaults. Click Next.

Default database

There are a number of settings which set up various options, but defaults  are good enough for this tutorial. Click Next and then click Finish to the window that shows up. You will come to the final window shown after the next picture. The picture shows all the configurational values you chose as well as those from the accepted defaults. If you find that the defaults are not the ones you want, you may edit the configuration settings by going through the same steps.

Options/settings

Finishing touches and testing the connectivity

Configurtional settings

 

You may test the connection and then click OK. The window that pops up shows LinkTut as a new DSN under the Machine Data Source tab as shown. Now click OK to this screen.

User DSN: LinkUut

{mospagebreak title=Importing the tables from MSDE}

The following screen shows up, where you make choices as to the tables you want to link to. Tables are the only objects you may link to. The prefix dbo for the tables indicate they are being linked with dbo permissions. Highlight the tables you want to link to, and click OK. You may also save the password with the link so that you may not need to enter this information again.

Tables to which links will be established

You are done!

Now the link to the tables has been established as seen in this picture. Four tables have been successfully linked using the ODBC Connection to the MSDE database. Table names have come over with a dbo prefix and the icons show that they are ODBC database tables.

linked tables in Table view

{mospagebreak title=Imported table/Original table comparison}

Design view comparison

These two pictures show the design of the tables that were linked and the original table design in the MSDE database. The data types in SQL Server are different from those of MS Access, and the linked table design features are not allowed to be altered.

design view of linked table

 

same table in MSDE[from Enterprise manager]

Comparison of tables

 

 

Summary

This step-by-step procedure should help in establishing ODBC links to tables in MSDE. The procedure is generic and can be used for other databases which one can find, or download drivers from third party vendors. As it was seen in this tutorial, the linked table data types will be those of the MS Access native type rather than of the originating database. The table design cannot be changed in the linked tables. Using MS Access with linked tables can effectively shield applications from data and make data accessible to a number of applications sharing the same data.

3 thoughts on “Importing Data into MS Access with ODBC

  1. ODBC makes connecting to databases so easy. From legacy text file stored data to data on the most advanced servers can be accessed. This is a must if you are working with databases. You are invited to make comments, raise questions, answer questions raised by others. All of this make this kind of format most useful.

  2. You did if we may say two very excellent ASP SQL Server tutorials. One was on, “Using Data Transformation Services” to use SQL Server to transform data into MS-Access tables. The second was on, “Importing data into MS-Access with ODBC”. In this second paper, on the first page, you note that one option is to, “import a table directly into MS-Access” — but, “this will be considered in a later tutorial”.

    May we ask, is the second tutorial any where’s near ready or perhaps in fact available? We think there is a real need for folks who wish to get tables of SQL server data — but, who do not necessarily have SQL Server installed on their machine, perhaps because they are working off-line or maybe just because they are “MS-Access folks”, but not so much, “SQL Server folks”. They thus wish to convert a SQL Server table containing say text data into MS-Access. It seems to us as both MS-products that, perhaps within some limits, that this ought to be possible! But, so far, a least from our investigations to date of the available doc, it is not clear that it actually is possible to do the import — at least, without SQL Server being available and running, also, on the same machine? If this is in fact so, maybe this is because of the different database objects either side, or, the security settings design –or, perhaps commercial intent? Or, maybe you can indicate a path how to do this?

  3. Yes you can import the tables. The tables will be part of your MS Access database and will reside in your mdb file. In order to do this you need not have SQL Server on the local machine [perhaps on a node on your intranet], but you must have access to it as a user with login permissions. I have not completed my tutorial for this, and when it is ready it will be posted. The steps are similar to the linking that was in the earlier tutorials.

    sincerely,

    Jayaram Krishnaswamy

[gp-comments width="770" linklove="off" ]