Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
MSDN Visual Basic Community
 
 
 
 
 
-->
TitleUse SQL to copy data from an Excel spreadsheet into an Access database
KeywordsADO, Access, Excel, database, SQL
CategoriesDatabase, 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.
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated