Solved Correct VBA Reference for ADODB (2 Viewers)

Isaac

Lifelong Learner
Local time
Today, 16:59
Joined
Mar 14, 2017
Messages
8,738
When using ADODB object in Excel, early binding, how do I know which is truly the "correct" reference to check here? I can do trial and error, but I hate to personify the old joke "Hey, it compiles! Ship it" mentality, and was hoping for some guidance on the rhyme or reason for which one to check. For instance, I checked the 2.0 item, and my project suddenly compiled (with a declared object as as ADODB.Connection). Then I unchecked that and checked the 2.1 library, and that compiled too. Am I just going for the most recent sounding version or is there some information/knowledge I am missing to make a more intelligent selection?

Or do I even have the right list of options in focus here:

ExcelReferenceForADODB.jpg
 

Micron

AWF VIP
Local time
Today, 19:59
Joined
Oct 20, 2018
Messages
3,476
IMO, the reason that the library isn't just over-written with the latest when you install Access version is because you should develop for the oldest version among your users. So if you have some on 2007 and some on 2013, you could have several variations of the same library and should use the oldest - keeping in mind that you will not be able to take advantage of whatever is in the latest version that is not in the earlier ones. If everyone is using the same version, I'd go with the latest - if you don't care that the code might fail if you gave the db to someone with an older version. This would apply to either Access or any app (like Excel) that you're doing automation with.
 

Isaac

Lifelong Learner
Local time
Today, 16:59
Joined
Mar 14, 2017
Messages
8,738
Ok, that makes sense. Eventually I'll convert this to late binding and (hopefully) this question will be moot, but I do love me some Intellisense when developing especially in less familiar territory, so I'm using references for now.
 

isladogs

MVP / VIP
Local time
Today, 23:59
Joined
Jan 14, 2017
Messages
18,186
Whilst I agree with Micron's comment the ActiveX Data objects 6.1 library has been around since Access 2007. I would therefore use that library.
 

Micron

AWF VIP
Local time
Today, 19:59
Joined
Oct 20, 2018
Messages
3,476
More info here - see post by Ralph. Link to Tony Toews reason not to "trust" ADO doesn't work. If anyone knows where it went it might make for interesting reading. I was going to ask if you really need ADO but forgot to.

Apparently, features are not the only reason to choose a version - bug fixes would point you to the latest version. I found the comment about most/all of those references pointing to the same thing odd and would think you could test that by following the path to the file - assuming you can. Why M$ doesn't allow that window to scroll horizontally is beyond me.
 

isladogs

MVP / VIP
Local time
Today, 23:59
Joined
Jan 14, 2017
Messages
18,186
I should point out that I rarely use ADO as I find DAO much easier to use and it does almost everything I need
Over 20 years ago, MS decided that ADO was 'the future' and that DAO was to be phased out.
Only a few years later, that decision was reversed by MS,. DAO remains the favoured approach
 

Isaac

Lifelong Learner
Local time
Today, 16:59
Joined
Mar 14, 2017
Messages
8,738
Edit 2020-07-01 16:17 I think (?) I am supposed to add a tag to this post that FYI I have cross posted this also on Stack Overflow. There.
--------------------------------------------------------------------------------------------------------------------------------
Thanks gentlemen. I'm using ADO because I want to write to a Sharepoint list using VBA in Excel.
Right now I am getting "The Microsoft Access database engine could not find the object 'Isaac Test Excel To Sharepoint', and the code errs on the INSERT line. I suspect it is because of either my site reference being wrong, or my list ID being wrong.

I don't think my list ID is wrong, because I carefully followed the directions to extract the list ID from the URL that's exposed when you go to List Settings, carefully Replacing the 3 replaceable items as mentioned here. I am passing it in as:
Code:
strSharepointListID = "{3404D534–10CB–4F53–BB9D–37F5612155F1}"

I would like to have concluded, "the connection is correct because the code doesn't err until all the way to the INSERT statement", but unfortunately I've proved that to be false: If I pass in a totally non-existent Site value, gibberish, the code still doesn't err until all the way at the INSERT statement.

The name of my list is definitely Isaac Test Excel To Sharepoint

The site I am passing is like this, with me sanitizing this by replacing some text with "text": (I've tried all 3 of these):
  1. strSharepointSite = "https://text.text.text.com"
  2. strSharepointSite = "https://text.text.text.com/sites/text"
  3. strSharepointSite = "https://text.text.text.com/sites/text/_layouts/15/start.aspx#/"
Full code:
Code:
Sub Upd2KPIMember_SP()
    Dim cnt As ADODB.Connection
    Dim mySQL As String
    Dim strSharepointListID As String, strSharepointSite As String
  
    'https://community.nintex.com/t5/Community-Blogs/Obtaining-a-list-id-in-SharePoint-2010-or-2013/ba-p/77664#:~:text=Navigate%20to%20the%20list%20and%20click%20List%20Settings.,Guid%20Format%20with%20URL%20encoding).
    'list ID from sharepoint URL:
    '   %7B3404D534%2D10CB%2D4F53%2DBB9D%2D37F5612155F1%7D
    'list ID after replacing as follows:
    '   %7B3404D534%2D10CB%2D4F53%2DBB9D%2D37F5612155F1}
    strSharepointListID = "{3404D534–10CB–4F53–BB9D–37F5612155F1}"
    strSharepointSite = "[sanitized for AWF post]"
  
    Set cnt = New ADODB.Connection
    With cnt
        .ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=" & strSharepointSite & ";LIST=" & strSharepointListID & ";"
        .Open
    End With

        mySQL = "insert into [Isaac Test Excel To Sharepoint] (column1,column2) values ('col1_val1','col2_val1');"
        cnt.Execute (mySQL)

    If CBool(cnt.State And adStateOpen) = True Then cnt.Close
    Set cnt = Nothing

End Sub

I'm also fairly sure the SQL syntax is good, because the code DID tell me when it was wrong--When at first I used INSERT TABLE instead of INSERT INTO TABLE.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 23:59
Joined
Jan 14, 2017
Messages
18,186
Unable to help further as I have never used Sharepoint
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:59
Joined
Oct 29, 2018
Messages
21,358
I'm using ADO because I want to write to a Sharepoint list using VBA in Excel.
Right now I am getting "The Microsoft Access database engine could not find the object 'Isaac Test Excel To Sharepoint',
Sorry to barge in... I just find that comment interesting. You're using Excel but the error says Access? :)
 

Isaac

Lifelong Learner
Local time
Today, 16:59
Joined
Mar 14, 2017
Messages
8,738
Sorry to barge in... I just find that comment interesting. You're using Excel but the error says Access?
Correct.
I believe this is because of specifying ACE as the provider.

ado error.jpg
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:59
Joined
Feb 19, 2013
Messages
16,553
only thing I find ADO useful for (over DAO) is disconnected recordsets. One of the disadvantages of ADO in access is that you have to write your own filter/sort routines since the built in access ones only work with DAO.
 

Isaac

Lifelong Learner
Local time
Today, 16:59
Joined
Mar 14, 2017
Messages
8,738
only thing I find ADO useful for (over DAO) is disconnected recordsets. One of the disadvantages of ADO in access is that you have to write your own filter/sort routines since the built in access ones only work with DAO.
This is independent from Access, though. I'm running this in Excel, going straight to a Sharepoint list. Can DAO do that? I initially researched ways to connect directly to Sharepoint from VBA and only found ADO examples, although there is precious little information about the entire subject.

Another place I have generally preferred ADO is going directly from Excel VBA to SQL Server, although I cannot now remember why.

PS - Normally, [i.e., in Access context], I do use DAO exclusively.
 

Isaac

Lifelong Learner
Local time
Today, 16:59
Joined
Mar 14, 2017
Messages
8,738
UPDATE:

After only a year, I got this problem solved. :p

Amazingly, unbelievably, the change that was required to make this code work was to totally REMOVE any single quotes OR brackets from the list name - AND, to use the list name - not the GUID (credit to Keshav Sharma here, they weren't 100% correct, but half way, and got me focused on that line of code and inspired to believe that there MUST be a simple answer to this). Thinking back, I guess this all makes sense to me. I should have known that those semicolon delimiters in the Connection String are serious business - and no additional delimiters are needed - not even with objects w/spaces in the name!

The final block of code, that works perfectly and near-instantly to insert values directly from EXCEL into SHAREPOINT (but, I believe, does require Access to be installed due to ADO's ACE reference...tho I'm not 100% sure about that), is:

Code:
Sub Upd2KPIMember_SP()
    'https://www.connectionstrings.com/questions/100002/vba-update-sharepoint-inserts/
    Dim cnt As ADODB.Connection
    Dim mySQL As String
    Dim strColumn1 As String, strColumn2 As String, strColumn3 As String

    Set cnt = New ADODB.Connection
'               trying this new thing due to response on SO: https://stackoverflow.com/questions/62685490/ado-connection-in-vba-unable-to-insert-to-sharepoint-list?noredirect=1#comment120355095_62685490
    With cnt
        .ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://redacted.ad.redacted.com/sites/sitenameredacted/;LIST=Isaac Test Excel To Sharepoint;"
        .Open
    End With

        mySQL = "insert into [Isaac Test Excel to Sharepoint] ([Column1],[Column2]) values ('col1_val1','col2_val2');"
        cnt.Execute (mySQL)

    If CBool(cnt.State And adStateOpen) = True Then cnt.Close
    Set cnt = Nothing

End Sub

Sure hope this can help someone else some day. Automating data inserts from Excel to Sharepoint makes for another significant tool in the toolbox!

PS - If anyone knows about ACE vs. ACCESS vs. ADO, and can provide their expert opinion on whether or not this code ought to work even if MS Access is not installed, let me know please. That would be good to understand as well. (I.E. is Ace one of those things that an Office Install - one which officially did not install Access - might include? Or no?)
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 16:59
Joined
Mar 14, 2017
Messages
8,738
Why bother early binding at all?
Oh - I have no reason to. I just wanted to satisfy my need to understand why there were SO many options in the References dialogue in Excel. It almost seemed like MS was just throwing anything they could think of in that dialogue box, rather than showing me which one I had on my computer. Or, that I had them all, which would lead me back to the same question - why.

Or at least, that was my original question. The thread evolved into something more serious, which was, binding notwithstanding, the code simply didn't work. Getting it to work was today's major victory.

You raise a good point though, I'll probably change it to late binding before any type of deploy.
 

Users who are viewing this thread

Top Bottom