[原创]把.mdb文件导出EXCEL修改后，重新导入原ACCESS中

nawong

Dim conn As New ADODB.Connection Dim rst As New ADODB.Recordset Sub ExportToExcel()'导出EXCEL conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Documents and Settings\DBBASE.MDB;" & _ "Jet OLEDB:Engine Type=4" Set rst = conn.Execute("Select * from TableB") Sheets.Add ActiveSheet.Name = "ExportToExcel" For Each Field In rst.Fields [a1].Offset(0, i) = Field.Name i = i + 1 Next Range("A2").CopyFromRecordset rst conn.Close Set conn = Nothing End Sub Sub ImportToAccess()'导入原ACCESS conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Documents and Settings\DBBASE.MDB;" & _ "Jet OLEDB:Engine Type=4" conn.Execute ("delete * from TableB") sqlString = "INSERT INTO [TableB] SELECT * FROM [Excel 8.0;DATABASE=" & _ ThisWorkbook.FullName & ";HDR=YES].[ExportToExcel$];" conn.Execute sqlString conn.Close Set conn = Nothing End Sub

fscoolboy

nawong兄怎么不上传一个示例呢？

nawong

以下是引用[I]fscoolboy[/I]在2006-5-17 10:19:29的发言：
nawong兄怎么不上传一个示例呢？

47RO77kD.rar (19.73 KB, 下载次数: 1107)

2006-5-17 10:25 上传

点击文件名下载附件

[此贴子已经被作者于2006-5-17 10:25:53编辑过]

qee用

做的很好!
谢谢楼主分享.

爱歌学习

这样方便多了，收藏...

爱歌学习

式中的 "Jet OLEDB:Engine Type=4" 好像不要也可以啊？

ldy888

要的就是它,感谢nowong.

请教:

如果数据库密码是:123

代码该怎么改?

zhenxzha

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\DBBASE.MDB;" & "Jet OLEDB:Engine Type=4", False, False, ";Pwd=123"

ldy888

以下是引用[I]zhenxzha[/I]在2006-5-24 13:11:19的发言：[BR]conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\DBBASE.MDB;" & "Jet OLEDB:Engine Type=4", False, False, ";Pwd=123"

谢谢!你帮了我大忙了.看来和EXCEL有很大不同.

ldy888

试了下不行,找到以下方法可用

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\DBBASE.MDB;" & _
"Jet OLEDB:Engine Type=4" & ";Jet OLEDB:Database Password=123"