Solved

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

Posted on 2015-01-21
10
212 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
  • 2
  • 2
  • 2
  • +2
10 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero 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 34

Expert Comment

by:PatHartman
ID: 40562291
DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strPathFile  & "' WHERE Source IS NULL"
0
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 119

Expert Comment

by:Rey Obrero
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

947 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now