0

I have an Excel Macro which extracts data from another file, then reformats said data, and finally adds said data to an Access database file as new records. My script works perfectly with regards to a traditional desktop .accdb Access database file, but I am looking to transition to a SharePoint Access Web App.

Currently, my Excel VBA script looks something like this:

dbPath = "C:\Some\Path\MyDatabase.accdb"
Set wsDAO = DBEngine.Workspaces(0)
Set dbDAO = wsDAO.OpenDatabase(dbPath)
Set rsDAO = dbDAO.OpenRecordset(TableName)
RecordCount = UBound(ExportValues)
FieldCount = rsDAO.Fields.Count

ReDim ExportArray(1 To RecordCount, 1 To n)
For r = 1 To RecordCount
    ExportArray(r, 1) = Stuff
    .
    .
    .
    ExportArray(r, n) = StuffN
Next r

For records = 1 To RecordCount
    If records = 1 Then
        rsDAO.AddNew
    Else
        rsDAO.Update
        rsDAO.AddNew
    End If

    For n = 1 To FieldCount - 1
        rsDAO.Fields(n).Value = ExportArray(records, n)
    Next n
Next records

rsDAO.Update

rsDAO.Close
dbDAO.Close

How can I modify this to access and update an Access Web App? I have no idea where to start. I've looked all around, but haven't been able to find any good information, especially given the new SharePoint that was recently rolled out by Microsoft. Thanks!

Community
  • 1
  • 1
margentieri
  • 31
  • 1
  • 1
  • 4

1 Answers1

0

As you found out, Excel and Access play very nicely together when you use VBA. I don't think you can transport your setup to SharePoint. Basically, VBA doesn't run in a web-based environment; it is for your desktop, or server machine. Last year I tried to do the same thing you described. I never got it working. I just downloaded the Access DB, ran the VBA to move data from Excel to Access, then sent the DB back to SharePoint (check-in). I think that's your only option. I'd be curious to see if someone here posts back to prove me wrong.

ASH
  • 20,759
  • 19
  • 87
  • 200
  • That's unfortunate. I was hoping I could enter a URL path and some user credentials into my script and be able to write directly to the Access Web App. Thanks for your input. – margentieri Jan 31 '17 at 18:05