Solved

How do I get the "Source" field updated to the name of the record's import file?

Posted on 2015-01-21
10
216 Views
Last Modified: 2015-01-29
This loop works to import all the files in the folder, but once the file is imported, I need the "Source field of each record in the import updated to the name of the import file (extension not needed).  How do I reference that?

Private Sub RefreshPDMObjectsBtn_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM PDMObjectsT"
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = "G:\XE_ECMs\IPP Sharing Development\Audit Data\"

strTable = "PDMObjectsT"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames
      DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= 'FileNameGoesHere' WHERE Source IS NULL"
'       Kill strPathFile

      strFile = Dir()
Loop

End Sub

Open in new window

0
Comment
Question by:Jay Williams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
10 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 40562286
change this

DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= 'FileNameGoesHere' WHERE Source IS NULL"

with

DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strFile &"' WHERE Source IS NULL"

or

Currentdb.execute  "UPDATE PDMObjectsT SET Source= '" & strFile &"' WHERE Source IS NULL", dbfailonerror
0
 
LVL 84
ID: 40562290
Changing the following line would update the table:

DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strPathFile & "' WHERE Source IS NULL"

However this would update ALL records in that table. Is that what you want?
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40562291
DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strPathFile  & "' WHERE Source IS NULL"
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 17

Expert Comment

by:Barry Cunney
ID: 40562294
Hi Jay,
If you embed the strFile variable(this variable holds the file name) into your UPDATE statement it should give the required
so concatenate this variable into the UPDATE statement
Private Sub RefreshPDMObjectsBtn_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM PDMObjectsT"
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = "G:\XE_ECMs\IPP Sharing Development\Audit Data\"

strTable = "PDMObjectsT"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames
      >> DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strFile & "' WHERE Source IS NULL"
'       Kill strPathFile

      strFile = Dir()
Loop

End Sub

Open in new window

0
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 250 total points
ID: 40562311
To get the file name without the extension you can do something like
Dim  strFileNameWithoutExtension$
 strFileNameWithoutExtension$ = Left(strFile, (InStrRev(strFile, ".", -1, vbTextCompare) - 1))

so the above looks for the . in the file name and gets everything to the left of this

you can then use  strFileNameWithoutExtension$ in the UPDATE statement instead of strFile
0
 

Author Comment

by:Jay Williams
ID: 40562341
You guys are good--and FAST!  Thanks!
0
 
LVL 84
ID: 40562358
Why did you accept that as the answer? Rey gave you exactly the same answer in his first post.
0
 

Author Comment

by:Jay Williams
ID: 40562376
I meant to accept multiple solutions, but I'm new and don't know how this thing works yet.  I'm here to learn--obviously.  Don't want to ignore anyone.  Please accept my sincere apologies and help me correct it if possible.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40564167
@Jay Williams

you can ask to reopen the thread by clicking on the "Request Attention" located just below your original post.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question