Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    97

    Unanswered: Importing Delimited Text

    Okay,

    I've created a subroutine to import delimited text into an existing table. Basically, I'm trying to automate the Wizard by creating a check box that will then automatically import those files I've checked off. However, when I run the code it kicks back saying that F1 is not a field in my existing table.

    Is there anyway to bypass the acImportDelim command from validating the non-existant field headings in my text file against the already existing field headings in my table?

    Previously, I'd created a work around for this by importing the data into a dummy table then automatically running an APPEND query to update the real table with the dummy tables data but this seems a bit cumbersome. Any suggestions?

    Thank you
    Chris

    --------------Code Follows---------------------

    Sub ImportText(strTbl As String, strLoctn As String)

    DoCmd.TransferText _
    TransferType:=acImportDelim, _
    TableName:=strTbl, _
    FileName:=strLoctn, _
    HasFieldNames:=False

    End Sub

  2. #2
    Join Date
    Dec 2002
    Posts
    97

    Lightbulb A Fix....sort of....

    Okay...

    So here's what I did.

    First, I found an article
    (http://support.microsoft.com/default...b;EN-US;208582)
    on creating an import spec...now...while I've used this many times before outside of VBA the thought that I could use it in my code escaped me.....sigh.....

    Once the spec had been created and the appropriate values were fed to the sub...everything worked fine.

    Now here's the killer question....I got a bunch of tables that would need specs created.

    Is there a) a way to generate specs on the fly
    b) a way to access or export the specs that can then be
    used by other Access Dbs?

    Thanks in advance.
    -C




    ----Code Follows------

    Sub ImportText(strSpecs As String, strTbl As String, strLoctn As String)

    DoCmd.TransferText _
    TransferType:=acImportDelim, _
    SpecificationName:=strSpecs, _
    TableName:=strTbl, _
    FileName:=strLoctn, _
    HasFieldNames:=0

    End Sub

  3. #3
    Join Date
    Nov 2003
    Location
    Melbourne Victoria Australia
    Posts
    7

    Importing Text Files

    I do my imports this way.

    Import the file using the Wizard, creating a new table from the text file.
    Save the Imports Specs, (Advanced )
    I empty the table of all records and rename the fields as required.
    I again import the text file, this time into the previously created table.
    (This is to ensure all is working OK)

    use the File / Specs/ Table in an Import macro

  4. #4
    Join Date
    Dec 2002
    Posts
    97

    Wizards

    I will also import using the wizard when I'm only updating one table or when I need to create specs based on a new text delimited file.

    However, in this instance I do this daily with the same five or six text files and it gets tedious. I'm trying to automate some of the process to save myself a bit of time and a few headaches.

    -C

Posting Permissions

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