Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-01-21
10
Medium Priority
?
220 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 1000 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 85
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 39

Expert Comment

by:PatHartman
ID: 40562291
DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strPathFile  & "' WHERE Source IS NULL"
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 1000 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 85
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

604 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