Trying to access database in MS Teams (1 Viewer)

[atzdgreat]

OBJECTIVE: to access database in MS Teams using excel vba macro.
CODE:

Dim DataStr As String
    Dim connStr As String
    DataStr = "https://xxxx.sharepoint.com/sites/TRF/Shared Documents/General/database.accdb"

    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataStr & ";" _
            & "Jet OLEDB;Database Password=xxxxxxxxx;"

    ' Create ADODB Connection
    Set conn = CreateObject("ADODB.Connection")

    ' Open Connection
    conn.Open connStr

     'Check if the connection is open
    If conn.State = 1 Then
        MsgBox "Connection opened successfully!"
    Else
        MsgBox "Error opening connection!"
    End If

ERROR: Not a valid filename.

may i know if someone already access database in MS Teams or in Sharepoint using excel vba macro. thank you so much.

 

[theDBguy]

Is converting the database into sharepoint lists out of the question? Just curious...

 

[atzdgreat]

Is converting the database into sharepoint lists out of the question? Just curious...

hi @theDBguy thank you for your reply. i have thoughts on converting my access to sharepoint list but unfortunately i cannot find any solution on connecting sharepoint list using excel vba ado.

 

[theDBguy]

hi @theDBguy thank you for your reply. i have thoughts on converting my access to sharepoint list but unfortunately i cannot find any solution on connecting sharepoint list using excel vba ado.

Here's a sample code from copilot.

Sub SQL_Two_SP_Lists()
    Dim sp_sdbPath As String, sp_sConnect As String
    Dim SP_List_1 As String, SP_List_2 As String
    Dim c As Long
    Dim cnSP As New ADODB.Connection
    Dim rsSP As New ADODB.Recordset

    ' SharePoint site URL
    sp_sdbPath = "https://your_SharePoint_URL_Here/"

    ' Connection string
    sp_sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=" & sp_sdbPath & ";"

    ' List GUIDs
    SP_List_1 = "LIST={List 1 GUID Here}" ' Replace with actual GUID
    SP_List_2 = "LIST={List 2 GUID Here}" ' Replace with actual GUID

    ' Establish a connection to the first List
    cnSP.Open sp_sConnect + SP_List_1

    ' Write the SQL & Establish a connection to the second List as a sub-query using IN
    sSQL = "SELECT A.*, B.* " & _
           "FROM List1 A " & _
           "INNER JOIN (SELECT * FROM LIST IN 'DATABASE=" & sp_sdbPath & ";" & SP_List_2 & "' 'WSS;RetrieveIds=Yes;') B ON A.Cust_ID = B.Cust_ID;"

    rsSP.Open sSQL, cnSP, adOpenStatic, adLockReadOnly ' Change cursor & lock type if needed

    ' Drop the results into an empty worksheet named 'Test'
    For c = 0 To rsSP.Fields.Count - 1
        ThisWorkbook.Sheets("Test").Cells(1, c + 1) = rsSP.Fields(c).Name
    Next
    ThisWorkbook.Sheets("Test").Cells(2, 1).CopyFromRecordset rsSP

    rsSP.Close
    cnSP.Close
End Sub

 

[CJ_London]

Just asked ChatGPT (I accept it doesn't always get it right )

one thing of note is the slope of the slashes which may or may not be relevant

you have
DataStr = "https://xxxx.sharepoint.com/sites/TRF/Shared Documents/General/database.accdb"


ChatGPT has
connString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=\\YourTeamName.sharepoint.com@SSL\DavWWWRoot\teams\YourChannelName\Documents\YourDatabaseName.accdb;"

The full response is:

Yes, you can use ADO (ActiveX Data Objects) in Excel VBA to connect to an Access database stored on Microsoft Teams. ADO provides a way to connect to various data sources, including Access databases, using standard SQL queries.

To connect to an Access database in Teams using ADO, you need to use the appropriate connection string that specifies the path to the database file stored in Teams.

Here's a basic example of how you can connect to an Access database in Teams using ADO in Excel VBA:

vbaCopy code

Sub ConnectToTeamsAccessDatabase()
Dim conn As Object ' ADODB.Connection
Dim rs As Object ' ADODB.Recordset
Dim connString As String
Dim sqlQuery As String

' Replace "YourTeamName" with the name of your Microsoft Teams team
' Replace "YourChannelName" with the name of your channel in Teams
' Replace "YourDatabaseName.accdb" with the name of your Access database file
connString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=\\YourTeamName.sharepoint.com@SSL\DavWWWRoot\teams\YourChannelName\Documents\YourDatabaseName.accdb;"

' SQL query to execute
sqlQuery = "SELECT * FROM YourTableName;"

' Create a new connection object
Set conn = CreateObject("ADODB.Connection")

' Open the connection
conn.Open connString

' Execute the query
Set rs = conn.Execute(sqlQuery)

' Do something with the recordset (rs)

' Close the recordset
rs.Close

' Close the connection
conn.Close

' Clean up
Set rs = Nothing
Set conn = Nothing
End Sub

In this example:

• Replace "YourTeamName" with the name of your Microsoft Teams team.
• Replace "YourChannelName" with the name of your channel in Teams where the Access database is stored.
• Replace "YourDatabaseName.accdb" with the name of your Access database file.
• Replace "YourTableName" with the name of the table you want to query from the Access database.

Ensure that you have the necessary permissions to access the Teams site and the specific channel where the database file is stored. Also, make sure that the necessary ACE (Access Database Engine) drivers are installed on your machine for connecting to Access databases via ADO.