Powershell + Access to MS SQL

17/11/2022 admin Uncategorized

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)
		$conn.Open()

		#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
		$dt.Load($rdr)
	}
	catch {Send-MailMessage -From admin@local.com -to admin@local.com -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 admin@local.com -to admin@local.com -Subject "Access2SQL:SQL:ERROR" -Body $_ -SmtpServer Mailserver}
	$conn.Close()
}
else {Send-MailMessage -From admin@local.com -to admin@local.com -Subject "Access2SQL:PING:ERROR" -Body $_ -SmtpServer Mailserver}

Powershell + CSS in the tables RDP + RDG + MFA + More time to login