Access 2013 Web App How do I get my data into SAS? RRS feed

  • Question

  • Hi,

    I'm creating an Access 2013 web app, which has about ten related tables.  It looks great and runs reasonably well, however I can't figure out how to get the data out to use in Excel or SAS.  

    For Excel, I have followed the blog post 'Visualize your Access 2013 web app data in Excel...' but I am not getting to 'SQL Server Native Client'.  What connection or software am I missing?

    Also, is there a limit of data that can be exported to Excel?  If not, I'll just connect SAS to Excel.  However, it would be better to make the connection from SAS directly to the SQL server.

    Thanks for your help!

    Tuesday, June 11, 2013 9:43 PM


All replies

  • Hi Kim,

    What do you mean about "I am not getting to 'SQL Server Native Client' "?  If you mean that you can't see it in the Data Link Properties dialog, please refer to the following link:

    Installing SQL Server Native Client


    "Microsoft SQL Server Native Client 11.0 is installed when you install SQL Server 2012 or the SQL Server tools. You can also get sqlncli.msi from the SQL Server Feature Pack web page."

    After that, you may follow the instruction of the blog: http://blogs.office.com/b/microsoft-access/archive/2013/01/22/visualize-access-data-in-excel.aspx

    Good day.

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Dummy yoyo Friday, June 21, 2013 10:11 AM
    Friday, June 14, 2013 6:46 AM
  • If you want to control Excel from Access, do this:

    Option Compare Database
    Option Explicit ' Use this to make sure your variables are defined
    ' One way to be able to use these objects throughout the Module is to Declare them
    ' Here and not in a Sub
    Private objExcel As Excel.Application
    Private xlWB As Excel.Workbook
    Private xlWS As Excel.Worksheet
    Sub Rep()
    Dim strFile As String
    strFile = "C:\Users\Excel\Desktop\YourExcelFile.xls"
    ' Opens Excel and makes it Visible
    Set objExcel = New Excel.Application
    objExcel.Visible = True
    'Opens up the Workbook
    Set xlWB = objExcel.Workbooks.Open(strFile)
    'Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet.
    Set xlWS = xlWB.ActiveSheet
    'Set xlWS = xlWB("Sheet2")
    With xlWS ' You are now working with the Named file and the named worksheet
    End With
    'Do Close and Cleanup
    End Sub
    Sub ControlExcelFromAccess()
    ' No reference to a type library is needed to use late binding.
    ' As long as the object supports IDispatch, the method can
    ' be dynamically located and invoked at run-time.
    ' Declare the object as a late-bound object
      Dim oExcel As Object
      Dim strFile As String
      strFile = "C:\Users\Excel\Desktop\YourExcelFile.xls"
      Set oExcel = CreateObject("Excel.Application")
    ' The Visible property is called via IDispatch
      oExcel.Visible = True
      Set xlWB = oExcel.Workbooks.Open(strFile)
    'Call Ron's code here . . .
    Set oExcel = Nothing
    End Sub

    If you want to control Access from Exce, do this:

    Global oApp As Object
    Sub OpenAccess()
       Dim LPath As String
       Dim LCategoryID As Long
       'Path to Access database
       LPath = "C:\Users\Excel\Desktop\Coding\Microsoft Access\Northwind.mdb"
       'Open Access and make visible
       Set oApp = CreateObject("Access.Application")
       oApp.Visible = True
       'Open Access database as defined by LPath variable
       oApp.OpenCurrentDatabase LPath
       'Open form of interest
       oApp.DoCmd.OpenForm "Form1"
    End Sub
    ********  *******  ********  *******  ********  *******  ********  *******  
    Sub AccessTest1()
          Dim A As Object
          Set A = CreateObject("Access.Application")
          A.Visible = False
          A.OpenCurrentDatabase ("C:\Users\Excel\Desktop\Coding\Microsoft Access\Northwind.mdb")
          A.Application.Run "ExportToExcelTest"
    End Sub
    If you need to run the code from a Macro, it needs to be a Public Function (rather than Sub), and it needs to be in a standard module (not a Form, Report or Class module).

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, June 16, 2013 3:01 PM
  • @ryguy72: you missed the point that the OP is using a Web app. No VBA.

    -Tom. Microsoft Access MVP

    Sunday, June 16, 2013 9:56 PM
  • The connection string to your web app data is available in Access: File > Info > Connections.

    Using that, you could probably directly connect SAS or Excel to it, or even create an Access client app and connect to it.

    -Tom. Microsoft Access MVP

    • Marked as answer by Dummy yoyo Friday, June 21, 2013 10:11 AM
    Sunday, June 16, 2013 10:00 PM
  • Whoops!!  I didn't realize that!  I have actually never used Access on the web.  I have Office 2013.  I thought you needed Office 365 to have web capabilities in Access.  Is that right?  How does Office 2013 even work with the web?  I tried to get my version to publish to the web, like a SharePoint site, and it didn't work at all.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, June 16, 2013 10:50 PM
  • I had same problem and installed native client.  For the next dialog box, I did not find information on Access info panel for #2(a) server SPN or (b) login info.

    Sunday, November 3, 2013 1:18 AM
  • I see that and thanks for the info.  How would I gain access to this data.  When I tried, it denies the login.  Since I don't know what to use,  I assume I have to get an account from O365.

    I have a E3 account.  Is this available or is this another fee service?

    Phil Funderburk

    Friday, November 8, 2013 2:54 PM