Problem with VBA/ADO Insert Into

**JohnM3** · 07-04-2011, 12:10 PM

Goal: Insert WS data into MS Access Table
using ADO and SQL

Error:Invalid Argument

SQL Used:
INSERT INTO MyNewTable (C1,C2,C3) SELECT * FROM
[Excel 12.0;HDR=YES;C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm].[BY_X$]

Connection:
Valid Connection

Table Structure:
C1 - Text
C2 - Text
C3 - Text

Table Name:
MyNewTable

WS NAME:
BY_X

Code:
The code is below but now useful
in that it calls related properties.


Public Function Add_Records_InsertInto_OneShot() As Boolean

    On Error GoTo EH_Add_Records_InsertInto_OneShot

    Dim i As Integer
    Dim colCursor As Integer
    Dim rowCursor As Integer
    Dim curCell As String
    Dim sValue As String

    '---------------------
    '   Init
    '---------------------
    Init_Cat_Vars
    ADO_CloseConnection = False
    Cat_TableName = "MyNewTable"
    
    '---------------------
    '   WB_StartUp_Quick
    '---------------------
    WSMyDataClear = False
    ADO_RunADOProviders = True
    ADO_RunADOExtenders = True
    WB_StartUp_Quick WSMyDataClear, ADO_RunADOProviders, ADO_RunADOExtenders

    '-----------------------
    '   Data Range
    '-----------------------
    With WS_BY_X
        LR = .Range(.Cells(.Rows.Count, 1).End(xlUp).Address).Row
        LC = .Range(.Cells(1, .Columns.Count).End(xlToLeft).Address).Column
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set Rng_Insert = .Range(.Cells(1, 1), .Cells(LR, LC))
        With Rng_Insert
            CntRow = .Rows.Count
            CntCol = .Columns.Count
        End With
    End With

    '---------------------
    '   SQL STATEMENT
    '---------------------
    SQL_Header = vbNullString
    SQL_ColHD = vbNullString
    SQL_RowDT = vbNullString
    SQL1 = vbNullString

    '-----------------------
    '   Column Header Loop
    '-----------------------
    With Rng_Insert
        For i = 1 To CntCol
            Select Case i
                Case 1: SQL_ColHD = SQL_ColHD & WB_CON_LP & .Columns(i).Rows(1).Value & "" & ","
                Case Is <> CntCol: SQL_ColHD = SQL_ColHD & "" & .Columns(i).Rows(1).Value & "" & ","
                Case CntCol: SQL_ColHD = SQL_ColHD & "" & .Columns(i).Rows(1).Value & "" & ")"
            End Select
        Next
    End With
    
    '---------------------
    '   SQL STATEMENT
    '---------------------
    SQL_Header = "INSERT INTO "
    SQL_Header = SQL_Header & Cat_TableName
    SQL_Header = SQL_Header & WB_CON_SPACE
    SQL_Header = SQL_Header & SQL_ColHD
    
    '---------------------
    '   SQL STATEMENT
    '---------------------
    SQL_RowDT = SQL_RowDT & " SELECT * FROM " & vbCrLf
    SQL_RowDT = SQL_RowDT & "["
    SQL_RowDT = SQL_RowDT & "Excel 12.0;HDR=YES;"
    SQL_RowDT = SQL_RowDT & WB_This.Path & "\" & WB_This.Name
    SQL_RowDT = SQL_RowDT & "]"
    SQL_RowDT = SQL_RowDT & "."
    SQL_RowDT = SQL_RowDT & "["
    SQL_RowDT = SQL_RowDT & "BY_X$"
    SQL_RowDT = SQL_RowDT & "]"
    
    '---------------------
    '   SQL STATEMENT
    '---------------------
    SQL1 = SQL_Header & SQL_RowDT

    '---------------------
    '   Connect
    '---------------------
    Catalog_ConnectUsingWBFolder
    
    '---------------------
    '---------------------
    Set ADO_Command = Nothing
    Set ADO_Command = New ADODB.Command
    With ADO_Command
        .ActiveConnection = ADO_CatConnection
        .CommandText = SQL1
        .Execute
    End With
    
    '-----------------------
    '-----------------------
    Add_Records_InsertInto_OneShot = True
    Exit Function
EH_Add_Records_InsertInto_OneShot:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "Add_Records_InsertInto_OneShot"
    ADO_Kill
    Add_Records_InsertInto_OneShot = False
    Exit Function

End Function

**MarvinP** · 07-04-2011, 01:59 PM

Hi John,

Why not just import Excel data into Access?

http://conferences.aicpa.org/tech08/...%20Fleenor.pdf

**JohnM3** · 07-04-2011, 09:18 PM

Hi MarvinP

That is a great article and I appreciate it and have saved it.

However, I am very interested in nailing down the correct syntax to simply use
ADO/SQL to load a worksheet to an Access table and hope someone here might
already be using it and might run across my question. I am googled-out and out
of ideas but suspect my syntax is close to the correct syntax.

regards
John

**JohnM3** · 07-04-2011, 10:37 PM

Goal: Insert an Excel WS to an Access table (in one shot) using ADO and SQL.

Good SQL: The following SQL works (Excel 2007 -> Access 2007)

INSERT INTO MyNewTable SELECT * FROM [BY_X$]
IN 'C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm' 'Excel 12.0 Macro;HDR=YES;'

Note:
There has to be as space between .... xlsm' 'Excel (2nd line of SQL)

This code uses internal properties that are not defined below.


Public Function Add_Records_InsertInto_OneShot_V2() As Boolean

    'http://www.1keydata.com/sql/sqlinsert.html
    'http://msdn.microsoft.com/en-us/library/bb208861(v=office.12).aspx

    'Google Search
    'insert into select access
    'http://www.ozgrid.com/forum/showthread.php?t=98209&page=1

    On Error GoTo EH_Add_Records_InsertInto_OneShot_V2

    Dim i As Integer

    '---------------------
    '   Init
    '---------------------
    Init_Cat_Vars
    ADO_CloseConnection = False
    Cat_TableName = "MyNewTable"
    
    '---------------------
    '   WB_StartUp_Quick
    '---------------------
    WSMyDataClear = False
    ADO_RunADOProviders = True
    ADO_RunADOExtenders = True
    WB_StartUp_Quick WSMyDataClear, ADO_RunADOProviders, ADO_RunADOExtenders

    '-----------------------
    '   Data Range - Not Used
    '-----------------------
    With WS_BY_X
        LR = .Range(.Cells(.Rows.Count, 1).End(xlUp).Address).Row
        LC = .Range(.Cells(1, .Columns.Count).End(xlToLeft).Address).Column
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set Rng_Insert = .Range(.Cells(1, 1), .Cells(LR, LC))
        With Rng_Insert
            CntRow = .Rows.Count
            CntCol = .Columns.Count
        End With
    End With

    '---------------------
    '   SQL STATEMENT
    '---------------------
    SQL_Header = vbNullString
    SQL_ColHD = vbNullString
    SQL_RowDT = vbNullString
    SQL1 = vbNullString

    '-----------------------
    '   Column Header Loop
    '-----------------------
    With Rng_Insert
        For i = 1 To CntCol
            Select Case i
                Case 1: SQL_ColHD = SQL_ColHD & WB_CON_LP & .Columns(i).Rows(1).Value & "" & ","
                Case Is <> CntCol: SQL_ColHD = SQL_ColHD & "" & .Columns(i).Rows(1).Value & "" & ","
                Case CntCol: SQL_ColHD = SQL_ColHD & "" & .Columns(i).Rows(1).Value & "" & ")"
            End Select
        Next
    End With
    
    '---------------------
    '   SQL STATEMENT
    '---------------------
    SQL_Header = "INSERT INTO "
    SQL_Header = SQL_Header & Cat_TableName
    
    '---------------------
    '   SQL STATEMENT
    '---------------------
    SQL_RowDT = SQL_RowDT & " SELECT * FROM " & "[BY_X$] " & vbCrLf
    SQL_RowDT = SQL_RowDT & " IN "
    SQL_RowDT = SQL_RowDT & "'"
    SQL_RowDT = SQL_RowDT & WB_This.Path & "\" & WB_This.Name
    SQL_RowDT = SQL_RowDT & "'"
    SQL_RowDT = SQL_RowDT & " "
    SQL_RowDT = SQL_RowDT & "'Excel 12.0 Macro;HDR=YES;'"
    
    'BAD
    'SQL_RowDT = SQL_RowDT & "'Excel 12.0 Macro;'"
    'SQL_RowDT = SQL_RowDT & """Excel 12.0 Xml;HDR=YES;"""
    
    '---------------------
    '   SQL STATEMENT
    '---------------------
    SQL1 = SQL_Header & SQL_RowDT

    '---------------------
    '   ACTUAL SQL STATEMENT
    '---------------------
    'INSERT INTO MyNewTable SELECT * FROM [BY_X$]
    'IN 'C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm' 'Excel 12.0 Macro;HDR=YES;'

    '---------------------
    '   Connect (to Access Database)
    '---------------------
    Catalog_ConnectUsingWBFolder
    
    '---------------------
    '   Command
    '---------------------
    Set ADO_Command = Nothing
    Set ADO_Command = New ADODB.Command
    With ADO_Command
        .ActiveConnection = ADO_CatConnection
        .CommandText = SQL1
        .Execute
    End With
    
    '-----------------------
    '-----------------------
    ADO_Kill
    
    '-----------------------
    '-----------------------
    Add_Records_InsertInto_OneShot_V2 = True
    Exit Function
EH_Add_Records_InsertInto_OneShot_V2:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "Add_Records_InsertInto_OneShot_V2"
    ADO_Kill
    Add_Records_InsertInto_OneShot_V2 = False
    Exit Function
End Function

**MarvinP** · 07-04-2011, 11:33 PM

Great Job!!

Now make the Forum Master happy and edit your first post and mark the Prefix to the Title as Solved!!

I'm so glad you got this answer as I didn't feel I was any help.

AND - Your code above might help others with the same question!!

**romperstomper** · 07-05-2011, 03:14 AM

FYI, you were very close with what you had originally:

INSERT INTO MyNewTable (C1,C2,C3) SELECT * FROM
[Excel 12.0;HDR=YES;Database=C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm].[BY_X$]

**JohnM3** · 07-05-2011, 12:51 PM

Hi RS

_THANKS_ for posting that. I like your solution better.

INSERT INTO MyNewTable (C1,C2,C3) SELECT * FROM
[Excel 12.0;HDR=YES;Database=C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm].[BY_X$]

regards
John

**sridevi kumaravel** · 10-25-2012, 07:12 AM

Hi I am trying to import data to Mssql 2008, can anyone help me

For the below query, equivalent query in SQL

INSERT INTO MyNewTable SELECT * FROM [BY_X$]
IN 'C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm' 'Excel 12.0 Macro;HDR=YES;'

When i try the same query in sql it says 'incorrect syntax near IN'

Even i tried the option suggested by RS,
Like this
INSERT INTO MyNewTable (C1,C2,C3) SELECT * FROM
[Excel 12.0;HDR=YES;Database=C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm].[BY_X$]

I am getting invalid object name.please help me.

Regards,
Sridevi

**JosephP** · 10-25-2012, 07:45 AM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Problem with VBA/ADO Insert Into

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Problem with VBA/ADO Insert Into

Re: Problem with VBA/ADO Insert Into

Re: Problem with VBA/ADO Insert Into

Re: Problem with VBA/ADO Insert Into

Re: Problem with VBA/ADO Insert Into

Re: Problem with VBA/ADO Insert Into

Re: Problem with VBA/ADO Insert Into

Re: Problem with VBA/ADO Insert Into

Re: Problem with VBA/ADO Insert Into

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions