Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 23rd, 2005, 06:10 PM
Friend of Wrox
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default Export table with Specs

Hi Everyone,

I need to export a table to a file, and include the headings, and use ; as the delimiter. I can do it through the export function, using a specification, but would like to completely automate the process using VBA. Any suggestions appeciated.



Reply With Quote
  #2 (permalink)  
Old December 24th, 2005, 02:24 AM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Hi Mike,

I did this in ADO, though if you're just writing out Jet tables DAO would be the recommended choice (per MS). The conversion would be super simple.

Also, I just provided the nuts and bolts using a few BASIC I/O functions. There are a few things you'd probably want to add to make the code more robust, based on what you need and what your data looks like. You'd want some error handling to see if the file you're writing to already exists, then either kill it, or open it for append. Also some error handling to see if the recordset is empty or not, etc. You may want to scrub the output a bit also, replacing single quotes in your data steam with double quotes, that sort of thing.

I do have the code check for null fields, and just insert a ";" if it finds any.

The only argument you need for the function is the name and path of the file you want to create, like:


The function returns a long to indicate success or failure. I usually just return a 0 in the body of the function for success, and a 1 in an error handler for failure. Anyway, here's the function:

Function ExportRecordset(strFileName As String) As Long

   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field

   Dim strDelimiter As String
   Dim intFileNumber As Integer
   Dim intCount As Integer
   Dim intNumberofFields As Integer
   Dim intIndex As Integer

   Set cnn = CurrentProject.Connection

   Set rst = New ADODB.Recordset
   rst.Open "tblData", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

   strDelimiter = ";"

   '-- Build header string --
   For intCount = 0 To rst.Fields.Count - 1
      strHeader = strHeader & strDelimiter & rst.Fields(intCount).Name
   Next intCount
   strHeader = Right$(strHeader, Len(strHeader) - Len(strDelimiter))

   intFileNumber = FreeFile
   Open strFileName For Output As intFileNumber

   '-- Write out header --
   Print #intFileNumber, strHeader

   '-- Write out records --
   intNumberofFields = rst.Fields.Count - 1

   Do Until rst.EOF
      For intIndex = 0 To intNumberofFields
         If (IsNull(rst.Fields(intIndex))) Then
            Print #intFileNumber, strDelimiter;
            If intIndex = intNumberofFields Then
               Print #intFileNumber, Trim$(CStr(rst.Fields(intIndex)));
               Print #intFileNumber, Trim$(CStr(rst.Fields(intIndex))); strDelimiter;
            End If
         End If
      Print #intFileNumber,

   Close #intFileNumber

End Function


Reply With Quote
  #3 (permalink)  
Old December 24th, 2005, 03:22 AM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Hmm...I dont' seem to have declared strHeader. You might want to do that.

Also, just a word of warning. When you're playing around with this type of I/O code, always let it run to Close #intFileNumber if you can. If you don't close the file you won't be able to recreate it or delete it latter. You'd want the Close command in an error handler too, and I keep a sub routine with just the line "Close 1" handy, just in case things blow up before this line is executed.


Reply With Quote
  #4 (permalink)  
Old December 25th, 2005, 01:16 PM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

quote:You may want to scrub the output a bit also, replacing single quotes in your data steam with double quotes, that sort of thing.
Not exactly what I meant to say (was trying to write SQL and talk about table exporting at the same time).

What I meant to say is that, when you export a table via the user-interface commands using either a default or a user-defined export specification, notice that your exported string values are enclosed in double quotation marks. You won’t see that behavior when programmatically exporting with BASIC I/O functions. If you want your string data enclosed in some kind of text qualifier (e.g., double quotes), you will need to concatenate the text qualifiers in code yourself.

A better example of export data scrubbing would be if your data has embedded carriage return or line feed characters (perhaps in a memo field), and you wanted to replace the CrLf stuff with, say, a single space (“ “).

Best way to add export functionality to your apps is to create a class module that handles all your text exporting. Then you can add additional functionality as you need it, simply by adding additional properties and methods to your class, while keeping it all encapsulated in one place. You can then execute an export with code like:

Sub ExportTable()

   Dim objTextExport As clsTextExport
   Set objTextExport = New clsTextExport

   ‘Set objTextExport properties
   With objTextExport
      .AppendToFile = False
      .ExcludeFields = ""
      .ExportDatabase = CurrentDb
      .ExportFilename = "C:\Export.txt"
      .ExportSource = "tblData"
      .ExportType = "ASCII"
      .FieldDelimiter = vbTab
      .IncludeFieldNames = True
      .RecordDelimiter = vbCrLf
      .ReplaceWith = " "
      .TextQualifier = Chr(34)

      ‘Call objTextExport methods
   End With

End Sub



Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Export a Table into Word hewstone999 Access VBA 0 March 20th, 2008 06:28 AM
Export Table data into an Excel SpreadSheet hewstone999 Access VBA 0 March 3rd, 2008 07:00 AM
How do I export an access db table to xml? solos VB How-To 4 April 3rd, 2007 11:56 PM
Export Table from Access to Excel aramchan Access VBA 2 July 12th, 2004 03:20 PM
Transfering Import Specs Morris Access 3 August 15th, 2003 08:09 AM

All times are GMT -4. The time now is 06:50 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.