Excel database connection over the web

[ChristineJ]

I have an Excel spreadsheet that submits and retrieves data from an Access database on my local computer using macros on buttons.

I would like to expand this so that anyone who has this spreadsheet can do the same over the web - submit to and retrieve from a database (it does not have to be Access) hosted by a web provider.

I'd like to be pointed in the right direction as to what to search for to learn how this can be accomplished.

 

[Zack Barresse]

Hi there,

You can connect to data sources online, but you must have it online first to start. That would be like the egg before the chicken, err, chicken before the egg, no, wait... You get my drift though I hope. It depends on where the data source is online. You can create a web database through Access, which can be stored in a OneDrive account (Access 2010 and 2013 create web databases differently, the latter being a SharePoint list, so it depends on what version of Office you're using).

Likewise you could do this with data tables located in a website, such as an ASP.NET data source. You could also use a SQL database, MySQL database, or really any database which is in the cloud (e.g. online).

 

[ChristineJ]

Thanks so much for the reply. I am so glad to find someone who understands my question!

I am currently working with Access 2013, but I do have a copy of Access 2010 on an older computer if needed.

I'm including the code that submits a range of cells (Sub PushTableToAccess1) to an Access database and then retrieves data that was submitted (Sub DownloadRegion1). Each submission is considered a "Take" and the data is retrieved by selecting a "Take" and identifying with a user ID in cells in the spreadsheet. Although the macro name is PushTableToAccess1, the range of data is not actually in an Excel table; just in cells. The database contains just one table that has no data in it until users start submitting from the spreadsheet. Ultimately I'll have 75 sheets in the spreadsheet and 75 tables in the database for 75 different assignments.

The spreadsheet is uniquely designed with formulas, functions, and macros for an accounting course I teach. I do have a SharePoint site at the university that I can use but will have to check whether I can give students permissions to submit data into the database. I would prefer to use OneDrive if possible - can I give other people permissions to submit data to a database on my OneDrive account?

My goal is for students to only see the spreadsheet (database is hidden to them) and to open it, work on it to submit and retrieve, and close it. The spreadsheet is programmed not to save; it is just an interface for them to use to complete and view assignments.

My question now is whether I could use the same code below with a different path to accomplish this on OneDrive or SharePoint with a web database. Completely new territory for me! Thanks for sharing your expertise.

Option Explicit
Const TARGET_DB = "C:\Users\Christine\Desktop\Project\Accounting.accdb"




Sub PushTableToAccess1()


    Dim cnn As ADODB.Connection
    Dim MyConn
    Dim rst As ADODB.Recordset
    Dim i As Long, j As Long
    Dim Rw As Long
    
    Sheets("EX1").Activate
    Rw = Range("P65536").End(xlUp).Row


    Set cnn = New ADODB.Connection
    MyConn = TARGET_DB
    
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open MyConn
    End With


    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:="Exercise1", ActiveConnection:=cnn, _
             CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
             Options:=adCmdTable
    
    
    For i = 4 To Rw
        rst.AddNew
        For j = 16 To 22
            rst(Cells(3, j).Value) = Cells(i, j).Value
        Next j
        rst.Update
    Next i
    
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    


End Sub
Sub DownloadRegion1()
If Range("C7") = "No previous takes" Then Exit Sub
Range("X4:AE8").ClearContents


    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim MyConn
    Dim i As Long
    Dim ShDest As Worksheet
    Dim sSQL As String
    
    Set ShDest = Sheets("EX1")


    sSQL = "SELECT * FROM Exercise1 WHERE [ID] ='" & ThisWorkbook.ActiveSheet.Range("P4").Value & "' AND [Take] = " & ThisWorkbook.ActiveSheet.Range("C7").Value
  
    Set cnn = New ADODB.Connection
    MyConn = TARGET_DB
    
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open MyConn
    End With


    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:=sSQL, ActiveConnection:=cnn, _
             CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
             Options:=adCmdText
    
        i = 0
    With Range("X3")  'Header of output data table
        For Each fld In rst.Fields
            .Offset(0, i).Value = fld.Name
            i = i + 1
        Next fld
    End With
     
    'First cell of output data table
    Range("X4").CopyFromRecordset rst


    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing


End Sub