Insert to Sharepoint using ADO/VBA

[Isaac]

Cross posted: https://stackoverflow.com/questions...ions-for-a-insert-using-ado-with-ace-provider
Ok a little background. I am working on a project to try to use VBA to insert data directly into Sharepoint, using ADO. I recently achieved working code to do a single insert, select, or delete.

However, now I'm working on VBA to loop through numerous rows/columns in a Worksheet, and build up a SQL string for insert, along the lines of:

insert table (column list)
select 'value' as [col1], 'value' as [col2] union all
select 'value' as [col1], 'value' as [col2] union all
select 'value' as [col1], 'value' as [col2]

Why am I posting this in the Access Queries forum? Well because my ultimate problem I'm running into has nothing to do with Excel. It just has to do with ADO, using the ACE provider as a connection string, which basically means if I can get it to work in Access, I believe I can get ADO to execute the statement against Sharepoint. Access's query engine is basically making the rules for me here.

Important note: The real scenario involves Union'ing 200 rows, not 3. When I get VBA to create the 'perfect' SQL statement - which works in SQL Server - then, if I paste that into an Access SQL query - it says "Text is too long to be edited". or, "Query is too complext"
(depending on if I insert into tablename (columns) Select qry.* from (all the unions) as [qry] or simply insert into tablename (columns) [then all the SELECT..UNION ALL's] )

In order to test what "would work if it wasn't so long", I have a shortened-down testing SQL statement.
I already found out that Access will refuse to perform the insert if it doesn't detect a 'FROM' clause....union'ing a select of literal values without a From isn't enough.
So I added a 'From [the same table I'm inserting into]' in each little select line ... and I was able to trick Access into executing it, but not correctly: Result=0 rows.
Which stinks & feels unfair, (ha), because in Access, if you run: Select 'value' as [col1] from [AnyTableInYourDatabase] .... it sort of works, although not very usefully: It returns one record for each row in AnyTableInYourDatabase

Here is my current iteration. The question, despite all this detailed writing, is quite simple: How to create a VBA/SQL statement that will successfully execute using ADO-with-a-ACE-provider-specified, using ONE sql statement to include about 200 input rows of information (which I'm actually doing in Excel, but it doesn't matter I don't think).

Current SQL example: (Access query is OK with this syntax & formation, but inserts 0 rows) (remember I don't really want the FROM clauses, just doing it to make Access happy. If I leave it out, like I'd prefer, Access says "must contain at least one input table or query"

insert into IsaacTestExcelToSharepoint_BulkInsert (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19,
col20,col21, col22, col23, col24, col25, col26, col27, col28, col29, col30,col31, col32, col33, col34, col35, col36, col37, col38, col39, col40)

select qry.col1, qry.col2, qry.col3, qry.col4, qry.col5, qry.col6, qry.col7, qry.col8, qry.col9, qry.col10, qry.col11, qry.col12, qry.col13, qry.col14, qry.col15, qry.col16, qry.col17, qry.col18, qry.col19,
qry.col20, qry.col21, qry.col22, qry.col23, qry.col24, qry.col25, qry.col26, qry.col27, qry.col28, qry.col29, qry.col30, qry.col31 , qry.col32, qry.col33, qry.col34, qry.col35, qry.col36, qry.col37, qry.col38,
qry.col39, qry.col40

from
 
(
    select 'value' as [col1] , 'value' as [col2] , 'value' as [col3] , 'value' as [col4] , 'value' as [col5] , 'value' as [col6] , 'value' as [col7] , 'value' as [col8] , 'value' as [col9] , 'value' as [col10] ,
'value' as [col11] , 'value' as [col12] , 'value' as [col13] , 'value' as [col14] , 'value' as [col15] , 'value' as [col16] , 'value' as [col17] , 'value' as [col18] , 'value' as [col19] , 'value' as [col20] ,
'value' as [col21] , 'value' as [col22] , 'value' as [col23] , 'value' as [col24] , 'value' as [col25] , 'value' as [col26] , 'value' as [col27] , 'value' as [col28] , 'value' as [col29] , 'value' as [col30] ,
'value' as [col31] , 'value' as [col32] , 'value' as [col33] , 'value' as [col34] , 'value' as [col35] , 'value' as [col36] , 'value' as [col37] , 'value' as [col38] , 'value' as [col39] , 'value' as [col40]
from [IsaacTestExcelToSharepoint_BulkInsert] UNION ALL

    select 'value' as [col1] , 'value' as [col2] , 'value' as [col3] , 'value' as [col4] , 'value' as [col5] , 'value' as [col6] , 'value' as [col7] , 'value' as [col8] , 'value' as [col9] , 'value' as [col10] ,
'value' as [col11] , 'value' as [col12] , 'value' as [col13] , 'value' as [col14] , 'value' as [col15] , 'value' as [col16] , 'value' as [col17] , 'value' as [col18] , 'value' as [col19] , 'value' as [col20] ,
'value' as [col21] , 'value' as [col22] , 'value' as [col23] , 'value' as [col24] , 'value' as [col25] , 'value' as [col26] , 'value' as [col27] , 'value' as [col28] , 'value' as [col29] , 'value' as [col30] ,
'value' as [col31] , 'value' as [col32] , 'value' as [col33] , 'value' as [col34] , 'value' as [col35] , 'value' as [col36] , 'value' as [col37] , 'value' as [col38] , 'value' as [col39] , 'value' as [col40]
from [IsaacTestExcelToSharepoint_BulkInsert] UNION ALL

    select 'value' as [col1] , 'value' as [col2] , 'value' as [col3] , 'value' as [col4] , 'value' as [col5] , 'value' as [col6] , 'value' as [col7] , 'value' as [col8] , 'value' as [col9] , 'value' as [col10] ,
'value' as [col11] , 'value' as [col12] , 'value' as [col13] , 'value' as [col14] , 'value' as [col15] , 'value' as [col16] , 'value' as [col17] , 'value' as [col18] , 'value' as [col19] , 'value' as [col20] ,
'value' as [col21] , 'value' as [col22] , 'value' as [col23] , 'value' as [col24] , 'value' as [col25] , 'value' as [col26] , 'value' as [col27] , 'value' as [col28] , 'value' as [col29] , 'value' as [col30] ,
'value' as [col31] , 'value' as [col32] , 'value' as [col33] , 'value' as [col34] , 'value' as [col35] , 'value' as [col36] , 'value' as [col37] , 'value' as [col38] , 'value' as [col39] , 'value' as [col40]
from [IsaacTestExcelToSharepoint_BulkInsert]
    )
as [qry]

Current VBA code example:

Sub BulkInsert_SharepointOnPrem()
    Dim cnt As ADODB.Connection, rs As ADODB.Recordset
    Dim strSQL_EntireStatement As String

    Set cnt = New ADODB.Connection
    With cnt
        .ConnectionString = _
       "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://redacted.ad.redacted.com/sites/sitenameredacted/;LIST=IsaacTestExcelToSharepoint_BulkInsert;"
        .Open
    End With

..........In the middle is my vba code to build up my strSQL_EntireStatement, working fine

        cnt.Execute (strSQL_EntireStatement)
     
    If CBool(cnt.State And adStateOpen) = True Then cnt.Close
    Set cnt = Nothing
End Sub

There must be SOME way to convince ADO/ACE to perform something that's more efficient than looping through the input and executing one Insert per source item.

 

[Isaac]

I guess if my ADO provider, (ace), requires Access to be installed on the machine anyway, it wouldn't kill me to automate a new access application & db, link to a newly created excel sheet containing the values to insert, and create a querydef with the sql for a mass insert.

Maybe I am trying too hard for the distinction of an approach that "technically doesn't involve access" - but if it really does in some way, I might as well just stop fighting with this and fire up access in the background to do my dirty work.

Still very open to responses here, would be nice to solve if possible.

 

[Pat Hartman]

If the data you are loading is in a table, even a linked one, why not just use an append query? If the data is in Excel, you can do the append from there without involving Access at all.

 

[Isaac]

If the data you are loading is in a table, even a linked one, why not just use an append query? If the data is in Excel, you can do the append from there without involving Access at all.

I'm not following you. The data is in an Excel spreadsheet. I was hoping to not involve access, but you said an append query which sounds like using access.

Note that in my final post I did mention that if the Ado method I am using involves ace, which then requires an access install, I might as well automate access anyway. But I did not quite follow your suggestion.

The desire or goal in this case is how to go directly from Excel to sharepoint. Not using access.

 

[Pat Hartman]

An append query is an append query and has nothing to do with Access. I would look into how to append from Excel. Try posting a question in an Excel forum for help.

 

[Isaac]

Thats exactly what im already doing

 

[Pat Hartman]

Obviously I missed something. I though you were trying to do this with Access.

 

[CJ_London]

JET is used by windows and prior to 2007 also by Access. Post 2007, access uses ACE. So it may be rather than using Provider=Microsoft.ACE.OLEDB.12.0

you need to use the JET eqivalent is access is not installed.

(Microsoft.Jet.OLEDB.4.0)

 

[Isaac]

JET is used by windows and prior to 2007 also by Access. Post 2007, access uses ACE. So it may be rather than using Provider=Microsoft.ACE.OLEDB.12.0

you need to use the JET eqivalent is access is not installed.

(Microsoft.Jet.OLEDB.4.0)

Do you mean that using Jet would give me different query syntax rules/allowances - such as what I need along the lines of:

insert tablename (col1)
select 'val1' as [col1]
union all
select 'val1' as [col1]

 

[CJ_London]

No, jet uses the same syntax as ace. My point was more about whether or not you needed access to run a query in excel - i.e use the jet provider rather than ace in your connection string

 

[Isaac]

Got it - ok thank you