|
发表于 2013-3-30 22:08
|
显示全部楼层
本楼为最佳答案
- Sub 导出ACCESS()
- 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 & "\test.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 & _
- " (序号 text(5),姓名 text(10),生产组 text(20),身份证号码 text(18)," & _
- "年龄 TINYINT,合计 REAL,正常发放小计 REAL,正常发放基础养老金 REAL," & _
- "正常发放个人帐户 REAL,补发放小计 REAL,补发放基础养老金 REAL," & _
- "补发放个人帐户 REAL,账号 text(20),备注 text(100),社会保障号 text(20)," & _
- "电话号码 text(20),参保性质 text(10),补充说明 text(20),所在社区 text(20));"
- AdoCmd.Execute , , 1 'adCmdText
- Set AdoCmd = Nothing
- Set AdoxCat = Nothing
- Set AdoConn = Nothing
-
- End If
- StrConn = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source=" & AccessFile & ";"""
-
- Set AdoConn = CreateObject("ADODB.Connection")
-
- AdoConn.Open StrConn
- If AdoConn.State <> 1 Then MsgBox "数据库连接失败", vbCritical + vbOKOnly: Exit Sub
- lLastrow = Cells(Rows.Count, 1).End(xlUp).Row
- If lLastrow < 5 Then Exit Sub
- arr = Range("a5:s" & lLastrow)
-
- strSQL = "select * from " & Database
- Set AdoRst = CreateObject("ADODB.Recordset")
- AdoRst.Open strSQL, AdoConn, 2, 3 'adOpenDynamic, adLockOptimistic
- With AdoRst
- For i = LBound(arr) To UBound(arr)
- .AddNew
- For j = 1 To UBound(arr, 2)
- .Fields(j - 1).Value = arr(i, j)
- Next
- .Update
- Next
- End With
- AdoRst.Close
- Set AdoRst = Nothing
- Set AdoConn = Nothing
- MsgBox "数据改入成功" & vbNewLine & _
- AccessFile & vbNewLine, vbInformation + vbOKOnly
-
- Exit Sub
- Errcheck:
- MsgBox Err.Number & vbNewLine & _
- Err.Description
- End Sub
复制代码 |
评分
-
查看全部评分
|