Create DSNLess Connection With VBA

Question

I am using SQL Server to create a view from a Postgresql server database table. Then I am using Access VBA to create a DSNLess connection to the view, which executes as it should. The problem is the table is linked as read only. I read that in order to make it "writable" I need to define the primary key, but a view can not have a primary key. So what should I alter in order to make this linked table be writable?

This is my SQL Server syntax to create the view

ALTER VIEW [dbo].[Maestro]
AS
SELECT        *
FROM            OPENQUERY(PostegresqlConnection, 
                     'SELECT
                      empid
                      ,empaddress
                      ,empphone
                      From sentry1
                      where empstatus = ''Active'' ')
GO

And this is the Access VBA I am using to link in the table

Option Compare Database
'//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
Public Function AttachDSNLessTable()
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String

'Alter these variables to change any settings
Dim stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, stUsername As String, stPassword As String

stLocalTableName = "LinkedMaestro"
stRemoteTableName = "Maestro"
stServer = "SSMS\Server1"
stDatabase = "TestDB"
stUsername = "Blue"
stPassword = "NotRealPassword"

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

If Len(stUsername) = 0 Then
    stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
    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

In the create view syntax the field empid is the primary key of the postgresql table. What must I alter (or do) in order for this table to be writable through a DSNLess connection?

Create DSNLess Connection With VBA

0 Answers0