Re: link table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joe Fallon (jfallon1_at_nospamtwcny.rr.com)
Date: 02/07/04 

Date: Sat, 7 Feb 2004 17:15:28 -0500

FWIW,
I always use Systems DSNs and no Trusted Connections.

I also run re-connect code that uses a single application account with
specific rights.
e.g. SELECT permission only on all tables.

Code sample:
I use this procedure to re-create links to SQL Server.
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
  MsgBox "You must supply a DSN in order to link tables."
  Exit Sub
Else
  strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
  Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
  tdfAccess.Connect = dbsODBC.Connect
  tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
  dbs.TableDefs.Append tdfAccess
  rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
    SysCmd acSysCmdClearStatus
    Exit Sub

Err_LinkSQLServerTables:
    MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
    Resume Exit_LinkSQLServerTables

End Sub

'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

    Dim dbs As Database, tdf As TableDef, I As Integer
    Set dbs = CurrentDb
    For I = dbs.TableDefs.Count - 1 To 0 Step -1
      Set tdf = dbs.TableDefs(I)
      If (tdf.Attributes And dbAttachedODBC) Then
        dbs.TableDefs.Delete (tdf.Name)
      End If
    Next I

    dbs.Close
    Set dbs = Nothing

Exit_DeleteODBCTableNames:
    Exit Sub

Err_DeleteODBCTableNames:
    MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
    Resume Exit_DeleteODBCTableNames

End Sub 

-- 
Joe Fallon
Access MVP
"ll" <lionelluo@hotmail.com> wrote in message
news:%23JESHrY7DHA.2736@TK2MSFTNGP10.phx.gbl...
> Thanks Joe.
>
> I didn't set it for SELECT permission only, but I use db_datareader
Database
> role instead.
> Is it the same?
>
> Yes, my SQL Server uses SQL and Windows authentication. But on the ODBC
> configure window"Microsoft SQL Server DSN configuration", I specify using
> SQL Server authentication
> and specify the LoginID and Password.
> In this case, should use SQL's login ID, am I right?
>
> I found two situations I don't understand:
> 1. On my laptop(windows XP and access XP), when I configure the ODBC as
> "File DSN", the first time when I create the link table, it will pup up a
> SQL Server Login Window, something like:
> SQL Server Login
> Data Sorce: SQLDSN
> (checkbox) Use Trusted Connection
> Login ID:
> Password:
> OK Cancel
> after that, if I select a sql tabke as link table and try to modify it,
will
> get an error: CDBC-- update on a link table 'dbo-Referal' failed.
> [Microsoft][ODBC SQL Server
> Driver][SQL Server]UPDATE permision denied on object "Referral", database
> "Test", owner'dbo'.(#229)
> ...
> But If I save it and reopen the access db, then the sql login pupup window
> won't show.
> and I can modify the link table directly.
> when I point the mouse to the link table, it shows "...Trusted
> Connection..." in a tooltip. Because my login account(to window) is an
> administrator account, so I guess i can make the changes to SQL server.
>
> If I define the ODBC source as a System DSN, then every time the SQL login
> window will pup up.
> Why is that???
>
> 2. On my client machine, I login to the system through Termial Server
> Service, the SQL login widow won't pupup in both case(defined ODBC as
"File
> DSN" or "System DSN", and always using "Trusted Connection". Why is that
> again???
>
> Pls advice.
> Thanks...
>
>
>
> "Joe Fallon" <jfallon1@nospamtwcny.rr.com> wrote in message
> news:%23fUvUQT7DHA.696@tk2msftngp13.phx.gbl...
> > If SQL Server is set for SELECT permission only on the table then there
is
> > NO WAY that Access can make it writable.
> >
> > Therefore, the user who can make the table writable has OTHER
PERMISSIONS
> in
> > SQL Server that allow this.
> > Perhaps the SQL Server uses Windows accounts too.
> > And your user who can edit data has Guest account privileges with
Insert,
> > Update, Delete on that table.
> > -- 
> > Joe Fallon
> > Access MVP
> >
> >
> >
> > "LL" <lionelluo@hotmail.com> wrote in message
> > news:eOGeZrN7DHA.1460@tk2msftngp13.phx.gbl...
> > > Hi, I create a user(user1) only have "read" permission in SQL server.
> and
> > > through ODBC(use user1) and Access to create a link table to access
the
> > sql
> > > server database.
> > > At my clinet's computer, I at least saw two situations:
> > > 1. one of the computer, Access still can modify the link table but
won't
> > > save to sql
> > > 2. can modify the link table and the changes saved to sql.
> > >
> > > I think the normal way shouldn't have permission to WRITE(UPDATE)
table.
> > > what's happening? how to track it down?
> > >
> > > Actually I found when I create the new DB, and build the link talbe,
> it's
> > > ready only.
> > > Only after I copy it to another user's profile folder(C:\Documents and
> > > Settings\if.CDE\desktop)
> > > then the the link table change to can writable?
> > >
> > > and in the sql trace tool, it shows "cde\if" as the NTUserName to
> execute
> > > the query not the connected user(readonly)?
> > >
> > > Any ideas? thanks.
> > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Unexpected Login Screen When Accessing SQL Data Via .NET Intra
    ... >> data and the SQL Server is on the same server. ... >> My problem is that one user has started getting a login popup ... >> right in via Windows Authentication like everyone else and like they ...
    (microsoft.public.sqlserver.connect)
  • Re: Question for Script of users permission.
    ... BUT AS I AM USING THE SCRIPT ON THE MSDE FOR THE LOGIN ... AND PERMISSION OF USERS. ... >> As i have only one SQL server I have check the same on ...
    (microsoft.public.sqlserver.security)
  • Re: cannot acees two databases as owner
    ... it does not matter you are a member of the ... the sysadmin fixed server role or if there is no any other Login which is ... group if it's a domain) and you can login to your SQL Server with, ...
    (microsoft.public.sqlserver.setup)
  • Re: Database security design with ASP.net and form-based authentication
    ... Since you already have forms-based security, why not use a single SQL login ... for all database access? ... data entry, guest/view only, admin, report viewer. ... so I'm using SQL Server authentication. ...
    (microsoft.public.sqlserver.security)
  • Re: Credentials not being passed with remote access
    ... allowed windows authentication to work, ... I had assumed johnx was a domain user rather than a local Windows account. ... johnx was set up as a local login on sql server. ...
    (microsoft.public.sqlserver.security)