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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.