[AccessD] Powershell...

[Darryl Collins]

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