Windows PowerShell - How to - Querying Microsoft SQL Server

[@drogokhal3058]

Hello, very nice presentation , just what I'm looking for (in few minutes basics and most important things). Kind Regards,

[@BikashBiswalCHHUAPUA]

Nice tutorials. Could you please mention the powershell command lines in the description field of you tube video.

[@arulchelvan3070]

Hi could you please put in the commands in the description as the commands you use are truncated and are hard to see

[@UmaYasoda]

How can I connect MS Access database using Power Shell

[@heikohorn]

Here are two ways to connect to an Access Database: #Using ADODB.Connection $intOpenStatic=3 $intLockOptimistic=3 $strDb="C:\temp\Database1.accdb" $objConnection=New-Object -ComObject ADODB.Connection #Connection for Access 2007 and earlier $objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source=$strDb") #Connection for Access 2010 - 2016 $objConnection.Open("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = $strDb") $objRecordSet=New-Object -ComObject ADODB.Recordset $strQuery="Select Field1 from Test" $objRecordSet.Open($strQuery, $objConnection, $intOpenStatic, $intLockOptimistic) $objRecordset.GetRows() $objRecordset.Close() $objConnection.Close() #Using System.Data.SqlClient.SqlConnection $strDb="C:\temp\Database1.accdb" $strQuery="Select * from Test" $objConnection=New-Object System.Data.OleDb.OleDbConnection $objConnection.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$strDb" $objConnection.Open() $objCommand=New-Object System.Data.OleDb.OleDbCommand $objCommand.Connection=$objConnection $objCommand.CommandText=$strQuery $objCommand.ExecuteNonQuery() $reader=$objCommand.ExecuteReader() while ($reader.read()) { $reader.GetValue(0)#get the first column } $reader.close() $objConnection.close()

[@UmaYasoda]

Hi Heiko, Thank you for your reply. I tried for first approach, it's throwing an error as " New-Object-ComObject :The term 'New-Object-ComObject' is not recognized as the name of a cmdlet, function, script file or operable program. I am working on my current assignment as "to send the CSV file data to MS Access 2003 using Power Shell". Please help on this.

[@UmaYasoda]

Hi Heiko, I am trying with the below code to insert data from csv to ms access 2003 using Power Shell conn1.ps1:- $connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\db1.mdb" $conn=New-Object System.Data.Oledb.OledbConnection($connectstring) $conn.Open() $Users=Import-Csv -Delimiter "," -Path "C:\Users\users.csv" foreach ($User in $Users) { $first=$User.first $last=$User.last $cmd=$conn.CreateCommand() $cmd.CommandText="INSERT INTO Table1('$first','$last')" $cmd.ExecutionNonQuery() } $conn.close() ============================== After executing the above script getting the below error:- Exception calling "Open" with "0" arguments(s) :"The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. +At line:1 char:1 +$conn.open() +------------------- + CategoryInfo :NotSpecified: (:) [], MethodInvocationException +FullQualifiedErrorid:InvlidOperationException ===================================== Please suggest on this how to resolve.

[@heikohorn]

+uma d Is Office 2003 installed on the Computer you are running PowerShell on?

[@UmaYasoda]

Heiko Horn yes it's installed