Access 2010 ADP Project to Sql Server 2008 Native R2

[johnson8809]

I created an Accces adp project using Access 2010 and Sql Server 2008 native R2. I just used 6 tables to start because I wanted to make sure that the same 6 tables linked over to my Sql Server back end w/out any problems. NOW! My problem is that I can add data to a table on the Sql Server back end and see the data dynamically in Access when I refresh and view the table. However, the Access front end side doesn't afford me the option to add a new row of data at all. I don't see anywhere where I could have made any choices that would make the Access side appear read only or deny the ability to manipulate data from that side. It does however allow me to create a stored procedure from the Access side that I can in turn see dynamically on the Sql Server.

[ItsMe]

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.

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.

    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