Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old October 22nd, 2007, 01:00 AM
Authorized User
 
Join Date: Mar 2007
Location: Sydney, NSW, Australia.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default Import File Attributes

Hi All

I am inporting a txt file daily, using the vba transfertxt command with a saved specification name, this is all working fine.
However i now need to get the date created/modified date from the txt file and store that date in a table (can be either same import table or a new table) can anyone help with how the get file attributes works.

Regards

Darren

  #2 (permalink)  
Old October 22nd, 2007, 06:59 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Dim objFSO As Variant
Dim objFile As Variant
Dim sFile As String
Dim dtCreated As Date
Dim dtModified As Date

sFile = "C:\Path\FileName.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(sFile)
   dtCreated = objFile.DateCreated
   dtModified = objFile.DateLastModified

This could be added to the table that is storing the data in two fields, which will be the same for each record retrieved. If there is an autonumber PK, take the MAX() of that field before you bring in the new data, then update the table in those two fields for each record from the MAX + 1 to the end after the data is moved in, with the dtCreated and dtModified values. You could do that like this:

'Put this in a Module
Public iMaxPK As Integer

'Then take the value. Let me know if you need help with that.

'Add this code with you transfer text code, which should run the previous code before the import, or after, to get the dates:
Dim rs As ADODB.Recordset
Dim sSQL As String

sSQL = "SELECT * FROM tblYourTextDataTable WHERE [PKFieldName] > " & iMaxPK

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rs.MoveFirst
Do Until rs.EOF
   rs("DateCreated") = dtCreated
   rs("DateModified") = dtModified
rs.MoveNext
Loop
rs.UpdateBatch

rs.Close

Did that help?



mmcdonal
  #3 (permalink)  
Old October 26th, 2007, 09:12 AM
Authorized User
 
Join Date: Mar 2007
Location: Sydney, NSW, Australia.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you for your help

Worked like a charm, i had to modify a little
But as usual your suggestion solved my problem

Thanks Again
Darren




Similar Threads
Thread Thread Starter Forum Replies Last Post
import file from jar geek.shrek XSLT 3 November 9th, 2007 06:19 AM
How can i import a local text file into a jsp file ravibabu Java GUI 1 April 25th, 2007 04:05 AM
Reading material attributes from an autocad file kesakiran C# 0 December 1st, 2006 01:24 AM
how to import from the same file kgoldvas XSLT 1 February 14th, 2006 06:45 AM
SWF File Import Ben Horne Flash (all versions) 2 December 10th, 2003 04:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.