Excel 2010 VBA need help with SQL Insert into , using ADODB

[DougP]

Error: first it says the table already exists. so it I remove the tab then it says the table can not be found. So what am I doing wrong here? Can I Insert records from one Tab to another using this in Excel, or, not?
We have 1500 rows and 30 columns and I just need teh following columns copied into a new sheet. so I thought this would be quick and easy?

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & WorkbookDestination & ";" & _
        "Extended Properties=Excel 8.0;"
        .CursorLocation = adUseClient
        .Open
    End With

    Dim SQLInsert As String
    SQLInsert = "SELECT [Webtime/Pace Name], " & _
                "EID, " & _
                "[LDAP Full Name], " & _
                "[First Name], " & _
                "[Last Name], " & _
                "[Type2], " & _
                "[LDAP Manager], " & _
                "[Report 1], " & _
                "[Report 2], " & _
                "[Report 3], " & _
                "[Report 4] " & _
                "INTO [Sheet1$] " & _
                "FROM [Base$];"
    rs.Open SQLInsert, cn

DougP

 

[Andrzejek]

Every time I use INSERT, my syntax is something like:
[tt]
INSERT INTO SomeTable (Select F1, F2, F3, ... From SomeOtherTable Where...)
[/tt]
So wouldn’t yours be something like:

SQLInsert = "INSERT INTO [Sheet1$] (SELECT [Webtime/Pace Name], " & _
                "EID, " & _
                "[LDAP Full Name], " & _
                "[First Name], " & _
                "[Last Name], " & _
                "[Type2], " & _
                "[LDAP Manager], " & _
                "[Report 1], " & _
                "[Report 2], " & _
                "[Report 3], " & _
                "[Report 4] )" 

Cn.Execute SQLInsert

Just a guess here…



Have fun.

---- Andy

 

[DougP]

Ok so I have this now, added teh yellow bit doone bwlow, we need to know to "select" "form" right?
But It does not like that either.
Error: arguments of wrong type, are out of acceptable range or are in conflict with one another.

SQLInsert = "INSERT INTO [BaseMini2$] (SELECT [Webtime/Pace Name], " & _
"EID, " & _
"[LDAP Full Name], " & _
"[First Name], " & _
"[Last Name], " & _
"[Type2], " & _
"[LDAP Manager], " & _
"[Report 1], " & _
"[Report 2], " & _
"[Report 3], " & _
"[Report 4] " & _
[highlight #FCE94F]" From [Base$])"[/highlight]
rs.Open SQLString, cn

DougP

 

[Andrzejek]

Yes, I know I missed " From [Base$])" part.

But did you try: Cn.Execute SQLInsert
instead of: rs.Open SQLString, cn

???

Have fun.

---- Andy