Option Explicit请各位大侠帮我看看,我想把excel表导入到access数据库里面,但是这样链接了还是不行,谢谢!!!希望把改后的结果也发给我哈!!!
Private Sub cmdimport_Click()
Dim conn As ADODB.Connection
Dim conn_db As ADODB.Connection
Dim rs_db As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim strconn As String
Dim strconn_db As String
Dim sqlstr As String
Dim sqlstr_db As String
Dim i As Integer
'打开数据库里面的表1
Set conn_db = New ADODB.Connection
strconn_db = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\导入excel到数据库\db1.mdb;" & _
"Persist Security Info=False"
sqlstr_db = "select * from 表1"
conn_db.CursorLocation = adUseClient
conn_db.Open strconn_db
Set rs_db = New ADODB.Recordset
rs_db.Open sqlstr_db, conn_db
MsgBox "连接成功"
With CommonDialog1
.FileName = "*.xls"
.Filter = "(Excel)*.xls|*.xls"
.ShowOpen
End With
'与要导入的excel表建立连接
Set conn = New ADODB.Connection
strconn = "provider=microsoft.jet.oledb.4.0;" & _
"data source=" & CommonDialog1.FileName & _
"Extended Properties='Excel 8.0;HDR=Yes'"
conn.Open strconn
rs.Open "select * from [sheet1$]", conn
For i = 0 To rs.RecordCount
rs_db.AddNew
rs_db!姓名 = rs_db(0)
rs_db!年龄 = rs_db(1)
rs_db!性别 = rs_db(2)
rs_db.Update
Next i
MsgBox "导入成功" & "共有" & rs.RecordCount & "条记录!"
End Sub
Private Sub cmdimport_Click()
Dim conn As ADODB.Connection
Dim conn_db As ADODB.Connection
Dim rs_db As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim strconn As String
Dim strconn_db As String
Dim sqlstr As String
Dim sqlstr_db As String
Dim i As Integer
'打开数据库里面的表1
Set conn_db = New ADODB.Connection
strconn_db = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\导入excel到数据库\db1.mdb;" & _
"Persist Security Info=False"
sqlstr_db = "select * from 表1"
conn_db.CursorLocation = adUseClient
conn_db.Open strconn_db
Set rs_db = New ADODB.Recordset
rs_db.Open sqlstr_db, conn_db
MsgBox "连接成功"
With CommonDialog1
.FileName = "*.xls"
.Filter = "(Excel)*.xls|*.xls"
.ShowOpen
End With
'与要导入的excel表建立连接
Set conn = New ADODB.Connection
strconn = "provider=microsoft.jet.oledb.4.0;" & _
"data source=" & CommonDialog1.FileName & _
"Extended Properties='Excel 8.0;HDR=Yes'"
conn.Open strconn
rs.Open "select * from [sheet1$]", conn
For i = 0 To rs.RecordCount
rs_db.AddNew
rs_db!姓名 = rs_db(0)
rs_db!年龄 = rs_db(1)
rs_db!性别 = rs_db(2)
rs_db.Update
Next i
MsgBox "导入成功" & "共有" & rs.RecordCount & "条记录!"
End Sub
Private Sub Command1_Click() On Error GoTo ErrHandler Dim strsql As String
Dim strsql_db As String
Dim j As Integer
If Text1.Text = "" Then
MsgBox "请输入导入年份!", 48, "信息"
Exit Sub
End If
strsql = "select * from jhtz_table "
Set rs = ExecuteSQL(strsql, msgtext)
With CommonDialog1
.DialogTitle = "从Excel导出" & Text1.Text & "数据"
.FileName = "*.xls"
.Filter = "(Excel)*.xls|*.xls"
.CancelError = True
.ShowOpen
End With
strcnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=" & CommonDialog1.FileName & ";Extended Properties='Excel 8.0;HDR=Yes'"
Set cnn = New ADODB.Connection
cnn.Open strcnn
strsql_db = "select * from [sheet1$]"
cnn.Execute strsql_db
Set rs_db = New ADODB.Recordset
rs_db.Open strsql_db, cnn, adOpenKeyset, adLockOptimistic
For j = 1 To rs_db.RecordCount
rs.AddNew
rs.Fields("jh_dwbm") = rs_db.Fields(0)
rs.Fields("jh_xh") = rs_db.Fields(1)
rs.Fields("jh_sbmc") = rs_db.Fields(2)
rs.Fields("jh_gg") = rs_db.Fields(3)
rs.Fields("jh_dw") = rs_db.Fields(4)
rs.Update
rs_db.MoveNext
Next j
MsgBox "导入成功,共有" & rs_db.RecordCount & "条记录!", 48, "信息"
Exit Sub
ErrHandler:
'用户按了“取消”按钮
MsgBox "用户取消从Excel导出数据操作!", 48, "提示"
Exit Sub
End Sub
----------------
'与要导入的excel表建立连接
........
For i = 0 To rs.RecordCount
rs_db.AddNew
rs_db!姓名 = rs_db(0)
rs_db!年龄 = rs_db(1)
rs_db!性别 = rs_db(2)
rs_db.Update
Next i
........
是不是应该这样:
....
For i = 0 To rs.RecordCount
rs_db.AddNew
rs_db!姓名 = rs(0)
rs_db!年龄 = rs(1)
rs_db!性别 = rs(2)
rs_db.Update
Next i
....