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

 

[Fazza]

Thanks for the helpful comparisons, Ahmed.

The bulk insert is still significantly faster. If it works fine for you, that might be better.

Unfortunately I don't recall what made me lose favour with the bulk insert. (I can guess it might likely have related to poor/inconsistent data, but that is only guessing. Sorry. It was some years ago.)

regards

 

[ahmed_one]

Thanks for the helpful comparisons, Ahmed.

The bulk insert is still significantly faster. If it works fine for you, that might be better.

Unfortunately I don't recall what made me lose favour with the bulk insert. (I can guess it might likely have related to poor/inconsistent data, but that is only guessing. Sorry. It was some years ago.)

regards

Thanks Faaza,

But if you remember why not to choose Bulk Insert, kindly post it...so that others can also change there coding before any disaster

Regards

Ahmed

 

[jolielle]

hi Ahmed and Fazza,
one thing i notice bulk insert using named range is that it's basically same as using an entire worksheet, because i see that all the extra information i put on the worksheet in cells NOT included in the named range also got imported into access, creating blank rows, or errors.

also, i realize that for exactly the same set of data, to store them in excel and to store them in access, access files are actually much bigger! i see that excel is ard 6mb, whereas access is 21mb! at first i thought ok fine maybe access has a larger fixed size for one table, and if i input more tables in the same database, the incremental size should be smaller. but NO! the size in access increases linearly as i put more and more tables of identical size (just different values inside, but exactly the same matrix). this is really counter intuitive to me, cos i thought access is a professional database software and hence should store same amt of info using less space? but at least based on my experience so far, excel is actually more efficient! am i missing something here? and yes i've already used "compact and repair database" to try to decrease the size.

Thanks Faaza,

But if you remember why not to choose Bulk Insert, kindly post it...so that others can also change there coding before any disaster

Regards

Ahmed

 

[xenou]

Hi,
Excel (2007+ file formats - xlsx, xlsb, or xlsm) use a compressed file format so actually they can be expected to be smaller files.
ξ

 

[jlynn303]

Hi Ahmed_One,

I have a question regarding the code you posted. As a brief background, I'd like to think I'm relatively fluent in Excel VBA but I'm admittedly a total novice with regards to Access VBA so I apologize for what might seem as elementary questions.

In your ssql variable definitions at the end of your code, you mention several named objects. I'd just like some clarification on what those named objects represent so that I can modify your code appropriately for my application. I too am trying to transfer an Excel named range into existing Access table using Excel VBA to drive the data transfer.

fdFolio: is that your Access table?
fdName, fdOne, fdTwo: are those the headings in the Access table to which you're importing the Excel data?

Also, I understand what you're doing conceptually when your defining the path of the Access database (dbPath). However, I'm confused several lines down when it looks like you're setting the Excel workbook connection equal to the Access database connection. How can these two connections be the same? dbPath clearly references a database object. How can it then also reference an Excel workbook object?

Thanks for your help. I greatly appreciate it.

Regards,

Jordan

 

[Fazza]

hello, Jordan

Starting from below code might be better? It loads data from a worksheet: change to suit a named range data source.

If you need a connection string for a newer Access version please google & edit the code to suit. On the line : cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DB_Path & ";"

Please add error checking as required. HTH. regards, Fazza

Sub ADO_ExcelToAccess(ByVal DB_Path As String, ByVal DB_TableName As String, ByVal SourceWorksheetName As String)
  'late bound
  'based on http://www.erlandsendata.no/english/index.php?d=envbadacexportado
  Dim r As Long, i As Long
  Dim cn As Object
  Dim rs As Object
  Dim ar As Variant
  
  With Worksheets(SourceWorksheetName).Range("A1").CurrentRegion
    ReDim ar(1 To .Rows.Count, 1 To .Columns.Count)
    ar = .Value
  End With
  
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DB_Path & ";"
  Set rs = CreateObject("ADODB.Recordset")
  rs.Open DB_TableName, cn, 1, 3, 2
  For r = 2 To UBound(ar, 1)
    With rs
      .AddNew
      For i = LBound(ar, 2) To UBound(ar, 2)
        .fields(ar(1, i)) = ar(r, i)
      Next i
      .Update
    End With
  Next r
  rs.Close: Set rs = Nothing
  cn.Close: Set cn = Nothing
  Erase ar
End Sub

 

[dnjoshi]

Is there a way to show the progress of rows inserted in excel status bar? I want to show the current progress so that the user knows something is running. I dont want to show the static message rather it should be 3 of 100 rows, 4 of 100 rows....

Dear lion22x

thanks for posting on this thread...

it's a fairly simple code to insert data from Excel sheet to Ms.Access:

Public Sub DoTrans()

//I am using late binding for creating objects, this code will run on any version of Excel
Set cn = CreateObject("ADODB.Connection")

//This line will set the path of Ms.Access database, in this case it is assumed that Ms.Access DB will be in same folder as in Excel File.
dbPath = Application.ActiveWorkbook.Path & "\FDData.mdb"

// This line will set the path of Active workbook
dbWb = Application.ActiveWorkbook.FullName

// Get the Active sheet name, so that we can correctly Export data using this variable
dbWs = Application.ActiveSheet.Name

// Set the connection, we are creating Excel workbook connection
scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath

//Another variable to hold worksheet name, but in this case it will //be in a format that is required to pass in open connection //method, which execute the SQL statement in next line
dsh = "[" & Application.ActiveSheet.Name & "$]"

// Open the connection
cn.Open scn

//Create SQL statement using proper sheet name dsh, created //before opening connection

ssql = "INSERT INTO fdFolio ([fdName], [fdOne], [fdTwo]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh


//Run the SQL
cn.Execute ssql


End Sub


You can use a for loop to run the above procedure for multiple sheets to export data to Ms.Access Database.

Let me know if this helps

regards

Ahmed

 

[Fazza]

hello & welcome

With the code you posted, no. The transfer is done as a single step - INSERT INTO. BTW, you might add some housekeeping to the code to close the connection (& set object variables to nothing though that isn't really necessary).

With the code I posted, yes. The transfer is done in a loop. For r = 2 to UBound(ar,1)
So a line within the loop like : application.statusbar = format$(r-1,"#,##0") " of " & format$(UBound(ar,1) -1,"#,##0") & " rows"
And after the loop : application.statusbar = false

This will slow the execution & there are alternatives that could be a little faster - most obviously would be to update the status bar every so often, such as every 1000 records. The overall method (of transferring from Excel to Access) should be fast enough that updating the status bar every row is not worthwhile.

How much data is there to upload? How long does it take? Where is the Access file (on a network that could be slow, for example, whereas on a c drive it should be pretty fast)? I'm curious cause I readily transfer reasonable amounts of data so quickly that status bar messages wouldn't be worthwhile.

regards, Fazza

 

[sethmac86]

Wow, amazing bit of code! Amazingly fast transfer, saved me over an hour of time when its not possible to run the transferspreadsheet command directly from access! Only question I've got is about the syntax for the following bit of code

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">ssql = "INSERT INTO fdFolio ([fdName], [fdOne], [fdTwo]) " ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh</code>

What are the options for the FROM statement? What else is there other than Excel 8.0, and what does HDR mean? I get the database portion.

Many Thanks!

 

[Fazza]

hello Seth

Great info on connection strings : http://www.connectionstrings.com/

This aging thread is great too : http://www.xtremevbtalk.com/showthread.php?t=217783

There will be some Microsoft Knowledge base articles that are very good too. Please google

regards