Results 1 to 5 of 5

Thread: Auto import

  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Unanswered: Auto import

    Hello.

    Is there an easy way to import .txt data into Access 2000 mdb file without repeating myself everytime after changing this .txt file.

    I have one .txt file which I manualy import on my computer in 7 steps:

    1. Open mdb database
    2. choose table Articles and delete all records which I need to import (in other hadn, I just need to update changed values, and add new)
    3. File -> Import -> Get External Data (select .txt and choose file articles.txt)
    4. choose Advanced Tab, select my predefined Specification, next, next
    5. choose where to store -> I choose In an Existing table and choose empty table Articles
    6. Next, Finish
    7. Confirm YES

    Is somehow possible to to all that via only one click on some desktop icon and get this automatically?
    Thanks.

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    You could try this. I hasten to add that the code is not mine, but cannot remember from whence it came.

    1.) Use the Import Text Wizard to set up and save an "Import Specification". This will save you the trouble of have to re-define this spec every time that you need to import this file.
    2.) Create a command button on a form. (turn off the wizard until done)
    3.) Open it's property sheet and name it "cmdImportTextFile".
    4.) Scroll down to the "On Click" event. Click on the ellipse (...) and choose code builder.
    5.) Copy and paste this code sample in, then modify it to reflect your file, import spec and table names.
    6.) This sample also includes the use of a function called "IsTableQuery()" that tests for an "ImportErrors" table and deletes it if it exists. I do that because there is seldom anything usable in the table anyway, and it causes an error if not deleted before the next time that the code runs.
    7.) In order for you to use this function, you will need to follow the instructions in this Microsoft KnowledgeBase article: INF: How to Determine If a Table or Query Exists Article ID: Q113549
    http://support.microsoft.com/default...&Product=acc97

    Private Sub cmdImportTextFile_Click()
    On Error GoTo cmdImportTextFile_Err

    'Syntax: 'DoCmd.TransferText [transfertype][, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename]

    'Sample: DoCmd.TransferText acImportDelim, "My Import Specification", "tblMyTable", "C:\MyDir\MyFile.txt", True

    DoCmd.SetWarnings False

    If IsTableQuery("", "tblMyTable_ImportErrors") Then DoCmd.DeleteObject acTable, "tblMyTable_ImportErrors"
    End If

    DoCmd.SetWarnings True

    '.... whatever else you might like to do here...

    End Sub

    David

  3. #3
    Join Date
    Jan 2004
    Posts
    30
    Thank you, it works...

  4. #4
    Join Date
    Jan 2004
    Posts
    30
    I forget only to say that it is all good if I firstly delete all records, but If I don't then my table doesn't update :-(.

    Is there any possibility that this Button click firstly delete all records in table and then after that to import all recors from .txt file.

    Thank you.

  5. #5
    Join Date
    Mar 2004
    Posts
    82

    Question

    To DJN:

    I liked the suggestion; I am looking for something similar, the difference being I am importing an Excel file into a table. I would like the import to check and modify data types in the Excel table to be similar to the specs in the table and append to existing data. I do not want the import to fail because the person handling the Excel spreadsheet forgot to modify the data types. Suggestions?

    Thank you.

Posting Permissions

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