excel 导入到access 提示索引或主关键字不能为空 但是表中的主关键字列并不是空的

excel导入到access 提示索引或主关键字不能为空但是表中的主关键字列并不是空的，因为我的表中就只有一条记录，错误排查了好久才弄好，原来是因为我把excel当成一个数据库进行操作的时候，居然自动把末尾的空行也进行了导入操作，空行的索引列当然为空了，所以一直提示这个问题！

解决办法：从excel中select的时候加上where索引 is not null

附加将excel 导入到access的VB.net代码

（下面的代码写在module中，需要的地方再用以下语句进行调用： 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 With
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("硕士录取")) Then
strs = "sslq"
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("数据库中不存在要导入的表，是否新建一张表？")
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)
myCmd2.Transaction = myTrans
myCmd2.ExecuteNonQuery()
Next
Dim sql3 As String = "alter table " & tablename & " addconstraint PK_xuehao primary key (xuehao)"
Dim myCmd3 As New OleDb.OleDbCommand(sql3, con)
myCmd3.Transaction = myTrans
myCmd3.ExecuteNonQuery()

'MsgBox("下面进行导入工作……")
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("数据导入成功!",vbInformation)
myTrans.Commit()
Catch ex As Exception
MsgBox(ex.Message + "导入不成功！ ")
Try
myTrans.Rollback()
Catch ex2 As Exception
MsgBox(ex2.Message)
End Try
Finally
con.Close()
xlsSheet = Nothing
xlsBook = Nothing
xlsApp = Nothing

EndTry

Else
MsgBox("此表已经存在，是否追加？")
Try
'MsgBox("下面进行导入工作……")
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("数据导入成功!",vbInformation)
con.Close()
Catch ex As Exception
MsgBox(ex.Message + "导入不成功！ ")
End Try

End If

End Sub