Correct VBA Reference for ADODB (1 Viewer)

Isaac

Well-known member
Local time
Today, 03:31
Joined
Mar 14, 2017
Messages
1,497
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, 06:31
Joined
Oct 20, 2018
Messages
3,393
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

Well-known member
Local time
Today, 03:31
Joined
Mar 14, 2017
Messages
1,497
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

CID
Local time
Today, 11:31
Joined
Jan 14, 2017
Messages
13,602
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, 06:31
Joined
Oct 20, 2018
Messages
3,393
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

CID
Local time
Today, 11:31
Joined
Jan 14, 2017
Messages
13,602
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

Well-known member
Local time
Today, 03:31
Joined
Mar 14, 2017
Messages
1,497
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

CID
Local time
Today, 11:31
Joined
Jan 14, 2017
Messages
13,602
Unable to help further as I have never used Sharepoint
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:31
Joined
Oct 29, 2018
Messages
11,105
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

Well-known member
Local time
Today, 03:31
Joined
Mar 14, 2017
Messages
1,497
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, 11:31
Joined
Feb 19, 2013
Messages
12,236
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

Well-known member
Local time
Today, 03:31
Joined
Mar 14, 2017
Messages
1,497
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom