VBA to Import Data from a tab delimited text file into certain fields of an access table automatically

Normally I link a table to such a text file and use an append query to "move" the fields to the appropriate fields in the target table.

Idea ?

Nic;o)

Hmm..

Rockiroads, My first reaction was to do the same but then had comlplications as we are trying to avoid to create new tables. Also, what I am trying to do is to create a button on a form which when clicked, reads the files and gets the values. This is because we get a new file every week but with the same name.

But I'll give your idea a shot. I'll let you know how it goes in an little while.

THanks!!

What's wrong with the linked table approach ?

Nic;o)

If you wanted to read instead of creating a temp table (either physical or linked like Nico suggested), then here is some sample code


    Dim iHandle As Integer
    Dim sLine As String
    Dim sFields() As String

    'Get a file handle
    iHandle = FreeFile

    'Open a file for read
    Open "C:\imp.txt" For Input As #iHandle
   
    'Read each line until no more...
    Do While Not EOF(iHandle)

        'Read line, assign into string sLine
        Line Input #iHandle, sLine

        'Show me what u got
        Debug.Print sLine

        'Put values into a string array
        sFields = Split(sLine, ",")

    Loop

    'Close    
    Close #iHandle



here my file contains the followng

ID,Txt
1,XXX
2,YYY
3,ZZZ


split will take individual values and you can then extract what u want, build a insert statement and run it to insert data
e.g.
split on 1,XXX will be

sFields(0) = 1
sFields(1) = XXX

To link a table to a textfile just create a new table and select the option to link.
On the Open dialog select as filetype .txt and navigate to your file.

After the ceation of the link open the query editor, select this linked table and the needed fields. Next change the query type into an append query and select the target table. Equally named fields will be "linked" automatically by Access, but just every "move" is possible.

Save that query and all needed is to run the query by a double-click.
You can even replace the textfile with a newer version (as long as the name remains the same) and rerun the query to get new data imported.

All without a single line of code.

Nic;o)

Nico,

There's nothing wrong with your approach as well but I was under the impression that the "linking" approach would fail if the file gets updated regularly.

But there's a slight problem that after linking the text file, date parameters got changed from 30/04/04 to 2030-04-04. How can I stop this?? if I change the parameters during linking process to text, then it transfers date in the exact format but then If I try inserting text from this temp table into a date/time parameter in the final table, the nwould it not create a  problem??

THanks for letting me know!!

Regards.

In the query you can transform the "-" into a "/" or to be 100% sure use the dateserial command like:

DateReformat:dateserial(mid(fieldx,7,2),mid(fieldx,4,2),mid(fieldx,1,2))

I used mid to get the same statement, but left and right can also be used.
Syntax: Dateserial(string with year, string with month, string with day)

Clear ?

Nic;o)

NICO,

No, I am more confused now..


<< Syntax: Dateserial(string with year, string with month, string with day)

Can you explain this a bit further??


<< DateReformat:dateserial(mid(fieldx,7,2),mid(fieldx,4,2),mid(fieldx,1,2))
Which query are you refering to and how to use DateReformat:Dateserial feature??

Thx..

Sorry, just too cryptical as usual, this is referring to a "field cell" in the graphical query editor.
In SQL text you would find:
select  dateserial(mid(fieldx,7,2),mid(fieldx,4,2),mid(fieldx,1,2)) as DateReformat, ...

Clearer?

Nic;o)

Cheers!!

HEllo Nico,

There is a slight problem that I have found with taking the linking approach. If I link a temp table to the text file (which contains the data in it)and then write an update query to update the main table, its not letting me run an update query on linked table. The update query is:
UPDATE [TEMP MASTER TABLE]
[TEMP MASTER TABLE] INNER JOIN [TEMP TABLE] ON
([TEMP MASTER TABLE].[Del] = cdate([TEMP TABLE].Field7))
AND
([TEMP MASTER TABLE].[Local Authority ID] = [TEMP TABLE].Field1)
SET
[TEMP MASTER TABLE].[Tape ref:] = [field2],
[TEMP MASTER TABLE].[Media Type] = [field8],
[TEMP MASTER TABLE].Density = [field9],
[TEMP MASTER TABLE].[DECKS USED] = [field10],
[TEMP MASTER TABLE].[Customer Records] = [field11],
[TEMP MASTER TABLE].[Non-Customer Records] = [field12],
[TEMP MASTER TABLE].[Number of Rejected Records] = [field13];

I am using the 'update' statement Bcos I need to fill in the details in the main table where ID in the temp table is equal to the ID in main table.

Its more easy to write a macro to import the data and run the SQL query and you can have a button which runs this macro whenever clicked.

Thanks for your help anyways!!

Regards,

Parag Chauhan