|
|
|
| Title | Use SQL to copy data from an Excel spreadsheet into an Access database |
|---|
| Keywords | ADO, Access, Excel, database, SQL |
|---|
| Categories | Database, Office |
|---|
|
|
|
By Toby Bascom.
There's an easy way to do this using SQL. You'll need a reference to
ADO and ADOX. The empty MDB must be created first if it does not
exist; hence, the need for ADOX:
|
|
Dim cSource As String
cSource = App.Path & _
IIf(Right$(App.Path, 1) <> "\", "\", "") & _
"books.xls"
Dim cTarget As String
cTarget = App.Path & _
IIf(Right$(App.Path, 1) <> "\", "\", "") & _
"books.mdb"
If Dir(cTarget) = "" Then
Dim oCat As ADOX.Catalog
Set oCat = New ADOX.Catalog
oCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & cTarget
End If
Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection
Dim cSQL As String
With oCon
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& _
"Data Source=" & cSource & ";" & _
"Extended Properties=Excel 8.0"
.Open
cSQL = "SELECT * " & _
"INTO [Table1] " & _
"IN '" & cTarget & "' " & _
"FROM [Sheet1$]"
.Execute cSQL
End With
|
|
|
Note: The HowTo Copy data from an Excel spreadsheet into an Access database
shows how to open and manipulate the database and the spreadsheet to do the same thing. That example is
more an exercise in manipulating these objects rather than doing this efficiently.
|
| |
-->
|
|
