I am not sure, exactly, why you are experiencing the issue you describe. If you linked to an actual table, you should be able to append records. It may be a User Rights thing on SQL server.
As for adp, as of Access 2013, it has been deprecated. You can still use it, but there really is not a need to create an adp. You can easily make ODBC connections to SQL Server and SQL Databases from an Access accdb, accde, etc. My preference is to use DAO over ADO and use ODBC connection strings. You can use the SQL Server Native Client 11.0 ODBC driver to create a DSN-less connection. This driver is backward compatible with ADO, too. Here is some info on downloading the driver that you would want to install on each client.
https://www.accessforums.net/sql-ser...ere-52914.html
Here is an example of a connection string.
Code:
Dim strODBC as string
strODBC = "ODBC;" & _
"Driver={SQL Server Native Client 11.0};" & _
"Server=tcp:192.168.X.XXX,1433;" & _
"Database=DatabaseName;" & _
"Uid=name;" & _
"Pwd=passw0rd;"
Here is some example code that is part of a process I use to recreate linked tables. I first delete the existing linked tables and then use records from a local table to recreate the linked tables.
Code:
While rs.EOF = False
strTableName = rs![TableName]
Set t = db.CreateTableDef(strTableName)
t.Connect = strODBC
t.SourceTableName = strTableName
db.TableDefs.Append t
t.RefreshLink
Set t = Nothing
rs.MoveNext
Wend