Other Applications

As mentioned briefly in Chapter 1, the world today is full of data, and not all of it is stored in databases. Daily we have to deal with text files, spreadsheets, mail systems, and so on, and it never seems to stop. We as programmers would like it if our bosses (and every one else's too, come to think of it) just junked all those old machines and applications, and brought us heaving and groaning into the 21 ^st century. However nice an idea that is, it seems unlikely to happen, especially considering that by the time you've upgraded the whole office, the first machine you bought is now out of date.

So we're stuck with our own old applications, and other people's, too. These applications may hold their data in a different format. This isn't a bad thing, as Access has built-in support for the most common data formats.

Databases

You've already seen that there are different versions of Access, and there are many more databases around than you'd realize. Transferring between these other databases and Access is pretty straightforward, because we can do it with one simple command - the TransferDatabase method of the DoCmd object:

 DoCmd.TransferDatabase [TransferType], DatabaseType, DatabaseName,   [ObjectType], Source, Destination, [StructureOnly], [StoreLogin] 

This is quite simple to use. For example, to export our ice cream details to a database called Prices , we could use this:

   DoCmd.TransferDatabase acExport, "Microsoft Access", _     "C:\IceCream\Prices.mdb", acTable, "tblIceCream", "tblIceCream"   

This says the following:

• The TransferType is acExport , so we are exporting something. There is one of three values (intrinsic constants) that can be used here, the other two being acImport to import some data, and acLink to create a link to some data. Linking means that Access stays connected to the data source, so that it is always up to date. Of course, linking also means that the original data source needs to be available whenever you use the transferred data in Access.

• The DatabaseType is "Microsoft Access", because we are transferring to another Access database.

• The DatabaseName is "C:\IceCream\Prices.mdb" , which is the database where the data is being transferred to.

• The ObjectType is acTable , to indicate we are exporting a table.

• The Source is "tblIceCream" , which is the name of the table in our current database.

• The Destination is "tblIceCream" , which is the name the table will take when it's in the new database. If this table already exists it will be overwritten.

When you use this method you need to make sure that both the directory and the target database exist, otherwise an error will be generated. One way to deal with this would be to use error handling - we'll be discussing this in chapter 11.

Importing some data would be just as simple:

   DoCmd.TransferDatabase acImport, "Microsoft Access", _     "C:\IceCream\Prices.mdb", acTable, "tblIceCream", "tblIceCream"   

Here the difference is the TransferType argument, which in this case is acImport . This tells Access that we are going to import data. Notice that nothing else has changed. The DatabaseName stays the same, but since we are now importing, it becomes the source of the data, and the current database becomes the destination. That's an important point, because it shows that the DatabaseName argument changes its role depending on which way the data is going.

Since there are several options for these arguments, let's have a look at them in detail:

Argument	Description
TransferType	The action to be performed. It must be one of: acImport , to import data acExport , to export data acLink , to link data If you leave this blank, the default of acImport is used.
DatabaseType	The type of database that you wish to transfer from or to. It must be one of: Microsoft Access Jet 2.x Jet 3.x dBase III dBase IV dBase 5 Paradox 3.x Paradox 4.x Paradox 5.x Paradox 7.x ODBC Databases
DatabaseName	The full name of the database to import from or export to. This must include the path , and if exporting, this must already exist. If the DatabaseType is "ODBC Databases" then this parameter is the ODBC Data Source Name, and not the database path.
ObjectType	The type of object to be exported. it must be one of: acDataAccessPage acDefault acDiagram acForm acFunction acMacro acModule acQuery acReport acServerView acStoredProcedure acTable If you leave this blank, the default of acTable is used.
Source	The name of the object that is supplying the data.
Destination	The name of the object once it is transferred.
StructureOnly	You should set this to True to indicate that only the structure of the object should be transferred, and set it to False (which is the default) to transfer the structure and the data. This allows you to just copy table details without copying the data.
StoreLogin	You should set this to True if you are connecting to an ODBC database and wish the user details to be saved with the connection. That way if you connect to the same source, you won't have to enter the user details again. Setting this to False (which is the default) ensures that each time you connect to an ODBC database you have to supply the user details.

Try It Out-Exporting to a Database

1. Select New from the File menu.

2. In the New Task pane, select Blank Database . Name the new database Sales and place it in the same directory as your other databases from the book (on the download CD, we've placed this file inside a Chapter08Files subfolder to keep things tidy):

   

3. Now close this database and switch back to IceCream.mdb .

4. Create a new form, not based on a table or query, and put a command button on it. Call this button cmdSales and give it a suitable caption:

   

5. In the Click event for this button place the following code (remember you can create this event procedure by clicking the right mouse button and selecting the Build Event option from the menu that appears):

      Application.DoCmd.TransferDatabase acExport, "Microsoft Access", _     "C:\BegAccessVBA2002\Sales.mdb", acTable, "tblSales", "IceCreamSales"     MsgBox "Sales data exported"   

   Note that this assumes C:\BegAccessVBA2002 is your database directory. If your databases are elsewhere then change this directory name. Note also that you do not need to include the Application object prefix for DoCmd , since it is implicit. We have included here for clarity, but DoCmd would also work just fine by itself, and the rest of the examples in this chapter will use DoCmd by itself.

6. Switch back to Access, run the form, and press the button to export the data.

7. When the message box pops up, you can close this database. Don't forget to save your changes - save the form as frmImportExport .

Open up the Sales database ( Sales.mdb ) and have a look at the new table that you've just created.

Notice that the fkCompanyID and fkIceCreamID now only show numbers whereas before (in the IceCream.mdb ) they showed the names of the company and "IceCream"

This is because the fields fkCompanyID and fkIceCreamID are lookup fields , and get their values from other tables - the company table and the ice cream table to be precise. Since we've not transferred these tables Access can no longer look up the text values, and so displays the underlying foreign key values instead, which in this case happen to be numbers. You can also see that Access still displays the fields as combo boxes but with nothing in the drop-down list. Again this is because it has nothing to look up. If you are happy with the numbers but want to remove the combo boxes then you can open the table in Design view, and remove the Row Source property of each field (on the Lookup tab), or choose TextBox from the Display Control property.

If you would really like to display the text in the fields, instead of just the numbers you should export the missing tables ( Company and IceCream ) using the TransferDatabase method you have just tried.

Spreadsheets

Transferring to spreadsheets is probably more common than transferring to databases. Access is really good at reporting, but most people who need to analyze figures are more familiar with spreadsheets. Things like Pivot Tables and Charting are very powerful and useful in Excel, and although you can use them in Access, most people are more familiar with the Excel environment. You probably won't be surprised to learn that there's a TransferSpreadsheet command:

 DoCmd.TransferSpreadsheet [TransferType], [SpreadsheetType],  TableName, FileName, [HasFieldNames], [Range] 

You can probably piece together how it works, but let's have a look at the arguments:

Argument	Description
TransferType	The action to be performed. It must be one of: acImport , to import data acExport , to export data acLink , to link data If you leave this blank, the default of acImport is used.
SpreadsheetType	The type of spreadsheet you wish to transfer to, or import from. It must be one of: acSpreadsheetTypeExcel3 acSpreadsheetTypeExcel4 acSpreadsheetTypeExcel5 acSpreadsheetTypeExcel7 acSpreadsheetTypeExcel8 acSpreadsheetTypeExcel9 acSpreadsheetTypeLotusWK1 acSpreadsheetTypeLotusWK3 acSpreadsheetTypeLotusWK4 acSpreadsheetTypeLotusWJ2 If you leave this blank, the default is acSpreadsheetTypeExcel8 . You may have expected to see an acSpreadsheetType10 or acSpreadsheetTypeXP here, but they are not available or needed, since Excel XP can happily open on files created in acSpreadsheetType9 , so we can use that instead .
TableName	The table name that the data should be imported into, or linked into; or a table name or query that is the source of the data to export from or link from.
FileName	The full name of the spreadsheet, including the path. If this file does not exist before you use the TransferSpreadsheet command, it will be created automatically. However if it does already exist, then it will be overwritten without warning.
HasFieldNames	When importing or linking, you can set this to True if the source of the data has field names as the first row. The default of False is taken if you leave this argument empty, which assumes that the first row contains data.
Range	A valid range of cells , or the name of a range, in the spreadsheet. You cannot supply a range when exporting. The syntax must be valid for the spreadsheet type you are importing.
useOA	This is a strange one - though listed in the documentation, there is no mention of what it does, and setting it to true/false (or anything else since it is a variant), appears to have no effect! Further research seems to suggest that it really does nothing of use for us, and can be safely ignored.

If you are exporting to an existing workbook, then the data is created in the next available worksheet.

Try It Out-Exporting to a Spreadsheet

1. Back in IceCream.mdb , open up frmImportExport in design mode.

2. Add another button, call it cmdSalesSheet , and give it a caption of Sales Spreadsheet .

3. Create a new query, adding the tblCompany , tblSales, and tblIceCream tables. You need to add all of the fields from the sales table to the query (except for fkCompanyID and fkIceCreamID , as these are just the ID numbers, and not the names). Instead of these two fields add CompanyName from tblCompany and IceCream from tblIceCream . Your query should look like this:

   

   This just links the three tables together, and outputs their names. You should also add a sort on the DateOrdered field. Save the query as qryAllSalesFigures .

4. Now in the code cmdSalesSheet command button, enter the following code:

      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _     "qryAllSalesFigures", "C:\BegAccessVBA2002\Sales.xls"     MsgBox "Sales data exported"   

5. Switch back to Access. Click the button, and a spreadsheet should be created at the specified location. You'll have to open in to see it though:

   

Later in the book we'll show you other ways of transferring data to Excel, using OLE Automation. Now, though, we're going to look at importing and exporting simple text files.