SQL Insert All from Excel to Access

[Jon von der Heyden]

Hi

I need some assistance with SQL syntax please. I need to insert all records in a worksheet (excl. headers in row) into an Access database. Do I really need to specify the name of every field and every entry? Is there a way to load my entire worksheet table into the Access table?

I've been toying with the below but not yet established how to pass the actual table:

Option Explicit
Public Sub InsertData()
'// written by Jon von der Heyden //
'// inserts data into an Access table //
'// June 2009 //
'// reference to Microsoft ActiveX Data Objects 2.x Library must be enable (where x refers to hights #)//
Dim cnAccess As ADODB.Connection
Dim sPath
Dim sConnect As String
Dim sSQL As String
'// open the upload file //
Application.Dialogs(xlDialogOpen).Show
sPath = Application.GetOpenFilename("Access Databases(*.mdb), *.mbe")
If sPath = False Then GoTo Finish
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & sPath
sSQL = "INSERT INTO Workflow_TBL (*);"
Set cnAccess = New ADODB.Connection
cnAccess.ConnectionString = sConnect
cnAccess.Open
cnAccess.Execute sSQL, , adCmdText + adExecuteNoRecords
ActiveWorkbook.Close
cnAccess.Close
Set cnAccess = Nothing
Finish:
End Sub

 

[Jon von der Heyden]

SOLVED: SQL Insert All from Excel to Access

Went down an alternative route. Solution here:

Option Explicit
Sub InsertData()
'// written by Jon von der Heyden //
'// inserts data into an Access table //
'// June 2009 //
'// reference to Microsoft ActiveX Data Objects 2.x Library must be enable (where x refers to highest #)//
Dim objApp As Object
Dim accApp As Access.Application
Dim strAccessPath
Dim strExcelPath
Set accApp = Nothing
Set accApp = CreateObject("Access.Application")
With accApp
    On Error Resume Next
        Set objApp = accApp
        objApp.AutomationSecurity = 1
    On Error GoTo 0
    
    strAccessPath = Application.GetOpenFilename("Access Databases(*.mdb), *.mdb", , "Where is the MS Access table?")
    If strAccessPath = False Then GoTo Finish
    
    .OpenCurrentDatabase filepath:=strAccessPath
    
    strExcelPath = Application.GetOpenFilename("Microsoft Office Excel Files(*.xls), *.xls", , "Where is the upload file?")
    If strExcelPath = False Then GoTo Finish
    
    .DoCmd.TransferSpreadsheet acImport, 8, "Workflow_TBL", strExcelPath, True, "TBL_ALL"
End With
Finish:
On Error Resume Next
    accApp.Quit acquitsavenone
    Set accApp = Nothing
End Sub

 

[Norie]

Re: SOLVED: SQL Insert All from Excel to Access

Jon

For your original question perhaps you could have used something like this.

INSERT INTO DataRegister
SELECT NewDataRegister.*
FROM NewDataRegister;

Note I tested this twice, once where the tables had the same name & no fields (well they would have since I cheated and used the same table twice).

The second time I created a new table (again cheating by just copying the original) and removed some fields.

It worked both times, the missing fields in the the latter were just blank.

Totally uncomprehensive and probably irrelavant to working with Excel, just thought I'd mention it.

PS I would stick with TransferSpreadsheet - could be useful when field names don't match up.

 

[Jon von der Heyden]

Hey Norie
Thanks for your feedback. I'm pleased to be reassured that TransferSpreadsheet is the better way.

 

[Norie]

Jon

Don't know about better, just seeing the initials ADO gives me the heebie-jeebies.

If something built-in like TransferSpreadsheet works then why not use it.

Though I was thinking of posting code that looped through every column of the Excel sheet, got the column headers/field names.

Then construct a large unwieldy SQL statement that could then be used to loop through the rows and insert the data into the table one record and a time.

But it's quicker to type DoCmd.T..., especially with Intellisense.

 

[Jon von der Heyden]

Though I was thinking of posting code that looped through every column of the Excel sheet, got the column headers/field names.

Then construct a large unwieldy SQL statement that could then be used to loop through the rows and insert the data into the table one record and a time.

Lol, I did that and then abandonned it knowing that there must be a better way.

Also, based on my experience on this project, the TransferSpreadsheet routine is quicker than the plain text SQL method.

 

[Fazza]

Hi, Jon. Maybe of interest, http://www.mrexcel.com/forum/showthread.php?t=332472 ? HTH, Fazza

 

[Jon von der Heyden]

Thank you Fazza. I've added that thread to my favourites to call on. I'm curious though, which method do you deem more robust / efficient? DAO DoCmd TransferSpreadsheet or ADO SQL?

 

[Fazza]

Hi, Jon.

I don't know, sorry. I use ADO within Excel 99.99% of the time, and hardly ever use Access.

Cheers, Fazza