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?
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
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?