[AccessD] Powershell...

[Stuart McLachlan]

A couple of initial thoughts:

"Register-ObjectEvent $watcher "Created" -Action $action
Does not mean that the file has been written to and closed.  

Have you tried writing $cmd.CommandText to log file to see what you are executing?



On 16 Aug 2018 at 22:23, Darryl Collins wrote:

> Hi Stuart,
> 
> Yes, I agree.  Didn't post the code last night (my time) as I didn't
> have it handy.  Here we go  I have changed all of the fields types
> in the database table to strings for the moment just to try and make
> this work.
> 
> My suspects are one of the following
> 
> 1: The CSV file will only ever have a single line. I am currently
> using a 'foreach' command which loops thru the file. Maybe I am
> populating a 2nd set of blank attributes?  This doesn't seem likely as
> only a the single line is written into the test text file.
> 
> 2: When powershell runs more than once it doesn't seem to close down
> Access properly and I am left with a acdb lock file.  Not sure why but
> it might be locking up the table and preventing further attempts to
> write to it.
> 
> 3: I have done something dumb with the SQL string that does the insert
> (this is possible, although it looks ok from what I can see).  I did
> try it once with hard coded values and it wrote it into the table, but
> only once - thus my thoughts about the table being locked by earlier
> attempts.
> 
> I like this powershell approach as it runs external with VBA having to
> have an interval timer or looping to constantly check the folder - it
> is fast and work seamlessly in the background, but I am bumbling
> around with the code as I have little experience with doing more
> complex things in Powershell.
> 
> Happy if anyone has any advice. 
> 
> Thanks to John C for your email as well.  Always appreciate your
> insights given your experience and expertise with all things Access.
> 
> Cheers
> Darryl
> 
> 
> 
> ###============== Start of code
> ###===============================================
> ### SET FOLDER TO WATCH + FILES TO WATCH + SUBFOLDERS YES/NO
>     $watcher = New-Object System.IO.FileSystemWatcher
>     $watcher.Path = "V:\ReportingQueue\"
>     $watcher.Filter = "*.*"
>     $watcher.IncludeSubdirectories = $true
>     $watcher.EnableRaisingEvents = $true  
> 
> 
> 
> 
> ### DEFINE ACTIONS AFTER AN EVENT IS DETECTED
>     $action = { $path = $Event.SourceEventArgs.FullPath
>                 $changeType = $Event.SourceEventArgs.ChangeType
>                 ##$logline = "$(Get-Date), $changeType, $Path"
>                 ##Add-content "V:\ReportingLog\ReportingLog.txt"
>                 -value $logline
>                ### could write to text log file fine in testing
> 
> 
> 
>                 $connectstring = "Provider=Microsoft.ACE.OLEDB.12.0;
>                 Data Source=V:\ReportingLog\ReportingLogDB.accdb"
>                 $conn = New-Object
>                 System.Data.OleDb.OleDbConnection($connectstring)
>                 $conn.Open() ### $File = Import-Csv
>                 "V:\ReportingQueue\IMG5029.csv" $File = Import-Csv
>                 -value "$path"
> 
>                 foreach ($item in $File) 
>                 { 
>                 $Chrono =  $item.Chrono
>                 $Account = $item.Account
>                 $ProberRun = $item.ProberRun
>                 $CreateEveID = $item.CreateEveID
> 
> 
>                 Add-content "V:\ReportingLog\ReportingLog.txt" -value
>                 "$(Get-Date)" Add-content
>                 "V:\ReportingLog\ReportingLog.txt" -value "$path"
>                 Add-content "V:\ReportingLog\ReportingLog.txt" -value
>                 "$Chrono" Add-content
>                 "V:\ReportingLog\ReportingLog.txt" -value
>                 "$Account" Add-content
>                 "V:\ReportingLog\ReportingLog.txt" -value
>                 "$ProberRun" Add-content
>                 "V:\ReportingLog\ReportingLog.txt" -value
>                 "$CreateEveID" ### Testing that the csv file is
>                 being read correctly - write results to txt file
> 
> 
>                 $cmd = $conn.CreateCommand()
>                 $cmd.CommandText="INSERT INTO
>                 tblReportingQueue(Chrono,Account,ProberRun,CreateEveID
>                 )
>                 VALUES('$Chrono','$Account','$ProberRun','$CreateEveID
>                 ')" $cmd.ExecuteNonQuery() } $conn.Close()
>               }
> 
> 
> 
> ### DECIDE WHICH EVENTS SHOULD BE WATCHED (only need add in this case)
>     Register-ObjectEvent $watcher "Created" -Action $action
> ### Register-ObjectEvent $watcher "Changed" -Action $action
> ### Register-ObjectEvent $watcher "Deleted" -Action $action
> ### Register-ObjectEvent $watcher "Renamed" -Action $action
>     while ($true) {sleep 5}
> 
> ###============== End  of code
> ###===============================================
> 
> 
> 
> 
> 
> 
> 
> 
> 
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of
> Stuart McLachlan Sent: Thursday, 16 August 2018 9:54 PM To: Access
> Developers discussion and problem solving
> <accessd at databaseadvisors.com> Subject: Re: [AccessD] Powershell...
> 
> If the ANPK is going up, I'd suspect a problem with your insert rather
> than with Powershell. Is it  an SQL statement?
> 
> How about posting the code.
> 
> 
> On 16 Aug 2018 at 10:08, Darryl Collins wrote:
> 
> > Hi Folks,
> > 
> > I am trying to use powershell to write from a CSV to an Access Table
> > when a file is added to a folder.
> > 
> > I have the powershell 'trigger' part working and I can get
> > powershell to write to a text file when triggered.
> > 
> > I can open and extract that data in the CSV I need (that is the file
> > that saved to the said folder which triggers powershell into
> > action).
> > 
> > But I want to write the single line from the csv file to an access
> > table.  I can create the connection in powershell and it opens the
> > table. It even seems to attempt to write it into the table.  No data
> > is being written but the auto key is creeping up after every
> > attempt, which suggests something is going on.
> > 
> > Is anyone skilled at powershell code, or can point me to a list /
> > group that might be able to help.
> > 
> > If it helps, I can post the code, but that might not be for a few
> > hours.
> > 
> > Cheers
> > Darryl.
> > 
> > 
> > 
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com