|
本帖最后由 hwc2ycy 于 2013-5-17 09:17 编辑
qinhuan66 发表于 2013-5-17 09:02
谢谢老师!我试做了几条都不成功能否写条代码参考?谢谢 - Function getRecordCount() As Long
- Dim AccessFile As String, Database As String
- Dim StrConn As String, strSql As String
- Dim AdoxCat As Object
- Dim AdoCmd As Object
- Dim AdoConn As Object
- Dim AdoRst As Object
- On Error GoTo Errcheck
-
- AccessFile = ThisWorkbook.Path & "\data.mdb"
- Database = "data"
-
- 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 & _
- " (年份 INTEGER,录入时间 datetime ,序号 text(3),定点医疗机构名称 text(50),医保卡号 text(12),单位名称 text(50)," & _
- "姓名 text(8),性别 text(2),年龄 text(2),入院日期 datetime ,出院日期 datetime ,住院天数 INTEGER,出院诊断 text(50)," & _
- "本次住院医疗费总额 REAL,甲类药费 real,乙类药费 real,进口药费 real,自费药费 real,超出范围 real," & _
- "进口材料费 real,国产材料费 real,特殊检查费特殊治疗费 real,丙类项目 real,其它费用 real,起付段金额 real," & _
- "个人政策自付小计 real,自费药品及自费项目 real,实际结算自付 real,统筹基金支付 real,大病求助基金支付 real," & _
- "个人支付金额 real,本年住院次数 INTEGER,本年范围内费用累计 real,本年大病范围内费用累计 real)"
- AdoCmd.Execute , , 1 'adCmdText
- Set AdoCmd = Nothing
- Set AdoxCat = Nothing
- Set AdoConn = Nothing
- getRecordCount = 1
- Exit Function
- End If
- StrConn = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source='" & AccessFile & "';"
- 'StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & _
- AccessFile & "';"
- Set AdoConn = CreateObject("ADODB.Connection")
- With AdoConn
- .CursorLocation = 3
- .Mode = 3
- .CommandTimeout = 5
- .connectionTimeout = 5
- .Open StrConn
- End With
- strSql = "select * from " & Database
- Set AdoRst = AdoConn.Execute(strSql)
- getRecordCount = AdoRst.RecordCount + 1
- AdoConn.Close
- Set AdoConn = Nothing
- Exit Function
- Errcheck:
- MsgBox Err.Number & vbNewLine & _
- Err.Description
- End Function
复制代码 这是返回记录数的,测试交给你了。
worksheets(1).range("f2")=getRecordCount
这个代码放在工作簿OPEN事件里调用
|
评分
-
查看全部评分
|