|
发表于 2013-4-2 15:30
|
显示全部楼层
本楼为最佳答案
本帖最后由 hwc2ycy 于 2013-4-2 15:32 编辑
- Sub 按钮2_Click()
- Dim AccessFile As String, Database As String, SQL As String
- Dim StrConn$, strSql$
- Dim lLastrow&
- Dim arr, i&, j As Byte
- Dim AdoxCat As Object
- Dim AdoCmd As Object
- Dim AdoConn As Object
- Dim AdoRst As Object
- On Error GoTo Errcheck
- AccessFile = ThisWorkbook.Path & "\收款收剧.mdb"
- Database = "收款信息"
- If Dir(AccessFile) = "" Then
- '检测文件是否存在,不存在则创建数据库
- Set AdoxCat = CreateObject("adox.catalog")
- AdoxCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AccessFile
- Set AdoConn = AdoxCat.ActiveConnection
- Set AdoCmd = CreateObject("ADODB.Command")
- Set AdoCmd.ActiveConnection = AdoConn
- AdoCmd.CommandText = "CREATE TABLE " & Database & _
- " (委托日期 datetime ,缴款人 text(50),项目 text(50),金额 REAL," & _
- "成年人正常缴费 real,成年人正常缴费人数 INTEGER," & _
- "未成年人正常缴费 real,未成年人正常缴费人数 INTEGER," & _
- "备注 text(100),收款方式 text(10));"
- AdoCmd.Execute , , 1 'adCmdText
- Set AdoCmd = Nothing
- Set AdoxCat = Nothing
- Set AdoConn = Nothing
- End If
- If Len([h5]) = 0 Or Len([j9]) = 0 Or Len([j10]) = 0 Then
- MsgBox "E6,J9,J10数据不完整,请先填好数据再行操作"
- Exit Sub
- End If
- StrConn = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source=" & AccessFile & ";"""
- Set AdoConn = CreateObject("ADODB.Connection")
- With AdoConn
- .CursorLocation = 3
- .Mode = 3
- .CommandTimeout = 5
- .connectionTimeout = 5
- .Open StrConn
- If .State <> 1 Then MsgBox "数据库连接失败", vbCritical + vbOKOnly: Exit Sub
- End With
- strSql = "select * from " & Database & " where 委托日期=#" & [h5] & "# " & " and 缴款人 like '" & [e6] & "' and 金额=" & [j7]
- Set AdoRst = AdoConn.Execute(strSql)
-
- If AdoRst.RecordCount = 0 Then
- strSql = " insert into " & Database & " values(#" & _
- [h5] & "#,'" & [e6] & "','" & [i6] & "'," & _
- [j7] & "," & [j9] & "," & [k9] & "," & _
- [j10] & "," & [k10] & ",'" & [d14] & "','" & [k14] & "')"
- AdoConn.Execute strSql
- MsgBox "添加成功"
- [e6] = "": [j9] = "": [j10] = ""
- Else
- MsgBox "记录已经存在,不能重复添加"
- End If
- AdoConn.Close
- Set AdoConn = Nothing
- Exit Sub
- Errcheck:
- MsgBox Err.Number & vbNewLine & _
- Err.Description
- End Sub
复制代码 最好在几个 关键的单元格添加数据有效性。
刚刚没想过空值的问题。
|
评分
-
查看全部评分
|