Excel to Acess via DAO

[gapa]

Hi,

wondering if someone could help me out here. I have copied this piece of code that will enable you to copy Excel data in Access. The problem is that it is copying cell by cell. Is there a way to modify it so that it can copy the whole row (row 2 to blank) or whole column or even better then whole sheet ?

thanks

Paul

the code is attached below

'------------------
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb")
' open the database
Set rs = db.OpenRecordset("TableName", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

 

[rasciodc]

Thanks for the reply! Your alternative looks good but will have me loop through all registers in a record set to upload them to Access. This could be quite slow...

I also thought of using the "DoCmd.TransferSpreadSheet" method.... have you ever had any experience with it?

Nevertheless, I'll try it and post results here.

Thanks!

 

[Fazza]

Thanks for the reply! Your alternative looks good but will have me loop through all registers in a record set to upload them to Access. This could be quite slow...

I also thought of using the "DoCmd.TransferSpreadSheet" method.... have you ever had any experience with it?

Nevertheless, I'll try it and post results here.

Thanks!

I expect the code would not be slow.

I'm guessing DoCmd.TransferSpreadsheet (which I've not used) would be slower, as it involves controlling a different application.

It'll be interesting to hear what you find when you compare the two approaches.

 

[rasciodc]

Here are the results I've got so far with the following methods:

1) RecordSet Looping (the "ADO_ExcelToAccess" function posted by Fazza in previous thread)
2) Transfer Excel Range (transfer an excel range in "bulk"). SPOILER: Limited to 65535 rows
3) Export Excel Sheet (DoCmd.TranferSpreadsheet). SPOILER: Also limited to 65535 rows - That was a surprise to me!

The data being pushed to Access is comprised of 23 columns and I tested from 10k rows to 100k rows in an i5-6300, 8GB of RAM and Win10 (x64) machine.

As predicted, opening an Access instance and "pulling data from Excel" was slower than opening and ADODB and "pushing data to Access".

Transfering a Range and Looping through a RecordSet took about the same amount of time, with the difference that the RecordSet doesn't have the row number limit of 65535.

This really was a surprise to me, as I thought that going through each record and updating it to Access would be the most slowest method compared to "bulk" data transfer methods.... Well, living and learning!

So, I can really endorse the code posted in the post #20 by Fazza and that's what I'm gonna use from now on.

Thanks for the help!! Cheers!

 

[Fazza]

Wow. I like that. You've done some excellent work & a lovely post/explanation. Thank you

 

[OriginalAccess]

Thank you, Fazza

I've been using your method for 3 years. There are 3 caveats:

1) If you export into an existing table with primary keys set up, the export will blow up in Excel - " Duplicate keys are not allowed…"

2) Ocasionally the export will fail because it grabs extra (empty) columns or rows. The error will be "Nulls not allowed". To prevent this error do
.EntireColumn.Delete and EntireRow.Delete on both export range, and ajacent columns and rows. Delete take time, so do the minimum required, not 16000.

3) Supposed text variables will be misinterpreted - "TRUE" will be interpreted as -1 and you'll get "Nulls not allowed" error for text field. Solution:
in the export range rename "TRUE" to "'TRUE".

Thanks again,
In general I get 1 hiccup every 3 months!

 

[Fazza]

Thanks for that. Yes, bad data will cause problems.

Another common problem situation - and well documented wrt Excel - is the so-called mixed data type: when a single field contains both text & numeric entries.

 

[OriginalAccess]

Thanks for that. Yes, bad data will cause problems.

Another common problem situation - and well documented wrt Excel - is the so-called mixed data type: when a single field contains both text & numeric entries.

Ideally you would never put text and numbers in the same column in Excel, But if you do, it's easy enough to split before export:

Option Base 1

Public Sub SplitNum()

Dim bolIsNum As Boolean
Dim lngCurrentRow As Long
Dim i As Integer
Dim avarMixed() As Variant
Dim asngNumber() As Single

Const clngStartRow As Long = 2
Const clngLastRow As Long = 10
Const cstrColMixed As String * 1 = "H"
Const cstrColNumber As String * 1 = "I"

  With ThisWorkbook.Worksheets("Export")
  avarMixed() = .Range(cstrColMixed & clngStartRow & ":" & cstrColMixed & clngLastRow).Value
  ReDim asngNumber(1 To clngLastRow - 1)
  For lngCurrentRow = 1 To clngLastRow - 1
    bolIsNum = False
    For i = 1 To Len(avarMixed(lngCurrentRow, 1))
      bolIsNum = IsNumeric(Mid(avarMixed(lngCurrentRow, 1), i, 1))
      If bolIsNum = True Then Exit For
    Next i
    If bolIsNum = True Then
     asngNumber(lngCurrentRow) = Right(avarMixed(lngCurrentRow, 1), _
        Len(avarMixed(lngCurrentRow, 1)) - i + 1)
     avarMixed(lngCurrentRow, 1) = Trim(Left(avarMixed(lngCurrentRow, 1), i - 1))
    End If
  Next lngCurrentRow
    
  .Range(cstrColMixed & clngStartRow & ":" & cstrColMixed & clngLastRow).Value = avarMixed()
  .Range(cstrColNumber & clngStartRow & ":" & cstrColNumber & clngLastRow).Value2 = asngNumber()
  End With

End Sub

The original cells contained "Mike Smith 789" and similar values

Error handler is left as an exercise for the student

 

[Fazza]

Entries like "Mike Smith 789" are simple text. These are fine (so long as the field is a text field).

The mixed data issue is when in a field some records have (purely) numbers and some have text.

Here is a good info on the subject http://dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/
Google will find many more.

 

[OriginalAccess]

"Mike Smith 789" gets split into two fields - "Mike Smith" and "789"

Obviously I used a simple sample, but even with the code I provided, assuming you have "Text1" and "Num1" fields in Access it will parse properly. If some cells have both text and numbers, some only text and some only numbers, in some Access records you'll have both Text1 and Num1 filled, in some only Text1 and in some only Num1. I assume you are exporting 2 columns from Excel.

For situations like "John 123 is absent" you wold need to loop through IsNumeric to get "John is absent" and "123". All depends on how consistent is the data in the original Excel range.

I looked at 'daily dose". Obviously each situation will require individual solution. I see no general problems, however. I wrote quite a few parsing algorithms (primarily for addresses). I did it in Access though, not in Excel.

BTW. You can export data to Access from Excel array directly via ADODB.Connection (not very reliable and little or no speed increase unless you build array on the fly) or by declaring Access recordset and cycling through array - much more reliable, faster than regular export but a lot of coding and maintenance.

The primary advantage of exporting array, is that you can specify precise size. With regular ODBC export Access sometimes grabs extra Column or Row, and you end up with "No nulls allowed". According to Microsoft ODBC connection is supposed to accept range as part of the source - in reality it ignores it.

 

[Fazza]

Situations like "Mike Smith 789" or "John 123 is absent" are nothing to do with the mixed data type issue. Also it is nothing to do with data parsing. Assuming there are 2 export columns is also off track.

The issue is when a single field has some records entirely text & others entirely numeric. This is a general problem.