Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: Open an SQL VIEW in MS Access

    Can someone explain if it is possible, and if so, how to connect an Access Database to SQL view?

    I know how to import and link tables but I'm not happy with this and I'd rather base the access front end on an SQL View already created.

    Christy

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There are several solutions. Here's one where you access the view through a query.:
    Code:
    Public Function OpenSQLView(Connection As String, ViewName As String) As String
    
        Dim qdf As DAO.QueryDef
        Dim strName As String
        
        strName = "qry_" & ViewName
        If DCount("*", "MSysObjects", "name='" & strName & "'") > 0 Then DoCmd.DeleteObject acQuery, strName
        Set qdf = CurrentDb.CreateQueryDef(strName)
        With qdf
            .Connect = Connection
            .SQL = "SELECT * FROM " & ViewName
        End With
        OpenSQLView = strName
        
    End Function
    You call it this way:
    Code:
    Sub TestOpenSQLView()
    
        Const c_Connection As String = "ODBC;DRIVER={SQL Server};SERVER=BSMM-SANDBOX;DATABASE=Sales;Trusted_Connection=Yes;"
        Const c_ViewName As String = "V_Billing_Customers"
        
        Dim strQryName As String
        
        strQryName = OpenSQLView(c_Connection, c_ViewName)
        DoCmd.OpenQuery strQryName
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Thanks so much for this.
    I think I'm almost there but I'm getting a Run-time error '2455'. "You entered an invalid reference to the property 'AllowFullMenus'.

    Any idea what this is about and how to solve?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    which version of Access do you use?
    Have a nice day!

  5. #5
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Sorry. I should have said originally.

    I'm using Access 2010.

  6. #6
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Sinndho... Ignore me totally. I'm attempting to access a view that I created on our live database but using the connection string to the test database!

    I've managed it now.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    OK, I ignore you
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •