Some tips for me and other

Powershell + Access to MS SQL

import-module SqlServer

# Ping Server
if (Test-connection Server -quiet) {
	try {
		$connString="Provider=Microsoft.ACE.OLEDB.16.0;Data Source=E:\Access.accdb"
		$conn=New-Object System.Data.OleDb.OleDbConnection($connString)

		#Create a command object and execute its reader.
		$cmd = $conn.CreateCommand()
		$cmd.CommandText = 'SELECT * FROM Computers'
		$rdr = $cmd.ExecuteReader()

		#Load the data table object
		$dt = New-Object System.Data.DataTable
	catch {Send-MailMessage -From -to -Subject "Access2SQL:ACCESS:ERROR" -Body $_ -SmtpServer Mailserver}
	try {
		#Clear the Table
		Invoke-Sqlcmd -Query "TRUNCATE TABLE AccessDB.dbo.Computers" -ServerInstance "Server" -Database "AccessDB"
		#Fill the Table
		$dt | Write-SqlTableData -ServerInstance Server -Database AccessDB -SchemaName dbo -TableName Computers -Force
	catch {Send-MailMessage -From -to -Subject "Access2SQL:SQL:ERROR" -Body $_ -SmtpServer Mailserver}
else {Send-MailMessage -From -to -Subject "Access2SQL:PING:ERROR" -Body $_ -SmtpServer Mailserver}

Comments are currently closed.