Re: Export text file

From: Joe Fallon (jfallon1_at_nospamtwcny.rr.com)
Date: 02/11/04 

Date: Wed, 11 Feb 2004 00:34:57 -0500

Yep.
The word you are looking for is: disambiguate.
It is better to use just DAO and no ADO reference at all.
Then you don't need to use a qualifier:
DAO.Recordset

Glad you got it going! 

-- 
Joe Fallon
Access MVP
"ctdak" <anonymous@discussions.microsoft.com> wrote in message
news:9370DA56-93ED-407C-B734-042FE3D771AD@microsoft.com...
> Joe,
> Your answers have been a BIG HELP!  Thanks for taking the time.  Yes, I
added the reference to DAO 3.6.  I don't plan on migrating my application to
anything else.  It will stay in Access.  What you are saying is that DAO is
all I need in that case.  Is that correct?
>
> Also, because I currently have both ADO & DAO referenced, I noticed two
recordset objects in the drop down list.  You say the code can get confused.
Dumb question:  Does that mean you have to somehow know which of the two
recordsets belongs to which object library?  In other words, can you pick
the wrong one from the list and mess things up?
>
> ctdak
>
>      ----- Joe Fallon wrote: -----
>
>      Be sure you have references to DAO 3.6.
>      May as well reomve reference to ADO if you do not need it.
>      (They both have Recordset objects and the code gets confused if you
>      reference the wrong one.)
>      This is the #1 issue in A2000 and newer.
>      I think in A2003 MS *finally* decided to make DAO the default
reference
>      again and let users choose ADO if they want it.
>      -- 
>      Joe Fallon
>      Access MVP
>
>
>
>      "ctdak" <anonymous@discussions.microsoft.com> wrote in message
>      news:C53DC850-5CB0-4F73-9A38-5224ADEB4DB9@microsoft.com...
>      > Joe,
>      > Your code has been a big help.  However, program execution stops
at:
>      >> Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)
>      >> I get Run-time error 91, "Object variable or With block variable
not set"
>      >> My Set statement is identical to yours, except for a query name
string
>      literal
>      > instead of strTable.
>      >> I have Dim statements:
>      > Dim rs As Recordset
>      > Dim CurrentDb as Database  (I added this one, but it didn't help)
>      >> Any idea why I am getting this error?
>      >> Thanks again,
>      > ctdak
>      >>      ----- Joe Fallon wrote: -----
>      >>      If you write VBA code you can do pretty much anything you
like with
>      the data
>      >      as you write it to the file.
>      >      You should build 2-3 procedures simialr to this (1 for each
major
>      section of
>      >      your file) and then call them when needed. If you take your
time and
>      review
>      >      this code you will see it is not really that difficult to
follow.
>      Then take
>      >      the ideas and expand on them. (e.g. need data from 2 tables -
open 2
>      >      recordsets!)
>      >>      Here is a sample of some general export code:
>      >>      Public Sub ExportDelim(strTable As String, strExportFile As
String,
>      >      strDelimiter As String, Optional blnHeader As Boolean)
>      >>         'strTable is the table or query name
>      >         'strExportFile is the full path and name of file to export
to
>      >         'strDelimiter is the field deliminator to use like Chr(9)
for tab
>      or
>      >      Chr(44) for comma or ??
>      >>         Dim fld As Field
>      >         Dim varData As Variant
>      >         Dim rs As Recordset
>      >         Dim intFileNum As Integer
>      >>         'set recordset on table or query
>      >         Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)
>      >>         'get file handle and open for output
>      >         intFileNum = FreeFile()
>      >         Open strExportFile For Output As #intFileNum
>      >>         If blnHeader Then
>      >            'output the header row if requested
>      >            varData = ""
>      >            For Each fld In rs.Fields   'traverse the fields
collection
>      >               varData = varData & fld.Name & strDelimiter
>      >            Next
>      >>            'remove extra last strDelimiter
>      >            varData = Left(varData, Len(varData) - 1)
>      >>            'write out the header row
>      >            Print #intFileNum, varData
>      >         End If
>      >>         'now your data
>      >         Do While Not rs.EOF
>      >            varData = ""
>      >            'concatenate the data row
>      >            For Each fld In rs.Fields
>      >               varData = varData & fld.Value & strDelimiter
>      >            Next
>      >>            'remove extra last strDelimiter
>      >            varData = Left(varData, Len(varData) - 1)
>      >>            'write out data row
>      >            Print #intFileNum, varData
>      >>            rs.MoveNext
>      >         Loop
>      >>         Close #intFileNum
>      >         rs.Close
>      >         Set rs = Nothing
>      >      End Sub
>      >>      -- 
>      >      Joe Fallon
>      >      Access MVP
>      >>>>      "ctdak" <anonymous@discussions.microsoft.com> wrote in
message
>      >      news:70B924E2-05EC-46D1-988C-CA6375793221@microsoft.com...
>      >> John,
>      >>> I have to do 4 main things to the text file that I can't do with
>      >      TransferText:  1) I have to add two unique header records and
one
>      unique
>      >      trailing record,  2) I have to combine fields that have been
output
>      from
>      >      different tables,  3) I have to conditionally omit fields
altogether
>      in
>      >      certain records depending on the contents of other fields, and
4) I
>      have to
>      >      change the output format of some field types (such as removing
the
>      time in a
>      >      Date/Time field and the $ sign in a Currency field).  These
types of
>      things
>      >      can easily be done with Basic code, but can they be done from
VBA?
>      >>>>>      ----- John Marshall, MVP wrote: -----
>      >>>      How do you want to modify it?
>      >>>      John...    Visio MVP
>      >>>>      "ctdak" <anonymous@discussions.microsoft.com> wrote in
>      message
>      >>      news:3675D79C-3E35-4DB9-BF48-5781D5713145@microsoft.com...
>      >>> I have successfully exported data from Access 2000 into a text
>      file
>      >      using
>      >>      a custom export specification.  I'm getting the data I want.
>      >      However, the
>      >>      resultant text file records are still not in the form that my
>      >      accounting
>      >>      software needs.  Is there any way in VBA to open a text file
>      and
>      >      modify it
>      >>      record-by-record, or do I have to use Visual Basic outside of
>      >      Access/VBA to
>      >>      do this?  Any direction would be appreciated.
>      >>>

Relevant Pages

  • Re: Query error after converting
    ... If removing the reference to ADO solved the problem, ... I already mentioned that Recordset is an object in both the ADO and DAO ... libraries for much of its functionality, and any external library it uses is ...
    (microsoft.public.access.conversion)
  • Re: Error Type Mismatch : Runtime error 13
    ... Both the DAO and ADO models have Recordset objects in them. ... You're trying to use DAO. ... Access 2000 and 2002 only have a reference ...
    (microsoft.public.access.forms)
  • Re: Any REAL reason to use ADO vs. DAO?
    ... I've run this test and I've _NEVER_ seen DAO faster than ADO ... has little to do with marshalling a recordset object across processes. ... Here both, ADO and DAO, don't allocate much Memory (no Copy ...
    (microsoft.public.vb.general.discussion)
  • Re: FindFirst error
    ... That was fine in Access 97, when there was only one kind of Recordset ... DAO recordsets and ADODB recordsets. ... you have both the DAO and ADO object libraries referenced. ... I suggest you simply drop the reference to ADO entirely. ...
    (microsoft.public.access.modulesdaovba)
  • Re: convert query into xml-file
    ... You can use both DAO and ADO in the same Access application. ... e.g. "Dim rst AS DAO.Recordset" or "Dim rst As ... ADODB.Recordset" rather than just "Dim rst As Recordset". ...
    (microsoft.public.access.queries)