inserting 786k rowsx 32 cols into Access 2007 from Excel 2007 took ca.
45 mins on my AMD Athlon 64 X2 Dual Core Processor 3800+ 2.01 GHZ, 1GB
RAM. and it never crashed
copying the same amount of data from Access 2007 to Excel 2007 (via
CopyFromRecordset) method took less than 1 minute
you may copy the data from Access to Excel every time you need to
update yr charts
never worked with such vast amounts of data in Excel but maybe this
method is worth trying?
1. in both macros change
path "C:\Documents and Settings\Jarek\Pulpit\" to your path
file name "Excel_Access.mdb" to your file name
table name "TranStany" to your table name
as well as 31 to 40 in Sub sciagnij_dane_z_ACCESSA()
(and anything else I might have forgot of)
accordingly for the macros to work
used this macro to insert data into Access:
Sub dopisz_do_ACCESSa()
Dim i As Integer
Dim licz As Integer
Dim rekord As String
Dim wartosci_pol_rekordu As String
Dim rsData As ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim sConnect As String
Dim sPath As String
Dim sSQL As String
sPath = "C:\Documents and Settings\Jarek\Pulpit\"
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
Set rsData = New ADODB.Recordset
Set cnAccess = New ADODB.Connection
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & "Excel_Access.mdb;"
cnAccess.ConnectionString = sConnect
cnAccess.Open
sSQL = "DELETE * FROM TranStany"
'Use the Connection object to execute the SQL statement.
cnAccess.Execute sSQL, , adCmdText + adExecuteNoRecords
ostatnia_data = 1
ostatni_rekord = 1000000
Range(Cells(ostatnia_data, 1), Cells(ostatni_rekord, 1)).Select
For Each cell In Selection
rekord = vbNullString
licz = 0
If Len(cell.Value) > 0 Then
For m = 0 To 31
'cell.Offset(0, m).Activate
If m > 0 Then
If cell.Offset(0, m) = 0 Then
rekord = rekord & "','" & 0
Else
rekord = rekord & "','" & cell.Offset(0, m)
End If
Else
rekord = cell.Value
End If
Next m
wartosci_pol_rekordu = " VALUES (" & "'" & rekord & "'" & ")"
sSQL = "INSERT INTO TranStany " &
wartosci_pol_rekordu
'Use the Connection object to execute the SQL
statement.
cnAccess.Execute sSQL, , adCmdText +
adExecuteNoRecords
Else
Exit For
End If
Next cell
cnAccess.Close
Set rsData = Nothing
Set cnAccess = Nothing
End Sub
I used this one to cope data from Access to Excel:
Sub sciagnij_dane_z_ACCESSA()
Dim rsData As ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim sConnect As String
Dim sPath As String
Dim sSQL As String
sPath = "C:\Documents and Settings\Jarek\Pulpit\"
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
Set rsData = New ADODB.Recordset
Set cnAccess = New ADODB.Connection
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & "Excel_Access.mdb;"
cnAccess.ConnectionString = sConnect
cnAccess.Open
sSQL = "SELECT * FROM TranStany"
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, _
adOpenForwardOnly, adLockReadOnly, adCmdText
If Not rsData.EOF Then
ActiveSheet.Range("A1:M100000").Rows.EntireRow.Delete
ActiveSheet.Range("A1").CopyFromRecordset rsData
Else
MsgBox "No data located.", vbCritical, "Error!"
End If
rsData.Close
cnAccess.Close
Set rsData = Nothing
Set cnAccess = Nothing
End Sub
HIH
