access acimport

i run the following code in my database

For Each Item In fld.Files
FileName = Item.NAME
DoCmd.TransferText acImportDelim, , "tbl_order_audit", foldername + FileName, True

I want to be able to add Foldername + Filename to the end of each record that is added. Every time this is run it imports around 10k lines into my database.

I also run this code for a pipe delimited file, however i tried it with the other file and it doesnt work due to them being different formats.

For Each Item In fld.Files
    If InStr(Item.NAME, "RSP") Then
        FileName = Item.NAME
        Open (foldername & FileName) For Input As #1    ' Open file.
        Do While Not EOF(1)    ' Loop until end of file.
        Line Input #1, TextLine    ' Read line into variable.
        Debug.Print TextLine    ' Print . Textline would be your data value for use in your own 'split' code.


        avarSplit = Split(TextLine, "|")
        For intIndex = LBound(avarSplit) To UBound(avarSplit)
              Next
        CurrentProject.Connection.Execute _
         "insert into tbl_all_audit ([field1], [field2], [field3], [field4], [field5], [field6], [field7], [field8]) select '" & avarSplit(0) & "', '" & avarSplit(1) & "', '" & avarSplit(2) & "', '" & avarSplit(3) & "','" & avarSplit(4) & "','" & Date & "', 'Response', '" & foldername & FileName & "'"

I am using SQL 2008 backend + adp frontend.
LVL 6
CaptainGibletsAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your code just imports records into a table named "tbl_order_audit". YOu can then modify those records as needed, but I'm not sure what you mean by "add Foldername + filename to the end of each record". Are you adding this to a FIELD in the table? You'll have to give more specifics.

Regarding your different formats: You should ask that as a separate question.
0
 
CaptainGibletsAuthor Commented:
sorry i missed a line of code off

foldername is the name of a mapped drive (M:\imports\)

this then looks through every txt file (filename) in that folder (around 1k of them) and imports each part of information into the database.

However what i want to do is add a field onto the end of the import which will write basically the whole unc path of the file it imported that particular row from (foldername + filename)
0
 
JezWaltersCommented:
As LSMConsulting has already suggested, have you thought of executing and ALTER TABLE/UPDATE query after each import?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
CaptainGibletsAuthor Commented:
but if it imports 1000 text files all into the same table, once it's in that table, how will i be able to tell which text file it was imported from?
0
 
JezWaltersCommented:
You can import each file one at a time, and after each one has been imported you can then run the appropriate queries to add the field (if necessary) and then set the field to the UNC of the file you've just processed.

Are you with me?
0
 
CaptainGibletsAuthor Commented:
i think i get what you mean, something like

For Each Item In fld.Files
FileName = Item.NAME
DoCmd.TransferText acImportDelim, , "tbl_order_audit", foldername + FileName, True
CurrentProject.Connection.Execute "update tbl_order_audit set field = '" & foldername + filename & "' where field = ''"
next
0
 
JezWaltersCommented:
That's the idea - it looks like you know what you're doing, but please ask if you need further assistance!  :-)
0
 
CaptainGibletsAuthor Commented:
seems so simple when somebody points you in the right direction! Thanks for help.
0
 
CaptainGibletsAuthor Commented:
I had pressed the wrong person to give points to, wanted them split between JezWalters and LSMConsulting.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.