Linked tables appear #deleted 1

[humpydumpy2000]

Good day, I have a strange problem linking mariadb to msaccess. I had been relinking tables dsn-less and it works perfectly with mysql connector 5.3 until today when I tried to modify the code and make it 8.0 because 5.3 is too old (dates back 2014). Unfortunately, things went crazy. I am still able to link the tables but it appears #deleted. I switched back to odbc connector 5.3 but the problem persists. I am not using bigint as PK.

 

[lameid]

When the records show #deleted for all records it is because Access can't uniquely identify the records in the other database.

If you link your table manually, access will ask you to select fields that uniquely identify each record. On the link then Access creates a unique index using those fields.

You can do the same programmatically below is a procedure to create the index. strIndexFieldList should be a comma separated field list representing your unique index.

Sub RDBMSLinkUniqueIndex(strLocalTable As String, strIndexFieldList As String)

  If strIndexFieldList <> "" Then
    'When linking a table Access will automatically Use any unique server side index it sees to allow it to update data.
    'This is the optional solution if there is not one.
    'DDL is used to create an index that can be used to uniquely identify a record and update it.
    
    Dim strIndex As String
    
    strIndex = "CREATE INDEX ODBC_Unique_Index " & _
                "ON " & strLocalTable & _
                "(" & strIndexFieldList & ")" & _
                ";"
    currentdb.Execute strIndex, dbFailOnError 'I actually use a function that returns a static database variable 
                                               'with a parameter that refreshes the table list because generally I have just deleted and linked the table
  End If

End Sub

 

[humpydumpy2000]

hi, thanks for your reply, as I've checked the tables in the server side has valid PK and unique keys. I think the problem lies with Microsoft Visual C++ runtime I can't figure which among the many I uninstalled was the main culprit.