Link a remote table read-only

[MasterRacker]

I am building an application for reporting and ad-hoc querying that links to the JET backend of a COTS application. I want to prevent any possibility of accidentally modifying any data and connect tables read-only.

From what I can find, the .Attributes method can't be modified and I can't seem to find any info on a read-only connect string. Can I do this?

Public Function LinkTable(dbFQN As String, tbName As String)
On Error GoTo Err_None
   
Dim result As Integer
Dim db As DAO.Database
Dim td As DAO.TableDef
    
    result = SysCmd(SYSCMD_SETSTATUS, "Linking Table: " & tbName)
    
    Set db = CurrentDb()
    db.TableDefs.Delete tbName      ' Remove Existing Table (maybe check first?)

On Error GoTo Err_Handler
   
    Set td = db.CreateTableDef(tbName)
    td.Connect = ";DATABASE=" & dbFQN
    td.SourceTableName = tbName

    db.TableDefs.Append td        ' create table link
    LinkTable = 0

Exit_Here:
    Exit Function

Err_None:
    If Err = 3265 Then Resume Next  ' table not found

Err_Handler:
    If Err = 3011 Then              ' Can't find table
        Application.Echo True
        MsgBox "Table " & tbName & " not found in " & dbFQN
    ElseIf (Err <> 3044) And (Err <> 3024) Then  '3044 = invalid path, 3024 = can't find DB
        Application.Echo True
        MsgBox "LinkTable: " & Error$(Err)
    End If

    LinkTable = Err
    Resume Exit_Here
End Function

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[RoyVidar]

The one thing that's supposed to work, is to use ULS on the back-end table (User Level Security), and set Read Only permissions. However, ULS is deprecated in 2007 version (with accdb/accde format, can still be used with mdb), so it's on it's way out.

Here's a thread from CDMA on that question

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/171598b8d4bf74e8/

Check also out Rick Brandt's suggestion here

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/4fb4f8ba169e9ff5/

, of using queries referring remote tables and setting RecordSetType property to Snapshot

Roy-Vidar

 

[MasterRacker]

I can't modify the back-end at all since it is part of a commercial app.

You're second link may do it though. I would essentially be creating read-only views. The user of my front end could still have full access to the query designer, but it would be pretty safe.

I'm wondering about performance, but I'll certainly test it out.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]