Beginning Access 2003 VBA

John Wiley & Sons

Chapter One

In Chapter 5, I covered the basics of using ADO and SQL to work with data sources. All the ADO and SQL examples dealt with data stored in tables in a database. However, in today's world of technology, you often work with data and applications in a variety of formats, such as text files and spreadsheets. You may need to import or link data from various sources into your database to avoid having to retype all the information that is already stored electronically in another format. At some point, another application might need the data in your application or you may want to get data out of your application for another reason. In that case, you can export information from your application into another format.

In this chapter, I will explore the various ways to use VBA code to link, import, and export to external data sources. The chapter will cover:

The difference between linking, importing, and exporting

Linking, importing, and exporting to external Access databases (MDB files)

Linking, importing, and exporting to SQL Server databases

Linking, importing, and exporting to other files such as spreadsheets and text files

Creating and sending an e-mail programmatically

The definition of a Web service and how you can use data returned from a Web service

These techniques will enable you to build robust applications that interact with a various applications and formats.

Linking, Importing, and Exporting Overview

Linking to external data means creating a pointer to an external data source that allows you to interact directly with the underlying data. Importing external data literally imports a copy of the data into your application. Exporting data refers to the idea of extracting data from your application to an external file or format.

Here are some situations when you should consider linking:

The data resides on a database server that your application and others can use.

The data is used by another program that requires the native file format.

The underlying data needs to be updated on a regular basis in its native format.

Here are some instances when you should consider importing:

An existing system is being migrated to a new application and the data from the old system will be used in the new application. (In some cases, you may be able to migrate to another system but keep the data on a database server without needing to import the data).

Numerous data operations must be run against the data from another source. You can obtain performance improvements by importing the data, but the underlying data will be out of sync if you make any changes to the data after it is imported.

Access allows you to link to and import from data sources such as Access (Jet) databases, SQL Server databases, other ODBC databases, Microsoft Sharepoint, XML documents, HTML documents, text files, Microsoft Exchange, Microsoft Outlook, and spreadsheets such as Microsoft Excel and Lotus.

Many of the techniques covered in this chapter can also be implemented using menus and wizards in Access. To import or link data using the menus and Wizards, select File[arrow right]Get External Data and then select either the Import or Link Tables option. You can export data by selecting a particular object (table, for example) in the Database Window, right-clicking, and selecting the Export option from the pop-up box.

Now that you understand the high-level concept of importing, linking, and exporting, you can jump right in to learning the techniques that will allow you to work with some of these supported data sources.

Create a blank database to use for the examples in this chapter. To do so, select File[arrow right]New[arrow right] Blank Database and specify Ch7CodeExamples for the filename and then click the Create button.

Access and Other Databases

You can use the TransferDatabase method of the DoCmd object to import from, link to, and export data to Access and several other databases, including SQL Server and Oracle. The basic syntax of the TransferDatabase method is shown in the following code.

DoCmd.TransferDatabase(TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin)

Various parameters are used to specify how the method should execute. The following table explains the use of each parameter.

Let's look at an example. Suppose you want to import data from an Access database called SampleDb. The data you want to import is in a table called Sales, and you want it to be imported to your current database under the name tblSales. You could run the following command from your current Access application.

DoCmd.TransferDatabase acImport, "Microsoft Access",_ "SampleDb.mdb", acTable, "Sales", "tblSales"

Here's an example that shows linking to a table called Sales in an ODBC database called Wrox.

DoCmd.TransferDatabase acLink, "ODBC Database", _ "ODBC;DSN=DataSourceName;UID=username;PWD=pwd;LANGUAGE=us_english;" _ & "DATABASE=Wrox", acTable, "Sales", "dboSales"

The ODBC data source name can point to any database that ODBC supports, including SQL Server and Oracle, to name a few examples. As with any linking operation, you see the table or tables from the Database Window in Access.

Try It Out Importing Data from the Sample Northwind Database

Now it's your turn to try this out. Let's import data from the sample Northwind database that comes with Access.

1. Insert a new module into your Ch7CodeExamples database.

2. Add the following code to the module.

Sub TestTransferDatabase() 'import from Northwind

DoCmd.TransferDatabase acImport, "Microsoft Access", _ "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb", _ acTable, "Employees", "tblEmployees" End Sub

3. Modify the preceding path to the location on your hard drive where Northwind.mdb is located. If you do not have the sample Northwind database installed, change the previous parameters to reference the Access database that you do have.

4. From the Immediate Window in the Visual Basic Editor, type TestTransferDatabase and press Enter to run the procedure.

5. Open the Database Window and you should see a screen similar to Figure 7.1.

How It Works

In this example, you used the TransferDatabase method to import data from the Northwind sample database. The parameters of the TransferDatabase method specified the various bits of information Access needed to perform the import.

Sub TestTransferDatabase()

'import from Northwind DoCmd.TransferDatabase acImport, "Microsoft Access", _ "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb", _ acTable, "Employees", "tblEmployees"

End Sub

After you ran the procedure , you should have noticed in the Database Window that the new table was inserted into your database.

Transferring Complete SQL Server Database

The TransferSQLDatabase method allows you to transfer an entire SQL Server database to another database. In effect, this method imports the entire SQL Server database into your Access database. Here is the basic syntax.

DoCmd.TransferSQLDatabase(Server, Database, UseTrustedConnection, Login, Password, TransferCopyData)

Various parameters are used to specify how the method should execute. The following table explains the use of each parameter.

For example, to transfer the entire contents of a database called Pubs to the current database, you can use a command similar to the following.

DoCmd.TransferSQLDatabase _ Server:="ServerName", _ Database:="Pubs", _ UseTrustedConnection:=True, _ TransferCopyData:=False

Spreadsheets

The TransferSpreadsheet method is very similar to the TransferDatabase method in that it enables you to import, link, and export, only in this case it deals with spreadsheets. The syntax is shown in the following code.

DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

Various parameters are used to specify how the method should execute. The following table explains the use of each parameter.

Now we'll walk through an example of how you might use the TransferSpreadsheet method to export data to a spreadsheet. Suppose you want to export the contents of the Employees table you just imported from Northwind into a spreadsheet so you can e-mail or send it to a colleague. The following code will create a new spreadsheet called Employees.xls in the temp directory.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _ "tblEmployees", "C:\Temp\Employees.xls"

An example of the spreadsheet created from the preceding command might look like the one shown in Figure 7.2.

You can also use ADO to select, insert, update, and delete the underlying data in most of the data sources in this chapter by specifying the correct ADO provider (in this case Excel).

Text Files

The TransferText method allows you to import from, link to, and export to text files. It has the following syntax.

DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

As you would expect, various parameters can be used to specify how the method should execute. These parameters are similar to the TransferDatabase and TransferSpreadsheet methods you have already seen. The following table explains the use of each parameter.

Try It Out Importing Data from a Text File

Now, you import data from a text file into a new table, called tblEmails, in your Access database.

1. Create a text file as shown in Figure 7.3 and save it in C:\temp.

2. Add the following procedure to the module in your database.

Sub TestTransferText()

DoCmd.TransferText acImportDelim, , _ "tblEmails", "C:\Temp\emails.txt", True End Sub

3. Run the procedure from the Immediate Window in Visual Basic Editor.

4. Return to the database window and you should see a screen similar to that shown in Figure 7.4.

How It Works

First, you created a text file that contained comma-delimited records. You then created a procedure to import the comma-delimited file to your database.

Sub TestTransferText() DoCmd.TransferText acImportDelim, , _ "tblEmails", "C:\Temp\emails.txt", True End Sub

The TransferText method imported the comma-delimited file into a new table called tblEmails, as shown in Figure 7.4. Note that the parameter for the SpecificationName was left blank because it is not required when working with delimited files.

XML Files

XML stands for eXtensible Markup Language. You have likely heard the XML buzzword, but you may not know what XML files really are. XML is a syntax that enables systems to create simple text documents with various tags that identify how the text should be interpreted. At the end of this section you will create an XML document from a table so you can see what one looks like.

The idea behind XML is to give various types of operating systems on different platforms a meaningful way of communicating with one another. As the use of XML has grown in popularity, the need to write Access applications that can import and export to XML text files has increased. Recent versions of Microsoft products incorporate extended XML functionality, including the ImportXML and ExportXML methods that Access provides to enable users to import from and export to XML databases. You will now look at each of these in turn.

The syntax for the ImportXML method is:

Application.ImportXML(DataSource, ImportOptions) The DataSource is the name and path of the XML file to import. The ImportOptions parameter can be acStructureAndData (default), acAppendData, or acStructureOnly. Thus, to import an XML document into a table in your Access database, you might use the following code:

Application.ImportXML "employees.xml", acStructureAndData

The ExportXML method allows you to export data in your Access database to XML files to exchange data with other applications. Here is the syntax.

Application.ExportXML(ObjectType, DataSource, DataTarget, SchemaTarget, PresentationTarget, ImageTarget, Encoding, OtherFlags, FilterCriteria, AdditionalData)

The following table explains what the various parameters of the ExportXML object are used for.

I said earlier that I would provide a sample XML file so you could see what it looks like. Well, it's now time to use the ExportXML method to export one of your tables to XML so you can see how it works.

Suppose you have the following procedure:

Sub TestExportXML() Application.ExportXML acExportTable, "tblEmployees", _ "c:\Temp\Employees.xml", _ "c:\Temp\EmployeesSchema.xml"

End Sub

The procedure uses the ExportXML method to export the Employees table in your database to an XML file called Employees.xml. After you run the preceding procedure, you create the XML file that looks similar to the XML file shown in Figure 7.5.

Notice in Figure 7.5 how the tags describe the data in detailed ways. This is a more detailed and structured way of organizing and describing data than HTML, which is just designed for displaying data.

That's all it takes to export data from your Access application to send to another system. You're now ready to learn how to send an e-mail from VBA code.

E-mails and Outlook

One way you can send an e-mail from VBA code is using the SendObject method, as shown below.

DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

The ObjectType, ObjectName, and OutputFormat parameters are used to specify a file created from the database to include as an attachment. Remember that earlier I said you exported the tblEmployees table to Excel so you could e-mail it to a co-worker. The SendObject method allows you to attach certain database objects in one of a variety of formats as part of the e-mail. Thus, to generate a new e-mail that also attaches the tblEmployees table as an Excel attachment, you could use something similar to the following:

'Send the Employees file DoCmd.SendObject acSendTable, "tblEmployees", acFormatXLS, _ "someone@yahoo.com", , , "Employee List", "For your review.", False

If you do not want to send an attachment, but just want to send an e-mail telling me how much you like the book so far, you can use the following command.

Continues...

---

Excerpted from Beginning Access 2003 VBA by Denise M. Gosnell Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---