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

 

[Fazza]

PS. You may find some old posts (~10 years ago) where the IMEX=1 setting in the connection string is discussed. IIRC this is not robust.
So a bit like the three caveats you posted above for bad data. It causes problems. Really the best thing is to have robust data.

 

[OriginalAccess]

Thank you radciodc
Fantastic graph, very informative, since you run it on the same machine.
Do upgrade your hardware, though. I get 61 msecs exporting 5 columns with 3500 rows (to a RAM Drive) on i7-4920K running at 4.8GHz with 64Gb Memory.
According to your chart it equates to 0.003486 msec per record. Are you sure it's the hardware and not the code that causes the delay?

 

[OriginalAccess]

Let's clarify the terminology.
Access has Fields in a Table or Recordset, Excel has Columns on a spreadsheet.
All records in a given Field must be one, and only one type - Number, Text, Boolean etc. No Variants allowed.
You can set it to Memo, or even Text for mixed data import. Both A and E have 1024 limitation for text length.

Each cell in Excel column can be formatted individually, but for the purpose of this discussion let's assume the column format is set to "General"
That means that Excel will format each cell according to what it thinks is best for cell contents: "Mike" will become "Text", "123" number and "John 123" will stay "General".
"TRUE" will be formatted "Yes/No" even though in case of stock market it's text.

I would never design a database to allow any one field (Text or Memo) to contain multiple value types (an exception would be a temporary parsing table). I would create as many fields as there are possible types. Doing otherwise would defeat the purpose of relational database.
Excel is not an RDB, so it allows any values to be entered into any cell. It also allows duplicates, nulls and other RBD no-nos.

Therefore, assuming I would ever have mixed data column, I would clean up / split it into appropriate number of strictly columns before export.
There are at least two variations:

1) Each Excel cell contains sufficient data - "Mike Smith 123000 in Connecticut" becomes
"Mike Smith" in Name - Text, $123,000 in Sales - Currency, "Connecticut" in Location - Text.
All values get exported to the same row / record in Access.

2) Each cell does NOT contain complete data:
$B$45 = "Mike Smith"
$B$46 = "123,000 Connecticut"
$B$47 = "56000 Brooklyn"

That can be parsed into $C$45:$E$46 as
"Mike Smith", 1230000, "Connecticut"
"Mike Smith", 56000, "Brooklyn"

and exported into Access as 2 rows with three fields each.

The same can be done in Access after mixed export via recordset, but you would need to generate unique Primary keys to preserve sorting.

Note that
$B$45 = "Mike Smith"
$B$46 = "123,000"
$B$47 = "56000"
Cannot be parsed and exported into an RDB - you'll end up with meaningless duplicates.

However
$B$45 = "Mike Smith"
$B$46 = "123,000"
$B$47 = "Connecticut"
Can be converted into a single row/record: "Mike Smith", 1230000, "Connecticut"

Frankly I cannot imagine why I would ever have a mixed data column in Excel, but then I come from rdb background.
One reason for existence of mixed data columns in Excel might be that compared to Access its UI sucks.
In Access I have total control over data entry, most of which is selected via multi-column drop-downs.
The best Excel can do is "Validation"