Creating Access database within Office 365

Question

I have been asked to create an access database within our Office 365 that will allow individuals to input information into a database via a web form that I can later extract in a report. I'm sure the functionality is there, it has just been a while since I have interacted with Access this in depth . The data being stored in this database won't be incredibly complicated or diverse. Somewhere around ten fields of data will be captured. Is it possible to house this within a sharepoint site? Any tips or recommendations are appreciated!

Answer 1

I would recommend you get this book

http://www.wrox.com/WileyCDA/WroxTitle/Professional-Access-2013-Programming.productCd-1118530837.html

. It deal with Web Apps (AWA) using Access 2013/Office 365

Answer 2

Seeing as how you have Access 365...

This can be done by creating an Access 2013 web app, and then creating a "Reporting Database" on your own PC.

From my understanding, an Access 2013 web app stores data quite a bit differently than with previous versions, using SQL Azure rather than Sharepoint lists.

I've just done it, and in fact have ONE of my databases set up for "read-write" as well, which allows me to run Update Queries on my web data which is stored in the SQL Azure.

Have a peek at 

http://blogs.technet.com/b/the_microsoft_access_support_team_blog/archive/2014/03/24/how-to-make-external-connections-to-an-access-web-app-new.aspx

(They also have a short 4-minute video.)

I have at least one more database (with undoubtedly many more to follow) that I would like to have this same functionality. I'm hoping that it's just a case of being able to save multiple .DSN files...

IMO, Microsoft should have provided either detailed instructions (as mentioned) or even a setup wizard for the "hobby developer" types such as myself.

I could see how the vast majority of users would find this EXTREMELY useful. My thoughts are that you would have all of the traditional desktop features available to create, update, manipulate, data on the desktop and allow users to interact with that data on the web.

This would be a win-win as there is no software requirement for the end user, it's easily and securely shared ... etc.

Answer 3

Thank you both for your replies. I'll have to take a more in depth at all this info in the next couple days.

Answer 4

i have some experience on Adp the web page created by access 2003.

i have found it not easy to handle html codes behind the page.

so i turn to asp.net.

Answer 5

Hi again.

As a bit of an update, and after having read a few other threads, I have found a pretty much "generic" way of accomplishing my needs using VBA and the .Connect property.

("Generic" in the way that this table and form could be copied to multiple "Reporting Databases" with different connection properties.)

I believe that all of the initial steps of creating a "reporting database" and setting/getting the 'read-write', installing the SQL Server Native Client 11.0 driver, etc are still necessary ... but the code (modified from a previous project) below will handle the linked tables. Here is a screenshot of my "tblODBCconnections" table design.

Here is the VBA code, which establishes a read-write connection for each LINKED table: 

Private Sub cmdRefreshLinks_Click()

Dim MyDB As DAO.Database

Set MyDB = CurrentDb

Dim tdf As DAO.TableDef

'The values that are needed to build the ODBC .Connect string

'are stored in a (local) table called "tblODBCconnection"

'The form that calls this code ("frmODBCconnection")is bound to that table.

Dim strConnection As String

strConnection = ""

strConnection = strConnection & "ODBC;Driver={SQL Server Native Client 11.0};"

strConnection = strConnection & "Server=" & Me!MyServer & ";"

strConnection = strConnection & "Database=" & Me!MyDatabase & ";"

strConnection = strConnection & "Uid=" & Me!MyUserName & ";"

strConnection = strConnection & "Pwd=" & Me!MyPassword & ";"

'These variables will be used to display a confirmation message.

Dim intLinkedCount As Integer

Dim intSuccessCount As Integer

'Dim strTable As String

Dim Result As Boolean

Dim Msg As String

Dim CR As String

CR = vbCrLf

DoCmd.Hourglass True

On Error Resume Next

 ' Loop through all tables in database.

 For Each tdf In MyDB.TableDefs

     If Len(tdf.connect) > 0 Then ' If the Connect property is non-empty, the Table Is linked

        intLinkedCount = intLinkedCount + 1 'Get a count of linked tables

        strTable = tdf.Name 'Get the linked table name

        'Debug.Print strTable

        'Debug.Print tdf.connect

        On Error Resume Next

        'Enable the Read-Write connection

        tdf.connect = strConnection

        tdf.RefreshLink 'Confirm that the .Connect property is valid

            If Err.Number <> 0 Then 'If RefreshLink fails...

            Else

            'The re-linking of the table was successful

                        intSuccessCount = intSuccessCount + 1

                        GoTo GetNext

            End If '(for Err <>0)

    End If '(for Len tdf)

GetNext:

Next tdf

MyDB.TableDefs.Refresh

Msg = ""

Msg = Msg & intSuccessCount & " of "

Msg = Msg & intLinkedCount & CR

Msg = Msg & "linked tables have been " & CR

Msg = Msg & "successfully re-linked."

MsgBox (Msg)

Set tdf = Nothing

Set MyDB = Nothing

DoCmd.Hourglass False

End Sub