当前位置：首页 > news >正文

VBA 数据库同一表的当前行与其他行的主键重复判断实现方案

news 2025/3/9 19:27:53

目的，判断是否主键重复，不重复则登录新数据，重复则不登录。

定义类型：
DataRecord
tableName 表名
rowNumber 行号
columnName 列名
data 数据

想要实现的代码逻辑如下：
模拟数据库的登录过程。假设现在登录了5条数据，要登录第6条数据。
规则1，判断其他5行数据的所有列，与第6行已经录的所有列的数据一一比较，如果存在不一致的，说明主键不重复，可以登录新的数据。
规则2，如果不存在一致的，则判断我们这次要登录的这个列的其他行的数据，是否存在不一致的，如果不一致则主键不重复，可以登录新的数据。
规则3，如果仍然没有不一致的数据，说明主键重复，不能登录这条数据。

代码结构如下：
私有DataRecordType类
模块1
在这里插入图片描述

DataRecordType类代码

' DataRecordType 类模块
Private pTableName As String
Private pRowNumber As Long
Private pColumnName As String
Private pData As String' 定义公共属性以访问私有变量
Public Property Get tableName() As StringtableName = pTableName
End Property
Public Property Let tableName(value As String)pTableName = value
End PropertyPublic Property Get rowNumber() As LongrowNumber = pRowNumber
End Property
Public Property Let rowNumber(value As Long)pRowNumber = value
End PropertyPublic Property Get columnName() As StringcolumnName = pColumnName
End Property
Public Property Let columnName(value As String)pColumnName = value
End PropertyPublic Property Get data() As Stringdata = pData
End Property
Public Property Let data(value As String)pData = value
End Property

模块1代码

Sub AddData(dataCollection As Collection, tableName As String, rowNumber As Long, columnName As String, data As String)Dim record As DataRecordTypeSet record = New DataRecordType  ' 关键：实例化对象record.tableName = tableNamerecord.rowNumber = rowNumberrecord.columnName = columnNamerecord.data = datadataCollection.Add record
End SubFunction InsertData(dataCollection As Collection, tableName As String, rowNumber As Long, columnName As String, data As String) As BooleanDim i As LongDim sameTableRowData As StringDim otherTableRowData As StringDim record1 As DataRecordTypeDim record2 As DataRecordTypeDim noexist As BooleanFor i = 1 To dataCollection.CountSet record1 = dataCollection(i)  ' 关键：从集合中获取对象If record1.tableName = tableName And record1.rowNumber = rowNumber ThenFor j = 1 To dataCollection.CountSet record2 = dataCollection(j)If record2.tableName = record1.tableName And record2.columnName = record1.columnName And record2.rowNumber <> record1.rowNumber ThenIf record2.data <> record1.data ThenCall AddData(dataCollection, tableName, rowNumber, columnName, data)InsertData = TrueExit FunctionEnd IfExit ForEnd IfNext jEnd IfNext iFor j = 1 To dataCollection.CountSet record2 = dataCollection(j)If record2.tableName = tableName And record2.columnName = columnName And record2.rowNumber <> rowNumber ThenIf record2.data <> data ThenCall AddData(dataCollection, tableName, rowNumber, columnName, data)InsertData = TrueExit FunctionEnd IfExit ForEnd IfNext jInsertData = False
End FunctionSub TestInsertData()Dim dataCollection As CollectionSet dataCollection = New Collection' 添加前 5 条数据Call AddData(dataCollection, "Table1", 1, "Column1", "Data1")Call AddData(dataCollection, "Table1", 1, "Column2", "Data2")Call AddData(dataCollection, "Table1", 1, "Column3", "Data3")Call AddData(dataCollection, "Table1", 2, "Column1", "Data1")Call AddData(dataCollection, "Table1", 2, "Column3", "Data3")' 插入第 6 条数据并检查结果Dim result As Booleanresult = InsertData(dataCollection, "Table1", 2, "Column2", "Data2")If result ThenMsgBox "第 6 条数据已插入。"ElseMsgBox "第 6 条数据未插入（与现有数据重复）。"End If
End Sub

运行TestInsertData()
在这里插入图片描述