Using Excel VBA to Export data to Ms.Access Table

[ahmed_one]

I am current using following code to export data from worksheet to Ms.Access database, the code is looping through each row and insert data to Ms.Access Table.

Public Sub TransData()

Application.ScreenUpdating = False
Application.EnableAnimations = False
Application.EnableEvents = False
Application.DisplayAlerts = False


ActiveWorkbook.Worksheets("Folio_Data_original").Activate


Call MakeConnection("fdMasterTemp")

For i = 1 To rcount - 1
rs.AddNew
rs.Fields("fdName") = Cells(i + 1, 1).Value
rs.Fields("fdDate") = Cells(i + 1, 2).Value
rs.Update

Next i


Call CloseConnection


Application.ScreenUpdating = True
Application.EnableAnimations = True
Application.EnableEvents = True
Application.DisplayAlerts = True


End Sub


Public Function MakeConnection(TableName As String) As Boolean
'*********Routine to establish connection with database

Dim DBFullName As String
Dim cs As String

DBFullName = Application.ActiveWorkbook.Path & "\FDData.mdb"

cs = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

Set cn = CreateObject("ADODB.Connection")

If Not (cn.State = adStateOpen) Then
cn.Open cs
End If

Set rs = CreateObject("ADODB.Recordset")

If Not (rs.State = adStateOpen) Then

rs.Open TableName, cn, adOpenKeyset, adLockOptimistic

End If

End Function




Public Function CloseConnection() As Boolean
'*********Routine to close connection with database

On Error Resume Next
If Not rs Is Nothing Then
rs.Close
End If


If Not cn Is Nothing Then
cn.Close

End If
CloseConnection = True
Exit Function


End Function

Above code works fine for few hundred lines of records, but apparently it will be more data to export, Like 25000 records, is it possible to export without looping through all records and just one SQL INSERT statement to bulk insert all data to Ms.Access Table in one go?

Any help will be much appreciated.

Thanks

Ahmed

 

[ahmed_one]

Dear jolielle,

As mentioned in Faaza's post, I've check the link, and found out that there is nothing special to do with regards to Named Ranges...Simply modify the following:
dsh = "[" & Application.ActiveSheet.Name & "$]" & "Data2"
(Assuming your Named Range called Data2)

Regards

Ahmed

 

[jolielle]

thank u both Fazza and Ahmed for such prompt and helpful reply!
Fazza since u mentioned in ur post in the link u gave about transferring data from arrays into access from excel, is it possible to directly use "insert" function to append an array of data in excel into an access table too? ie we do not go through a sheet or a named range, but since i already have the data stored in an array i would like to directly append it to access table. (rather than say dumping it onto a worksheet first, then import the worksheet to access). of course this array function will NOT have the headers but only data (assuming i already created a table in access with all the right headings and consistent data type for each column).

 

[Fazza]

thank u both Fazza and Ahmed for such prompt and helpful reply!
Fazza since u mentioned in ur post in the link u gave about transferring data from arrays into access from excel, is it possible to directly use "insert" function to append an array of data in excel into an access table too? ie we do not go through a sheet or a named range, but since i already have the data stored in an array i would like to directly append it to access table. (rather than say dumping it onto a worksheet first, then import the worksheet to access). of course this array function will NOT have the headers but only data (assuming i already created a table in access with all the right headings and consistent data type for each column).

AFAIK one can't INSERT from an array of data, though the second link I posted does load data from an array (not using INSERT).

 

[jolielle]

Hi Fazza,
yeah i see in that link that u could transfer data from excel to access by looping through the array. i was hoping i didn't have to do that (cos it might be slow if i have a HUGE array) but simply append by using "insert". but anyway thx for confirming that this can't be done! appreciate ur help and that of Ahmed's!

AFAIK one can't INSERT from an array of data, though the second link I posted does load data from an array (not using INSERT).

 

[Fazza]

you're welcome

I suggest you test the code for how long it takes. I think it is quite fast & maybe the fastest approach for data in an array. (that is, faster than loading from the array to a worksheet, and then from the worksheet.)

 

[ahmed_one]

you're welcome

I suggest you test the code for how long it takes. I think it is quite fast & maybe the fastest approach for data in an array. (that is, faster than loading from the array to a worksheet, and then from the worksheet.)

Hi Fazza,

Looping through data and performing Insert for each record is quite slow as compare to batch insert...I've already tried this approach (it took approx 7-10 mins to insert 20000 records through looping and only 3-4 seconds by bulk inserts), that is the reason for modifying my for bulk insert which is no doubt lightening fast..

jolielle, I will try to experiment on my existing code for array bulk insert, and if there is any development I will post the code for you.

Thanks

Ahmed

 

[Fazza]

thanks, Ahmed

7 to 10 minutes is too long. Was that using the code I posted? [Or similar, load data into array and loop through array. Not one record at a time from the worksheet.] If not, please post the code used.

Was the mdb file on a network drive? It should take only seconds, I'd thought.

regards


Post Script: Does the code have a single connection to the database (& multiple executions/updates) or are there multiple connections/updates (one per record)?

 

[ahmed_one]

thanks, Ahmed

7 to 10 minutes is too long. Was that using the code I posted? [Or similar, load data into array and loop through array. Not one record at a time from the worksheet.] If not, please post the code used.

Was the mdb file on a network drive? It should take only seconds, I'd thought.

regards


Post Script: Does the code have a single connection to the database (& multiple executions/updates) or are there multiple connections/updates (one per record)?

Dear Faaza,

No, not that code which you mentioned, but almost similar...Kindly check the 1st post in this thread...That code doesn't use array...It loop through each row and perform insert for each record..

MDB file is resides in similar folder with Excel and there is no network path involve...No multiple connection also.

Regards

Ahmed

 

[Fazza]

Thanks, Ahmed

Looping through basically cell by cell is what you're describing, or commenting on, as being slow. The code I posted only has a single read from the worksheet (to an array) and is quite different in performance. It is not "almost similar". I'm sure it would be much faster than the code in the original post.

regarsd

 

[ahmed_one]

Thanks, Ahmed

Looping through basically cell by cell is what you're describing, or commenting on, as being slow. The code I posted only has a single read from the worksheet (to an array) and is quite different in performance. It is not "almost similar". I'm sure it would be much faster than the code in the original post.

regarsd

Thanks Faaza, you are right...Loading data in array is much fast then performing cell by cell Insert...it took only 10 sec to export excel 40000 records to Access...

Comparison of both Array vs Bulk vs Cell by Cell: (For 40000)
Array Method: 10 seconds
Cell By Cell: 10 mins!!!
Bulk Insert: 3 seconds..

Thanks again for showing me more robust method...I will use in my other codes..

Regards

Ahmed