DSN-Less connection is read only

**Jim Doherty** · May 6 '10, 04:33 PM

Originally posted by winshent

I have used the VBA script 'AttachDSNLessT able' from the MS website to connect to my db in SQL Server 2000.

http://support.microsoft.com/kb/892490

Code:

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

    On Error GoTo AttachDSNLessTable_Err

    Dim td As TableDef
    Dim stConnect As String

    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next

    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If

    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)

    CurrentDb.TableDefs.Append td

    AttachDSNLessTable = True

    Exit Function

AttachDSNLessTable_Err:

    AttachDSNLessTable = False

    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

I have full permissions with Windows security and can update data thru Enterprise Manager. My code does not parse in a username so is following the path:

Code:

If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

However, I only have read only access when using linked tables generated via the script from MS.

Can anyone help with this?

Make sure your Primary keys for each table are set on the Server tables without that your tables will be read only in Access

DSN-Less connection is read only

DSN-Less connection is read only

Comment