Microsoft Office Tutorials and References
In Depth Information
Lesson 29: Working with Access from Excel
The preceding line of code will fail, and result in a run time error, if any field in
your Excel table contains data that is in conflict with the specified data type of
its corresponding field in the Access table. For example, if the second field in your
Access table is specified to be a Number data type, and in your Excel worksheet,
column B has a text value in it, the macro will fail at this point because a text value
is attempting to be placed into an Access field meant to accept only numbers.
‘Close the connections.
‘Release Object variable memory.
Set dbRecordset = Nothing
Set dbConnection = Nothing
‘Clear the range of data (the records) you just transferred.
‘Range(“A2:H” & LastRow).ClearContents
You are probably aware that beginning with the release of Office 97,
extensions changed for Microsoft’s applications. For example, Excel workbooks that
had the extension .xls now are either .xlsx or .xlsm . Access extensions also
changed, from .mdb to .accdb , as shown in the preceding macro.
Take note of the version(s) of Excel and Access when the time comes to
implement this code. Especially, the Provider line in the code is
.Provider = “Microsoft.ACE.OLEDB.12.0;Data Source=” & dbFileName &
“;Persist Security Info=False;” .
Had this been a version of Office prior to 2007, that same line might have been
.Provider = “Microsoft.Jet.OLEDB.4.0”
.Provider = “Microsoft.Jet.OLEDB.4.0;” & “Data Source=” _
& dbFileName & “;” & “Extended Properties=Excel 8.0;” .