Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO and text Import

    I recieved the following tip it would appear to be very useful but gives a run time error !invalid file path!. I have exhuasted my resources trying to resolve the problem. I have run this from the Access vb editor.

    Thanks to anyone who might be able to provide a solution.

    Accurately read text files into ADO recordsets

    Often, you may have cause to read a comma-delimited, or some other
    form of delimitation, into an ADO recordset. However, if your text
    file doesn't contain header information, you may encounter odd
    behavior in which ADO uses data in the first record as the recordset's
    field names.

    This glitch results because, by default, ADO assumes that each text
    file will contain headers on the first row. So how do you indicate
    otherwise? One hint can be found in the connection string generated by
    Visual Basic when it connects to a text file, as seen here:

    connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=" & App.Path & ";" _
    & "Extended Properties='text;FMT=Delimited'"

    As you can see, the connection string contains a section called
    Extended Properties, which indicates a delimited text file. By adding
    a third argument to this section, we can tell ADO that the file doesn't
    contain headers. The argument named HDR takes YES or NO as its sole
    value. With this extra argument in place, the completed connection
    string looks like this:

    connCSV.Open "Provider=Microsoft.Jet" _
    & ".OLEDB.4.0;Data Source=" & App.Path _
    & ";Extended Properties='text;HDR=NO;" _
    & "FMT=Delimited'"

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ADO and text Import

    Hi,
    If you add a Debug.print app.path statement, what do you get as the output?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO and text Import

    I get the correct path and file. If I use the same sting but point it at an Access mdb file it works, of course without the extended text properties included

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO and text Import

    The provider string is root of the problem. You're telling ADO that the connection is to the Jet 4.0 provider, when what you're actually looking at is a text file. You want to use MSDASQL.1 as the provider. I've never been able to figure out the correct connection string for this, so I'll leave the rest of the exercise to you.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •