CODEWITHSUNDEEP
sql-serverms-accessvbauserformlinked-tables
MDSN
MDSN

Reputation: 21

My userform turns blank because I can't edit linked table connection

I am currently developing an access application, which dynamically builds userforms through a VBA module. In order to create the forms, I need data from an MS SQL DB. I collect this data through linked table connections, which are established upon initiation of the entire application by using the AttachDSNlessTable method:

https://support.microsoft.com/en-us/kb/892490

The userinputs are collected in the userforms, and subsequently inserted into some other linked tables (:ResultTables) on the same sql-server.

MY PROBLEM IS that VB-based linked table connections does not let me insert my userform inputs to the resulttables. When I view the form in FormView, it is instead completely blank. All the controls are nonetheless visible in DesignView.

The problem doesn't exist when I manually create the linked tables, apparently because I can choose an index which then allows me to fill new rows in the table.

I am completely sure that the user has the right access to the sql-server to perform update/insert/delete procedures. I have tried to index the table on the server, but the index is not inherented in Access. Once the table is linked, I can't edit the connection. I have also tried the approaches suggested for blank userforms:
https://support.microsoft.com/en-us/kb/93261

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
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
    CurrentDb.TableDefs.Refresh
    'td.CreateIndex (ID2)
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:

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

End Function

Upvotes: 2

Views: 72

Answers (2)

MDSN
MDSN

Reputation: 21

thanks for your reply! In the end I created a create-sql-statement for the primary key. It works smoothly: 

SQL_PrimaryKey = "CREATE INDEX PrimaryKey ON " & QMT_QMTmonitoring_TransactionFact & _
                 " (ID) WITH PRIMARY" 
Database_Link.Execute SQL_PrimaryKey

Upvotes: 0

Andre
Andre

Reputation: 27634

Linking an SQL Server table should detect the Primary Key automatically. It works for me using DoCmd.TransferDatabase TransferType:=acLink.

Apparently it doesn't work for the CurrentDb.CreateTableDef method.

So I suggest using this command to link the tables:

DoCmd.TransferDatabase TransferType:=acLink, _
    DatabaseType:="ODBC", _
    DatabaseName:=stConnect, _
    ObjectType:=acTable, _
    Source:=stRemoteTableName, _
    Destination:=stLocalTableName, _
    StructureOnly:=False, _
    StoreLogin:=True

I have written more about creating DSN-less linked tables here, but most of it deals with linking SQL Server Views, where the PK isn't automatically detected.

Upvotes: 1

Related Questions