Disclaimer: I'm very new into programming, so my coding skills are subpar at best.
My goal is to have my Visual Studio 2019 project (named Detailing Error Log) add a new record row into my Microsoft Access Database table (accdb file named Database1, table named Data Collection) based on which checkboxes are checked. I would only like rows to be added, not deleted, whenever the "Import" button is clicked. The Database will then be saved and the checkboxes in my Visual Studio project unchecked. The Database will be used to store that data until it is used by a coordinating visual studio program to count the occurrences of specific text within a specific month. From there it will be displayed in graphs.
I had successfully accomplished this using Excel, however when the file became too large it caused a great deal of lag; both whenever I ran the debugger and also when refreshing the graphs. I understand there will most likely be a noticeable loading time, but I would like it to be minimized.
My problem is that I am getting this error at my con.Open(): "System.InvalidOperationException: 'The 'Microsoft.ACE.OLEDB.12.0Data Source = S:\software\System\DPD & DEL (KPI)\Database1.accdb' provider is not registered on the local machine."
What does this error mean? This is also currently the only error thrown.
Here is my code for reference, thanks in advance for the help!
*NEATOL = "No Entry At Time Of Log"
Private Sub ConnectionPrep(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Dim dbProvider As String
Dim dbSource As String
Dim sql As String
Dim inc As Integer
Dim MaxRows As Integer
Dim con As New OleDb.OleDbConnection
dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0"
dbSource = "Data Source = S:\software\Melton System\DPD & DEL (KPI)\Database1.accdb; Persist Security Info = False"
con.ConnectionString = dbProvider & dbSource
con.Open()
End Sub
Private Sub InputInformation(sender As System.Object, e As System.EventArgs) Handles ImporttBUT.Click
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim cb As New OleDbCommandBuilder()
Dim dsnewrow As DataRow
dsnewrow = ds.Tables("Data Collection").NewRow()
dsnewrow.Item("M/Y OF LOG") = Me.MonthList2021.SelectedItem
dsnewrow.Item("TIME OF LOG") = DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss")
dsnewrow.Item("USER") = UserName
dsnewrow.Item("STOCK NUMBER") = Me.StockNumberTXTB.Text
If MissedPartCHKB.Checked = True Then
dsnewrow.Item("MISSED PART") = Me.MissedPartCHKB.Text
ElseIf MissedPartCHKB.Checked = False Then
dsnewrow.Item("MISSED PART") = "NEATOL"
End If
If NotInEpicorCHKB.Checked = True Then
dsnewrow.Item("NOT IN EPICOR") = Me.NotInEpicorCHKB.Text
ElseIf NotInEpicorCHKB.Checked = False Then
dsnewrow.Item("NOT IN EPICOR") = "NEATOL"
End If
If MissedBuyoutCHKB.Checked = True Then
dsnewrow.Item("MISSED BUYOUT") = Me.MissedBuyoutCHKB.Text
ElseIf MissedBuyoutCHKB.Checked = False Then
dsnewrow.Item("MISSED BUYOUT") = "NEATOL"
End If
If NonStockCHKB.Checked = True Then
dsnewrow.Item("MISSED NON STOCK ITEM") = Me.NonStockCHKB.Text
ElseIf NonStockCHKB.Checked = False Then
dsnewrow.Item("MISSED NON STOCK ITEM") = "NEATOL"
End If
If MissedSTKItemCHKB.Checked = True Then
dsnewrow.Item("MISSED STOCK ITEM") = Me.MissedSTKItemCHKB.Text
ElseIf MissedSTKItemCHKB.Checked = False Then
dsnewrow.Item("MISSED STOCK ITEM") = "NEATOL"
End If
If MissedAutomatedPartCHKB.Checked = True Then
dsnewrow.Item("MISSED AUTOMATED") = Me.MissedAutomatedPartCHKB.Text
ElseIf MissedAutomatedPartCHKB.Checked = False Then
dsnewrow.Item("MISSED AUTOMATED") = "NEATOL"
End If
If MissingPrintAfterQTYCHKB.Checked = True Then
dsnewrow.Item("MISSING PRINTS AFTER QUANTITY") = Me.MissingPrintAfterQTYCHKB.Text
ElseIf MissingPrintAfterQTYCHKB.Checked = False Then
dsnewrow.Item("MISSING PRINTS AFTER QUANTITY") = "NEATOL"
End If
If MissedPrintsNOTSentChadCHKB.Checked = True Then
dsnewrow.Item("MISSED PRINT NOT SENT TO CHAD") = Me.MissedPrintsNOTSentChadCHKB.Text
ElseIf MissedPrintsNOTSentChadCHKB.Checked = False Then
dsnewrow.Item("MISSED PRINT NOT SENT TO CHAD") = "NEATOL"
End If
If OtherCHKB.Checked = True Then
dsnewrow.Item("OTHER") = Me.OtherTXTB.Text
ElseIf OtherCHKB.Checked = False Then
dsnewrow.Item("OTHER") = "NEATOL"
End If
If AddedMissingDimCHKB.Checked = True Then
dsnewrow.Item("ADDED MISSING DIMENSION") = Me.AddedMissingDimCHKB.Text
ElseIf AddedMissingDimCHKB.Checked = False Then
dsnewrow.Item("ADDED MISSING DIMENSION") = "NEATOL"
End If
If FixedDimensionCHKB.Checked = True Then
dsnewrow.Item("FIXED DIMENSION") = Me.FixedDimensionCHKB.Text
ElseIf FixedDimensionCHKB.Checked = False Then
dsnewrow.Item("FIXED DIMENSION") = "NEATOL"
End If
ds.Tables("Counting").Rows.Add(dsnewrow)
da.Update(ds, "Counting")
MsgBox("Entry succesfully added to database.")
MissedPartCHKB.Checked = False
MissedAutomatedPartCHKB.Checked = False
NotInEpicorCHKB.Checked = False
NonStockCHKB.Checked = False
MissedSTKItemCHKB.Checked = False
MissedBuyoutCHKB.Checked = False
MissedPrintsNOTSentChadCHKB.Checked = False
MissingPrintAfterQTYCHKB.Checked = False
AddedMissingDimCHKB.Checked = False
FixedDimensionCHKB.Checked = False
OtherCHKB.Checked = False
OtherTXTB.Text = ""
End Sub
It might seem like a good idea to build your connection string that way but it's not. If you want to break it up then do so properly:
Dim builder As New OleDbConnectionStringBuilder
builder.Provider = "Microsoft.ACE.OLEDB.12.0"
builder.DataSource = "S:\software\Melton System\DPD & DEL (KPI)\Database1.accdb"
builder.PersistSecurityInfo = False
Using connection As New OleDbConnection(builder.ConnectionString)
'...
End Using
Doing it properly, you can't forget bits and pieces because there are no bits and pieces. You just supply the values. You also won't end up putting the PersistSecurityInfo value into a variable named dbSource when it's nothing to do with the data source. Pretending to separate your connection string components but not actually doing it properly is worse than not doing it at all.
You can also simplify the code above somewhat like so:
Dim builder As New OleDbConnectionStringBuilder
With builder
.Provider = "Microsoft.ACE.OLEDB.12.0"
.DataSource = "S:\software\Melton System\DPD & DEL (KPI)\Database1.accdb"
.PersistSecurityInfo = False
End With
or, taking it a step further:
Dim builder As New OleDbConnectionStringBuilder With {.Provider = "Microsoft.ACE.OLEDB.12.0",
.DataSource = "S:\software\Melton System\DPD & DEL (KPI)\Database1.accdb",
.PersistSecurityInfo = False}