When an excel file is imported to access, the index or primary keyword cannot be blank, but the primary keyword column in the table is not empty.

Import excel to access
It is prompted that the index or primary keyword cannot be blank, but the primary keyword column in the table is not empty, because there is only one record in my table and it takes a long time to troubleshoot the error, it turned out that when I used excel as a database for operations, I automatically imported the empty rows at the end. The empty row index column is of course empty, so I always asked this question!

Solution: select from excel
When adding where index is not null

Add the excel file to access VB.net.
Code

(The following code is written in the module and called using the following statement: CallExportExcelSheetToAccess (ComboBox_sheet.SelectedItem.ToString, selexcel, ComboBox_sheet.SelectedItem.ToString, TextBox_access.Text )):

PublicSub ExportExcelSheetToAccess (ByVal sSheetName As String, ByVal sExcelPath AsString, ByVal sAccessTable As String, ByVal sAccessDBPath As String)
Dim xlsApp As New Excel. Application
Dim xlsBook As Excel. Workbook
Dim xlsSheet As Excel. Worksheet
XlsBook = xlsApp. Workbooks. Open (sExcelPath)
XlsSheet = xlsBook. Worksheets (sSheetName)

Dimcols As Integer
Cols = xlsSheet. UsedRange. Columns. Count
Dim colName () As String
ReDim colName (cols)
'''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
Dim con1 As New ADODB. Connection
Dim rs As New ADODB. Recordset
Dim Sql1 As String = "select * from [" & sSheetName & "$]"
Con1.CursorLocation = ADODB. CursorLocationEnum. adUseClient
Con1.ConnectionString = "provider = Microsoft. Jet. OLEDB.4.0; Datasource =" & sExcelPath & "; Extended Properties = 'excel8. 0; HDR = Yes '"
Con1.Open ()
Rs. Open (Sql1, con1, ADODB. CursorTypeEnum. adOpenDynamic, ADODB. LockTypeEnum. adLockBatchOptimistic)
With rs
For i1 As Integer = 0 To cols-1
ColName (i1) = rs (i1). Name. ToString
'Msgbox (colName (i1 ))
Next
End
Rs. Close ()
Con1.Close ()

XlsBook. Close ()
XlsApp. Quit ()
XlsSheet = Nothing
XlsBook = Nothing
XlsApp = Nothing

Dim con As New OleDbConnection ("Provider = Microsoft. ACE. OLEDB.12.0; DataSource =" & sAccessDBPath &"")
Con. Open ()
Dim j As Integer
Dim tablename As String
Tablename = Form_face.ComboBox_daoru.SelectedItem.ToString.Substring (0, Form_face.ComboBox_daoru.SelectedItem.ToString.Length-1)
Dim strs As String
If (Form_daoru.ComboBox_ssbs.SelectedItem.ToString.Equals ("Master admission") Then
Strs = "ssscsi"
Else
Strs = "bslq"
End If
Tablename = tablename + strs
Dim adapter As OleDbDataAdapter
Dim ds As New DataSet
Adapter = New OleDbDataAdapter ("select * from MSysObjects Where name = '" & tablename & "' and type = 1 and flags = 0", con)
Adapter. Fill (ds)

If ds. Tables (0). Rows. Count = 0 Then
MsgBox ("the database does not contain the table to be imported. Do you want to create a new table? ")
Dim myTrans As OleDbTransaction
MyTrans = con. BeginTransaction
Try
Dim SQL As String = "CREATE TABLE" & tablename & "(" & colName (0) & "char (100 ))"
Dim myCmd As New OleDb. OleDbCommand (SQL, con)
MyCmd. Transaction = myTrans
MyCmd. ExecuteNonQuery ()
Dim sql2 As String
For j = 1 To cols-1
Sql2 = "alter table" & tablename & "add" & colName (j) & "char (100 )"
'Msgbox (colName (j ))
Dim myCmd2 As New OleDb. OleDbCommand (sql2, con)
Mydomain2.transaction = myTrans
Mydomain2.executenonquery ()
Next
Dim sql3 As String = "alter table" & tablename & "addconstraint PK_xuehao primary key (xuehao )"
Dim myCmd3 As New OleDb. OleDbCommand (sql3, con)
Myrule 3.transaction = myTrans
Mybench 3.executenonquery ()

'Msgbox ("next to import ...... ")
Dim Command As New OleDbCommand ("insert into" & tablename & "select * from [Excel 12.0; HDR = YES; DATABASE =" & sExcelPath & "]. ["& sSheetName &" $] where xuehao is not null ", con)
Command. Transaction = myTrans
Command. ExecuteNonQuery ()
MsgBox ("data import successful! ", VbInformation)
MyTrans. Commit ()
Catch ex As Exception
MsgBox (ex. Message + "Import failed! ")
Try
MyTrans. Rollback ()
Catch ex2 As Exception
MsgBox (ex2.Message)
End Try
Finally
Con. Close ()
XlsSheet = Nothing
XlsBook = Nothing
XlsApp = Nothing

EndTry

Else
MsgBox ("This table already exists. Do you want to append it? ")
Try
'Msgbox ("next to import ...... ")
Dim Command As New OleDbCommand ("insert into" & tablename & "select * from [Excel 12.0; HDR = YES; DATABASE =" & sExcelPath & "]. ["& sSheetName &" $] where xuehao is not null ", con)
Command. ExecuteNonQuery ()
MsgBox ("data import successful! ", VbInformation)
Con. Close ()
Catch ex As Exception
MsgBox (ex. Message + "Import failed! ")
End Try

End If

End Sub