Excel to Access Table (Insert/Delete)

 

Import Excel to Access Table

#Excel #Table To MS #Access Table #SQL #Insert #Office #VBA

Public Sub PS_ExcelTableToAccess(ByVal DatabaseName As String, ByVal XlsTableName As String)

  On Error GoTo ErrorHandler

  Dim cn As Object

  Dim dbPath As String

  Dim ExcelTableName As String

  Set cn = CreateObject("ADODB.Connection")

  'dbPath = Application.ActiveWorkbook.Path & "\PS_AccessExcel.accdb"

  'dbPath = "D:\PS_AccessExcel.accdb"

  

  dbPath = DatabaseName

  dbWb = Application.ActiveWorkbook.FullName

  dbWs = Application.ActiveSheet.Name

  ExcelTableName = XlsTableName

  scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

  dsh = "[" & Application.ActiveSheet.Name & "$]" & ExcelTableName  '

  cn.Open scn

  ssql = "INSERT INTO TablePS ( Id, , State, Thematic, TotalBudget, PriorYearExpenditure, Budget2020, TotalExpenditure2020, ReportYear, ReportPeriod ) "

  ssql = ssql & "Select Id, , State, Thematic, TotalBudget, PriorYearExpenditure, Budget2020, TotalExpenditure2020, ReportYear, ReportPeriod FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh

  cn.Execute ssql

  cn.Close

  Set cn = Nothing

'MsgBox "Insert-Done"

Exit Sub

ErrorHandler:

MsgBox "Err No:" & Err.Number & " Cannot Add to Temp."

Resume

End Sub

Public Sub PS_Delete(ByVal DatabaseName As String, ByVal ReportPeriod As String, ByVal ReportYear As LongPtr)

  Dim cn As Object

  Dim dbPath As String

  Dim ssql As String

  Dim tGrantID As String

  Dim rptYear As LongPtr

  Dim rptPeriod As String

  

  Set cn = CreateObject("ADODB.Connection")

     dbPath = DatabaseName

  

  rptYear = ReportYear

  rptPeriod = ReportPeriod

  

  scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

  cn.Open scn

  ssql = "DELETE * FROM FR_Report WHERE [ReportPeriod]='" & rptPeriod & "' AND [ReportYear]=" & rptYear

    cn.Execute ssql

  cn.Close

  Set cn = Nothing

End Sub

Share This

Facebook

Twitter

Google+

LinkedIn

Related Posts