method 1. Use CopyFromRecordset (for Access, SQL)
First: 49 Second: 45 Third: 43 Fourth: 43 Fifth: 42
Method 2: Use queryTable (for Access, SQL)
First: 10 second: 6 Third time: 3 Fourth: 4 Fifth: 4
Method 3: Using BCP (for SQL)
From the command line direct running time of 701 milliseconds, returning from VB is 0 test code as follows:
method 1:
Option expedition
PRIVATE SUB Command1_Click () DIM T1 AS DATE T1 = NOW () DIM STRCONN AS STRING STRCONN = "provider = SQLOLEDB.1; Integrated Security = SSPI; PERSIST
Security Info = False; Initial Catalog = mlog; Data Source = SZ09 "Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = CreateObject (" ADODB.Connection ") cn.Open strConn cn.CursorLocation = adUseServer Set rs = cn.Execute ( "table1",, adCmdTable) Dim oExcel As Excel.Application Dim oBook As Excel.Workbook Dim oSheet As Object Set oExcel = CreateObject ( "Excel.Application") Set oBook = oExcel.Workbooks.Add Set oSheet = oBook .Worksheets (1) Osheet.Range ("a1"). CopyFromRecordset RS OBOOK.SAVEAS "D: /1.XLS" OEXCEL.QUIT SET OSHEET = Nothing set OBOOK = Nothing set = nothing = nothing = nothing = nothing = Nothing = nothing = nothing = nothing = nothing = Nothing = Noth .Close set cn = Nothing msgbox (DateDiff ("S", T1, NOW ())) End Sub
Method 2:
Option expedition
Private Sub Command1_Click () Dim t1 As Date t1 = Now () 'Create a new workbook in Excel Dim oExcel As Object Dim oBook As Object Dim oSheet As Object Set oExcel = CreateObject ( "Excel.Application") Set oBook = oExcel.Workbooks .Add Set oSheet = oBook.Worksheets (1) Dim strConn As String strConn = "Provider = SQLOLEDB.1; Integrated Security = SSPI; PersistSecurity Info = False; Initial Catalog = mlog; Data Source = SZ09" 'Create the QueryTable Dim oQryTable As Object Set oQryTable = oSheet.QueryTables.Add (_ "OLEDB;" & strConn & ";", oSheet.Range ( "A1"), "Select * from table1") oQryTable.RefreshStyle = xlInsertEntireRows oQryTable.Refresh False 'Save The Workbook and Quit Excel Obook.saveas "D: /1.xls" OEXCEL.QUIT SET OSHEET = NOTHING SET OBOOK = NOTHING SET OEXCEL = Nothing MsgBox (Datediff ("S", T1, NOW ()) End Sub
Method 3:
PRIVATE SUB Command1_Click () DIM T1 AS DATE T1 = NOW () DIM SCMD AS STRING SCMD = "BCP MLOG..TABLE1 OUT D: /1.CSV -W -T, -R / N -S SZ09 -P
Kenfil "DIM WSH As Object Set WSH = CreateObject (" wscript.shell ") WSH.Run SCMD, True Msgbox (Datediff (" S ", T1, NOW ()) End Sub
Note: CVS itself is a file that can be used by Excel (you can open this article directly in Excel)
Parts), if you want to convert this file into XLS files, it is very simple:
Dim oExcel As Object Dim oBook As Object Dim oSheet As Object Set oExcel = CreateObject ( "Excel.Application") Set oBook = oExcel.Workbooks.Open ( "d: /1.csv") 'Save as Excel workbook and Quit Excel oBook . Saveas "D: /1.xls", XLWORKBOOKNORMAL OEXCEL.QUIT
