ODBC Tables - How do I exclude Views?

[Mike_In_Ga]

I have connected to a SQL Server Db and pulled in all the "tables". The problem is that some of the "table" are actually SQL views. How can I use the Linked table GUI to ull in only actual SQL tables and exclude the SQL views? It seems that the ODBC connector tool treats a SQL view and SQL table as the same object......ot perhaps in the background Access knows the difference but does not show that to the user in the GUI.

I am trying to run a module I created that performs a bunch of UPDATE procedures to the SQL Db and it is failing because it tries to run the procedure on a view and fails. I can even manually delete the views from the Access Db but I dont know how to tell if the linked Objects in the Access Table screen are SQL views or SQL tables. Unfortuneately I dont have access to the server SQL is sitting on to look directly at the source Db.

 

[Banana]

I don't have any experience with MS SQL, but in MySQL, I could just create an account and give it only privileges to open tables, but not the views. Could you ask your DBA to create such account for you?

Also, it should be noted that Views can be updated, as long as we know what the index are.

 

[georgedwilkinson]

Some DBAs insist on having a naming standard in MS SQL for their objects. If you're lucky enough to have a setup like that, they typically name views with either a "v" or "vw" prefix. Stored procedures are typically "sp", Indexes are usually either "i" or "indx" or "ndx".

You could connect to the database with ADOX and ask for the information. I've never done that, so you'd need to look up how to use the ADOX library.

There are also some hidden tables in SQL Server that give you all kinds of information about the objects in the database. You can query those directly, if you have the rights.

 

[LPurvis]

Indeed - even if permissions weren't granted to the System tables (sysobjects is all you really need) it would be pretty easy for a DBA to set up an SP which returned the information you needed.
Essentially:
SELECT * FROM sysobjects WHERE xtype = 'U'
Or if permissions exist you can use that SELECT statement from the local db to iterate the table's list yourself.

ADOX is one option and so is good old fashioned DAO (and since you'll be using it to create the table links more than likely - why not?

I'll maybe knock something together...

 

[LPurvis]

More or less - something like the following.
(As mentioned - if permissions prevented then an SP would be a simple option).

Call it something like
LinkToAllSQLTables "YourServerName","DBName",False,"UserLogin","UserPassword"
or
LinkToAllSQLTables "YourServerName","DBName",True

Option Compare Database
Option Explicit
 
Dim dbLocal As DAO.Database
 
Public Sub LinkToAllSQLTables(strServer As String, strDBName As String, Optional blnIntegratedSecurity As Boolean, _
                Optional strUserName As String, Optional strPassword As String, Optional strPrefix As String)
     
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    
    Set dbLocal = CurrentDb
    Set db = OpenDatabase("", False, False, "ODBC;Driver={SQL Server};DATABASE=" & strDBName & _
                            ";Server=" & strServer & ";UID=" & strUserName & ";PWD=" & strPassword)
    Set rst = db.OpenRecordset("SELECT Name FROM sysobjects WHERE xtype = 'U'", dbOpenSnapshot)
    
    With rst
        Do Until rst.EOF
            CreateLinkedODBCTable rst("Name"), strServer, strDBName, blnIntegratedSecurity, strUserName, strPassword, strPrefix & rst("Name")
            .MoveNext
        Loop
        .Close
    End With
    db.Close
    Set rst = Nothing
    Set tdf = Nothing
    Set db = Nothing
    Set dbLocal = Nothing
    
End Sub
 
Public Sub CreateLinkedODBCTable(strTableName As String, strServer As String, strDBName As String, _
                            Optional blnIntegratedSecurity As Boolean, _
                            Optional strUserName As String, Optional strPassword As String, _
                            Optional strAlias As String)
'Creates a SQL Server ODBC linked table based on provided connection properties
'Usage example: CreateLinkedODBCTable "tblTableName", "ServerName", "Northwind", True
On Error GoTo errHere
    Dim db As Database
    Dim tdf As TableDef
    Dim prp As DAO.Property
    Dim strConnect As String
    
    strConnect = "ODBC;Driver={SQL Server};Server="
    strConnect = strConnect & strServer & ";Database=" & strDBName
    
    If blnIntegratedSecurity Then
        strConnect = strConnect & ";Trusted_Connection=Yes"
    Else
        If Len(strUserName) > 0 Then
            strConnect = strConnect & ";UID=" & strUserName
        End If
        If Len(strPassword) > 0 Then
            strConnect = strConnect & ";PWD=" & strPassword
        End If
    End If
    Set tdf = dbLocal.CreateTableDef()
    With tdf
        .Name = IIf(Len(strAlias) > 0, strAlias, strTableName)
        .Connect = strConnect
        .SourceTableName = strTableName
    End With
    dbLocal.TableDefs.Append tdf
    
ExitHere:
    Set tdf = Nothing
    
    Exit Sub
    
errHere:
    MsgBox "Error " & Err & vbCrLf & Err.Description
    Resume ExitHere
End Sub

 

[dallr]

Leigh gave you a good solution. What I am providing as an alternative solution which works more in reverse.
Step 1
Let your DBA create a view within your sql server which lists all the views within your specified database. example.

use <YourDatabaseName>
go

CREATE VIEW vw_sysviews
AS
SELECT [name]AS ViewName 
FROM sys.views

Step 2
Link your access database to the server using ODBC.

Step 3
You can then run the following code which looks at your linked ODBC tables and deletes the ones which are views.

Function RemoveLinkedViews()

Dim tbf As DAO.TableDef
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strView As String

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT ViewName FROM dbo_vw_sysviews")

Do Until rst.EOF
    strView = "dbo_" & rst.Fields("ViewName").Value
    
    For Each tbf In CurrentDb.TableDefs
    
        If (tbf.Attributes = dbAttachedODBC) _
            And tbf.Name <> "dbo_vw_sysviews" Then     ' only use tables that are linked via the an ODBC connection
                                                       ' do not use the table that holds all the view names
           If tbf.Name = strView Then
                Debug.Print "Deleted Linked View: " & tbf.Name
                db.TableDefs.Delete tbf.Name  ' delete the linked view here
           End If
           
        End If
    
    Next tbf
    rst.MoveNext
Loop

db.TableDefs.Refresh   ' refresh  to remove deleted tables.

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Function

Of course the only remaining view will be vw_sysviews (prefixed dbo_vw_sysviews). Which you can delete manually.

Dallr

 

[Banana]

Another thing you could do is

!NOT RECOMMENDED! !NO WARRANTY! !UNTESTED!

Edit the registry's value- there is an entry in Jet 4.0 for "AttachODBC" or something like that that lists something like "TABLE, VIEW,"- You could remove the 'VIEW' from the registry.

Note: There is a slight chance that your computer may die a horrible death, so Cavaet Emporer!

 

[dallr]

Welll that is extremely risky!!

Secondly, are you sure the registry value will note if it is a view. Remember the link does not hold any details.

dallr

 

[Banana]

Didn't bother to test this, but saw that listed.

From what I understand that would work when attaching a new object; likely not the case with already attached objects.

 

[datAdrenaline]

Hello Mike,

Your initial post makes me think you are using the table linker from Access, if so, why not just NOT select the views? ... do you know which ones are views or base tables, or are you doing this a lot? ... not that you really need to justify what you're doing to me, but I just wanted to make sure this task was not being "over analyzed" per se.

You have definately recv'd some good advice from the thread participants for automating this task. However, my recomendation would lean towards something like Leigh has put together, simply because you (or someone else) are not required to maintain information regarding the views that are contained in the SQL Server database.

In addition, I wanted to bring some definitions into the mix to learn why views are listed in the available tables. The SQL Server (or ANSI 92) world, definitions are like this:

A table is a multiset of rows. A table is either a base table, a viewed table, or a derived table. In other words anything that returns records (aka: a recordset)! A View, a Stored Procedure, or a 'base table' all return records. In the Access/JET world, these are analogous to a Table, Query, and Recordset object variables. So ... in general any set of records, whether they be derived or from a base table is called a 'Table'. That is why SQL Server VIEWS are included in the list of available Tables in Access's table linker.

From ANSI 92:
--------------
base table: A base table is a named table defined by a <table definition> that does not specify TEMPORARY.

derived table: A derived table is a table derived directly or indirectly from one or more other tables by the evaluation of a <query expression>. The values of a derived table are derived from the values of the underlying tables when the <query expression> is evaluated.

viewed table: A viewed table is a named derived table defined by a <view definition>. A viewed table is sometimes called a view.

-----

With that information and the ADO object model, we have what is called "Schema Recordsets". You can use the schema recordsets to extract information from an ADO connected database.

To get the names of the base tables of a SQL Server database into a Forward Only record, you can use the core line of:

Set rst = cnn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))

where rst is an ADODB recordset; cnn is an ADODB connection to the database.

The recordset will have 4 columns in it (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE). The Array() argument of the core VBA line is the Filter to apply to the recordset. As you notice I have set the filter to show only those records with "TABLE" as the value for the "TABLE_TYPE" column. IF that filter is NOT specified, the recordset will include VIEWs too.

There are many "Schema Recordsets" ... another one that may be useful in this circumstance is the adSchemaViews schema recordset. This can be used in a loop to see if something is a VIEW, if it is, then don't link to it.

Many options exist. I would be glad to expand on the use of the schema recordsets if you wish, but Leighs solution should work well for you, and in all honesty my "fleshed out" solution with the schema recordsets would be similar as it would probably only vary on how to get the list of base tables.

Hope this helps!!

 

[dallr]

Heading in a different direction now.

Mike i noticed you said this

I am trying to run a module I created that performs a bunch of UPDATE procedures to the SQL Db and it is failing because it tries to run the procedure on a view and fails.

What updates are you talking about here? Since the data is stored in the server why not use a stored procedure executed from Access to hand this?

Dallr

 

[LPurvis]

and in all honesty my "fleshed out" solution with the schema recordsets would be similar as it would probably only vary on how to get the list of base tables.

Believe me matey... I felt a bit weird obtaining server based info without using ADO lol. Thought I would go purely DAO just for consistency with the associated linking table procedure methodology.

 

[Mike_In_Ga]

Heading in a different direction now.

Mike i noticed you said this
What updates are you talking about here? Since the data is stored in the server why not use a stored procedure executed from Access to hand this?

Dallr

Easy answer. Because I am not a programmer and get by with access because the GUI wrotes the code for me. If I had to write the code myself it would take me a week and if I went that route Id just do it directly on SQL server. Someday Ill learn VB well enough so that something that takes you guys 5 minutes doesnt take me two days