Exporting Query into tab delimited text format

I assume that you've already declared your variables and set the connection to the database as follows:

  Dim your_ADO_Conn As New ADODB.Connection
  Dim rs1 As New ADODB.Recordset
  Dim strNew As String

Step1: Open/run the select query.
--------
  rs1.Open "SELECT * FROM yourtable", your_ADODB_Conn

Step2: Create and open a new text file to export your data into.
--------
  Open "C:\temp\mydata.txt" For Output As #1

Step3: Loop thru the recordset and output all field values.
--------
  Do While Not rs1.EOF    
    For i = 0 To rs1.Fields.Count -1
      strNew = strNew & rs1.Fields(i).Value & vbTab
    Next
    Print #1, strNew      'output the current record to text file
    strNew = ""             'clear variable before the next record
    rs1.MoveNext           'go to the next record
  Loop

>> I need to export a select query as a tab delimited txt file and store it in a specifice folder and I need this file to erase and replace any existing file in that folder.  Is this possible. <<

When you use the "Output" keyword on the "Open" statement, any existing file will be replaced automatically with the new file.

With your "strNew" variable, better declare it as of type Variant to be flexible to hold any type of data.

I use  the "vbTab" built-in constant which is equivalent to Chr(9) to make your code more self-documented.

@jkaios - wouldn't recommend using variants - the file is going to get outputted as a string anyway and variant's are incredibly slow.

also, try this for the inner loop:

dim fld   as field
dim sTemp1 as string
dim sTemp2 as string

  Do While Not rs1.EOF    
    For each fld  rs1.fields
      sTemp1 = sTemp1 & fld.value & vbtab
    next fld

    if len(sTemp1) > 2000 then
      sTemp2 = sTemp2 & sTemp1
      sTemp1 = ""
   
      if len(sTemp2)>64000 then
        print #1, sTemp2
       sTemp2 = "'
      end if
    end if

    rs1.movenext
  loop

print #1, sTemp2 & sTemp1

there are 3 optimisations here - if you are dealing with a large numbers of records, you'll notice a massive performance increase:

a) using for each fld in... means that the fld object has already been resolved and an additional object lookup does not have to be done for each field

b) vb's string handling is appaling so when a string gets over 2000 chars or so it's much more efficient to pack those 2000 chars onto another sttring and start the first one again

c) dumping a small amount of data (ie. 1 record) to a file at a time has a big overhead - the most efficient size is between 32K and 64K of data at a time.

good one aikimark - just remember if any of the columns could be null, you need to do this (and in sqlserver its char not chr):

rs1.Open "SELECT isnull(col1,'') & char(9) & isnull(col2,'') & char(9) & isnull(col3,'') & char(9) & ...

I assumed this was an MSAccess database.  As such, the & operator takes care of handling Null value concatenations.  I don't see a need to use the IsNull() function.  Furthermore, the IsNull() function in VB and MSAccess SQL environments only has one parameter.  You might be thinking of the NZ() function.

If SQLServer, then we need to use the + character to concatenate strings.

ok - thought this was sql server. in sql if 1 element of an expression is null, the whole expression evaluates to, hence the use of isnull(). isnull in vb has 1 param but sql server has two - 'value' and 'new value'.

The comments about string concatenation speed are true.  If you must do a lot of string concatenations (which you don't in this instance), I'd recommend something like VB.Net's StringBuilder class.  There are several fast concatenation routines available for free download from various sites.  I also wrote my own StringBuilder class to accomodate lots of concatenations, which I will post if anyone is interested in this side topic.