Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: Open a SQL View in MSAccess

    Does anyone know how I would open a SQL View in MSAccess?

    Here's the code I have so far:
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim sql As String
    conn.Open "Driver={SQL Server};" & _
    "Server=DOCFINDB;" & _
    "Database=DocFinityDB;" & _
    "UID=;" & _
    "PWD=;"
    sql = "Select * from TestTable"
    rst.Open sql, conn, adOpenKeyset, adLockReadOnly
    rst.movefirst
    msgbox rst!Field1
    cnn.close
    set cnn = nothing

    It's been a while since I've created a DSN-less connection and for some reason I can't even get the code above to work. Ultimately though, I want to open a SQL Server View instead of the sql as the recordset. Is it possible to open a SQL Server View in MSAccess modifying the code above? I've been told I can do this...

    sql = "Select * from VIEWName"

    Will this work?
    Last edited by pkstormy; 05-22-07 at 18:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Paul

    Quote Originally Posted by pkstormy
    Is it possible to open a SQL Server View in MSAccess modifying the code above?
    From a relational point of view (no pun intended) a view is practically the same as a table (in fact I think that is the meat of one of Codd's rules... but I might be wrong). Anyway, the point is apart from updates & inserts (where you might need some T-SQL trickery) you treat a view in exactly the same way as you would treat a table.

    Connection strings:
    http://www.connectionstrings.com/
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Disregard. Dumb question. I got it to work. Just had a little bit of a permissions on SQL Server problem.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for the response pootle flump! I should've known the answer but you gave me some insight.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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