Private Sub into_Click() '导入 Dim cn As ADODB.Connection Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\10.10.0.250\图形流向\sclsylb.mdb;Persist Security Info=False"
cn.Execute "INSERT INTO " + jitaihao.Combo1.Text + " SELECT * FROM [" + jitaihao.Combo1.Text + "] IN ""e:\backup.xls"" ""EXCEL 12.0;"""
'查询以上插入数据 Set rs = cn.Execute("SELECT * FROM " + jitaihao.Combo1.Text + "") While Not rs.EOF Debug.Print rs.Fields(0); rs.Fields(1) rs.MoveNext Wend MsgBox "导入成功", vbOKOnly, "提示" Set rs = Nothing Set cn = NothingEnd Sub
Private Sub into_Click() '导入 Dim cn As ADODB.Connection Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.jet.OLEDB.4.0;Data Source=\\10.10.0.250\图形流向\sclsylb.mdb;Persist Security Info=False"
cn.Execute "INSERT INTO " + jitaihao.Combo1.Text + " SELECT * FROM [" + jitaihao.Combo1.Text + "] IN ""e:\backup.xls"" ""EXCEL 5.0;"""
'查询以上插入数据 Set rs = cn.Execute("SELECT * FROM " + jitaihao.Combo1.Text + "") While Not rs.EOF Debug.Print rs.Fields(0); rs.Fields(1) rs.MoveNext Wend MsgBox "导入成功", vbOKOnly, "提示" Set rs = Nothing Set cn = NothingEnd Sub
看了你发的源码,说实话,你这vb代码写的,让我不敢恭维. 就像你main中那么多菜单,考虑一下分类,二级菜单 而且这么多菜单在写命令的时候,如果代码很相似就写一个传参的过程,调用之. 你说的问题关键点就是在于本地导入远程服务器的数据库,和远程服务器的数据库导出到本地. 还是说本地导入的话,需要将xls表上传到服务端 你的导入代码修改如下(下面代码的成功的前期是1,你的导入代码正确,因为我不会用你的方式导入,我只是给你增加了上传功能,导入只是修改了文件路径而已 2,e盘下有backup.xls 3,数据库连接成功,4\\10.10.0.250\图像流向\temp这个文件夹存在且完全共享)Private Sub into_Click() '导入Dim db As Database Dim rs As Recordset Dim i As Integer Dim fso As Object'''上传xls Set fso = CreateObject("scripting.filesystemobject") fpath = "e:\backup.xls" '这个fpath你可以用对话框来自己选择,为了方便我直接将If fpath <> "" And fso.fileexists(fpath) = True Then '检测文件是否存在 fso.copyfile fpath, "\\10.10.0.250\图形流向\temp\import.xls" '拷贝到图像流向下面的文件夹temp中并重命名为import.xls End If''''打开并导入 Set db = OpenDatabase(App.Path + "\temp\import.xls", True, False, "Excel 5.0") db.Execute (" insert into " + jitaihao.Combo1.Text + " in '" & App.Path & "\temp\import.xls" & "' Select * from " + jitaihao.Combo1.Text + "") MsgBox "导入成功", vbOKOnly, "提示"End Sub导出就简单多了.(我贴的是我自己程序导出代码,你借鉴一下) 为了让你看明白,我把连接数据库,以及查询记录集都给你写出来dim conn as adodb.connection Dim rs1 As New ADODB.Recordset dim sql as stringset conn=new adodb.connection if conn.state<>0 then conn.close conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\sclsylb.mdb"sql="SELECT * FROM QS800" 'QS800表你应该很熟悉 if rs1.state<>0 then rs1.close rs1.cursorlocation=aduserclient rs1.open sql,conn,1,3 '导出xls表 Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim xlQuery As Excel.QueryTable 'On Error GoTo OutPutErr Set xlBook = xlApp.Workbooks().Add Set xlSheet = xlBook.Worksheets("sheet1") Set xlQuery = xlSheet.QueryTables.Add(rs1, xlSheet.Range("a1 "))With xlQuery .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True End WithxlQuery.FieldNames = True xlQuery.Refresh cmdlg.Flags = 2 cmdlg.Filter = "EXCEL文档(*.xls)" cmdlg.ShowSaveIf cmdlg.FileName <> "" Then xlApp.DisplayAlerts = False xlBook.SaveAs FileName:=cmdlg.FileName If MsgBox("导出成功,是否打开查看?", vbOKCancel, "导出EXCEL") = vbOK Then xlApp.Workbooks().Open cmdlg.FileName xlApp.Visible = True Else xlApp.Quit End If End If If xlApp <> Null Then Set xlApp = Nothing set conn=nothing set rs1=nothing//end如果再出错误的话,我想可能你没按照我的要求去做,或者你的导入代码有误,解决不了的话,给我留言.
Private Sub into_Click() '导入'上传 Set fso = CreateObject("scripting.filesystemobject") fpath = "e:\backup.xls"If fpath <> "" And fso.fileexists(fpath) = True Then fso.copyfile fpath, "\\10.10.0.250\图形流向\import.xls" End If'导入 Dim db As Database Dim rs As Recordset Dim i As Integer Set db = OpenDatabase("\\10.10.0.250\图形流向\import.xls", True, False, "Excel 5.0")If MsgBox("是否导入", vbYesNo, "提示") = 6 Then db.Execute (" insert into " + jitaihao.Combo1.Text + " in '\\10.10.0.250\图形流向\sclsylb.mdb' Select * from [" + jitaihao.Combo1.Text + "$]") MsgBox "导入成功", vbOKOnly, "提示" Else MsgBox "取消导入", vbOKOnly, "提示" End If'释放资源 Set db = Nothing'删除临时文件 If fso.fileexists("\\10.10.0.250\图形流向\import.xls") = True Then fso.deletefile "\\10.10.0.250\图形流向\import.xls" End If End Sub
我做的是把excel数据导入SQL SERVER数据库 ,给你参考一下Private Sub Command1_Click() Dim strconn As String ' 定义Excel 连接字符串 Dim cn As ADODB.Connection ' 定义Excel 连接 Set cn = New ADODB.Connection ' 初始化commandialog1 的属性,选取Excel 文件,文 ' 件名保存在CommanDialog1.filename 中备用CommonDialog1.Filter = " 电子表格文件(.xls) |*.xls" CommonDialog1.DialogTitle = " 请选择要导入的文件" CommonDialog1.ShowOpen' 设置连接SQL 数据库的连接字符串 strtemp = " [odbc;Driver= {SQL Server} ;Server=(local);Database=Afws;UID=sa;PWD=sa]" ' 设置Excel 数据连接 strconn = " Provider =Microsoft.Jet.OLEDB.4.0;Data Source=" & CommonDialog1.FileName & " ; Extended Properties=Excel 8.0" cn.Open strconnstrSql = "insert into " & strtemp & ".hw_level1 select * from [sheet1$]" cn.Execute strSql, lngRecsAff, adExecuteNoRecordsMsgBox " 成功导入到SQL 数据库中!", vbExclamation + vbOKOnlycn.Close Set cn = NothingEnd Sub
可以是菜单形式 右键、快捷键都行
该精简就精简,还且要注意用词准确,
什么叫"貌似成功了",难道执行insert之后,还有"貌似"插入数据成功之说?还有就是lz说的为什么不用datagrid显示,就可以直接修改.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\10.10.0.250\图形流向\sclsylb.mdb;Persist Security Info=False"
cn.Execute "INSERT INTO " + jitaihao.Combo1.Text + " SELECT * FROM [" + jitaihao.Combo1.Text + "] IN ""e:\backup.xls"" ""EXCEL 12.0;"""
'查询以上插入数据
Set rs = cn.Execute("SELECT * FROM " + jitaihao.Combo1.Text + "")
While Not rs.EOF
Debug.Print rs.Fields(0); rs.Fields(1)
rs.MoveNext
Wend
MsgBox "导入成功", vbOKOnly, "提示"
Set rs = Nothing
Set cn = NothingEnd Sub
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.jet.OLEDB.4.0;Data Source=\\10.10.0.250\图形流向\sclsylb.mdb;Persist Security Info=False"
cn.Execute "INSERT INTO " + jitaihao.Combo1.Text + " SELECT * FROM [" + jitaihao.Combo1.Text + "] IN ""e:\backup.xls"" ""EXCEL 5.0;"""
'查询以上插入数据
Set rs = cn.Execute("SELECT * FROM " + jitaihao.Combo1.Text + "")
While Not rs.EOF
Debug.Print rs.Fields(0); rs.Fields(1)
rs.MoveNext
Wend
MsgBox "导入成功", vbOKOnly, "提示"
Set rs = Nothing
Set cn = NothingEnd Sub
拿本地这个例子来说吧,在导入时,程序会检测本地这个excel是否存在,若存在则执行,若不存在,也执行,但是会返回空记录(至于错误提示,有些动态网页导入导出会提示,vb提不提示我不太清楚).而服务器端也是这样进行操作的,他会检测服务器端相应的位置(如e:\backup.xls)有没有这个文件.所以你的问题要解决的话,需要做的就是第一步先将文件上传到服务器(这个当然要用程序写了.),第二步在服务器端执行sql语句.
至于vb上传文件,你可以到网上搜一下,资源很丰富的.有什么问题继续给我留言.
就像你main中那么多菜单,考虑一下分类,二级菜单
而且这么多菜单在写命令的时候,如果代码很相似就写一个传参的过程,调用之.
你说的问题关键点就是在于本地导入远程服务器的数据库,和远程服务器的数据库导出到本地.
还是说本地导入的话,需要将xls表上传到服务端
你的导入代码修改如下(下面代码的成功的前期是1,你的导入代码正确,因为我不会用你的方式导入,我只是给你增加了上传功能,导入只是修改了文件路径而已 2,e盘下有backup.xls 3,数据库连接成功,4\\10.10.0.250\图像流向\temp这个文件夹存在且完全共享)Private Sub into_Click() '导入Dim db As Database
Dim rs As Recordset
Dim i As Integer
Dim fso As Object'''上传xls
Set fso = CreateObject("scripting.filesystemobject")
fpath = "e:\backup.xls" '这个fpath你可以用对话框来自己选择,为了方便我直接将If fpath <> "" And fso.fileexists(fpath) = True Then '检测文件是否存在
fso.copyfile fpath, "\\10.10.0.250\图形流向\temp\import.xls" '拷贝到图像流向下面的文件夹temp中并重命名为import.xls
End If''''打开并导入
Set db = OpenDatabase(App.Path + "\temp\import.xls", True, False, "Excel 5.0")
db.Execute (" insert into " + jitaihao.Combo1.Text + " in '" & App.Path & "\temp\import.xls" & "' Select * from " + jitaihao.Combo1.Text + "")
MsgBox "导入成功", vbOKOnly, "提示"End Sub导出就简单多了.(我贴的是我自己程序导出代码,你借鉴一下)
为了让你看明白,我把连接数据库,以及查询记录集都给你写出来dim conn as adodb.connection
Dim rs1 As New ADODB.Recordset
dim sql as stringset conn=new adodb.connection
if conn.state<>0 then conn.close
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\sclsylb.mdb"sql="SELECT * FROM QS800" 'QS800表你应该很熟悉
if rs1.state<>0 then rs1.close
rs1.cursorlocation=aduserclient
rs1.open sql,conn,1,3
'导出xls表
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlQuery As Excel.QueryTable
'On Error GoTo OutPutErr
Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlBook.Worksheets("sheet1")
Set xlQuery = xlSheet.QueryTables.Add(rs1, xlSheet.Range("a1 "))With xlQuery
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End WithxlQuery.FieldNames = True
xlQuery.Refresh
cmdlg.Flags = 2
cmdlg.Filter = "EXCEL文档(*.xls)"
cmdlg.ShowSaveIf cmdlg.FileName <> "" Then
xlApp.DisplayAlerts = False
xlBook.SaveAs FileName:=cmdlg.FileName If MsgBox("导出成功,是否打开查看?", vbOKCancel, "导出EXCEL") = vbOK Then
xlApp.Workbooks().Open cmdlg.FileName
xlApp.Visible = True
Else
xlApp.Quit
End If
End If
If xlApp <> Null Then Set xlApp = Nothing
set conn=nothing
set rs1=nothing//end如果再出错误的话,我想可能你没按照我的要求去做,或者你的导入代码有误,解决不了的话,给我留言.
在
MsgBox "导入成功", vbOKOnly, "提示"之后加上
fso.deletefile "\\10.10.0.250\图形流向\temp\import.xls"//end
Set fso = CreateObject("scripting.filesystemobject")
fpath = "e:\backup.xls"If fpath <> "" And fso.fileexists(fpath) = True Then
fso.copyfile fpath, "\\10.10.0.250\图形流向\import.xls"
End If'导入
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = OpenDatabase("\\10.10.0.250\图形流向\import.xls", True, False, "Excel 5.0")If MsgBox("是否导入", vbYesNo, "提示") = 6 Then
db.Execute (" insert into " + jitaihao.Combo1.Text + " in '\\10.10.0.250\图形流向\sclsylb.mdb' Select * from [" + jitaihao.Combo1.Text + "$]")
MsgBox "导入成功", vbOKOnly, "提示"
Else
MsgBox "取消导入", vbOKOnly, "提示"
End If'释放资源
Set db = Nothing'删除临时文件
If fso.fileexists("\\10.10.0.250\图形流向\import.xls") = True Then
fso.deletefile "\\10.10.0.250\图形流向\import.xls"
End If
End Sub
Dim strconn As String ' 定义Excel 连接字符串
Dim cn As ADODB.Connection ' 定义Excel 连接
Set cn = New ADODB.Connection
' 初始化commandialog1 的属性,选取Excel 文件,文
' 件名保存在CommanDialog1.filename 中备用CommonDialog1.Filter = " 电子表格文件(.xls) |*.xls"
CommonDialog1.DialogTitle = " 请选择要导入的文件"
CommonDialog1.ShowOpen' 设置连接SQL 数据库的连接字符串
strtemp = " [odbc;Driver= {SQL Server} ;Server=(local);Database=Afws;UID=sa;PWD=sa]"
' 设置Excel 数据连接
strconn = " Provider =Microsoft.Jet.OLEDB.4.0;Data Source=" & CommonDialog1.FileName & " ; Extended Properties=Excel 8.0"
cn.Open strconnstrSql = "insert into " & strtemp & ".hw_level1 select * from [sheet1$]"
cn.Execute strSql, lngRecsAff, adExecuteNoRecordsMsgBox " 成功导入到SQL 数据库中!", vbExclamation + vbOKOnlycn.Close
Set cn = NothingEnd Sub