Exporting Database and Spreadsheet Files

These days, it isn't very likely that you would need to export data from Access to a dBASE, Paradox, or Lotus file (and in any case, as discussed in the "Exporting Text Files" section, you can export to a comma-delimited text file that can be imported into those applications). However, Access still offers the option of doing exports to these legacy applications, as discussed in this section.

If you select the "Export Data to App Files" option on the main menu and click the button to its left, the Export Job Data to Application File form (frmExportAppData) opens. This form (much like the Export Job Data to Text File form) lets you export a range of records filtered by date to a dBASE, Paradox, or Lotus 1-2-3 file. Figure 10.23 shows filtered data ready to export to a dBASE file.

FIGURE 10.23

Filtered records for export to a dBASE file.

Export Job Data to Application File

Export Date Range: Jobs to Inspect:

From Date: 7/10/2006

From Date: 7/10/2006

Product Number

Job Date/Time

Factory Name

Product! 0

Line t Batch Quantity 7

FRB-1009782-C

7/10/2006 1:13:00 PM

Nutley

201

1123.49

FRB-1013911-B

7/10/2006 2:24:00 PM

Oakdale

114

8877.2

FRB-1013187-D

7/11/2006 6:23:00 AM

Sîmms

114

3838.22

F RB-1013912-A

7/11/2006 8:43:00 PM

Nutley

201

348.98

FRB-1013917-A

7/13/200611:13:00 AM

Nutley

143

576.33

FRB-1011580-E

7/14/2006 2:23:00 PM

Oakdale

114

224.11

F RB-1013920-A

7/14/2006 5:39:00 PM

Simms

156

5788.1

FRB-1013924-B

7/15/200611:33:00 AM

Simms

143

837.22

*

Record! M ¡lof8 ►

W >■"- 1 t 1 ¡Search

Export File Type 191 dBASE © Parade»

Inspect New Jobs to Export!

Clear Jobs to Export

Export File Type 191 dBASE © Parade»

Inspect New Jobs to Export!

Clear Jobs to Export

Export Jobs to Application File

The "Clear Jobs to Export" button clears the selected records, and the "Export Jobs to Application File" button does the export to a file of the selected format; this procedure is listed next. Similar to the other export procedures, this procedure sets up a Select Case statement to do the export differently according to the selected application type; the TransferDatabase statement with various arguments:

Private Sub cmdExportJobs_Click()

On Error GoTo ErrorHandler

Dim intFileType As Integer Dim strQuery As String

Dim strAppFile As String Dim strTitle As String Dim strPrompt As String Dim strOutputPath As String Dim strDBName As String intFileType = Nz(Me![fraFileType].Value, 1) strQuery = "qryFilteredJobs" strOutputPath = GetOutputDocsPath()

Trim off last backslash.

strOutputPath = Left(strOutputPath, _ Len(strOutputPath) - 1)

Select Case intFileType

Case 1

dBASE

strDBName = "Jobs.dbf"

strAppFile = strOutputPath & & strDBName

DoCmd.TransferDatabase transfertype:=acExport, databasetype:="dBASE IV", _ databasename:=strOutputPath, _ objecttype:=acTable, _ Source:=strQuery, _ Destination:=strDBName, _ structureonly:=False

Case 2

Paradox strDBName = "Jobs.db"

strAppFile = strOutputPath & "\" & strDBName

DoCmd.TransferDatabase transfertype:=acExport, databasetype:="Paradox 5.X", _ databasename:=strOutputPath, _ objecttype:=acTable, _ Source:=strQuery, _ Destination:=strDBName, _ structureonly:=False

Case 3

Lotus 1-2-3

strAppFile = strOutputPath & "\Jobs.wk1" DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeLotusWK1, _ TableName:=strQuery, _ FileName:=strAppFile, _ hasfieldnames:=True

End Select strTitle = "Exported jobs"

strPrompt = "Exported filtered jobs to " & strAppFile MsgBox strPrompt, vblnformation + vbOKOnly, strTitle

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

0 0

Post a comment