ADO form recordset not updateable

And I specifically do not want to use Linked Tables.

Does your table1 have a primary key?  If not, neither recordsets nor linked tables will be updateable.

Yes the table has a primary key.
The data is updatable when I use a linked table.
Just not when I use an ADO recordset

If you're doing this on a form, is your FORM updateable?  I.e., Allow Edits, Allow Additions, Allow Deletions are all Yes, and Data Entry is No?

Yes the form is updatable. Allow Edits, Allow Additions and Allow Deletions are Yes.
Also in the debugger after the recordset property has been set I have checked
debug.Print me.recordset.Supports(adAddNew) 'also adUpdate and adDelete show true as well  
True

So I know the recordset should be updatable, but it doesn't show me the new record and doesn't let me update the records.

I also tried me.UniqueTable="Products" to see if that would make a difference, but it didn't

I was having a similar problem and it turned out that the record was lock by another form I had open.  I had been using one form as the master and when I wanted to change some detail of the master record I would bring up another form.  I could not save records on the second form.  What I had to do is close the master form and then I was allowed to update the record.  When I was done updating I re-opened the master form.

Is this a possibility in your case?

nope.
I even tried putting it as the recordset on a blank form with nothing else open.
It gave me the number of records in the navigation bar, but with the new record navigation button grayed out.
I tried the exact same thing with the linked table and the new record navigation button was lit up.

well I copied the data directly from the website and it still gave me the non updatable recordset.
I used the OleDB for ODBC sources for both PostGreSQL and SQL Server.
I created a new form and put this code in the OnOpen event. It gave me the records but not the new record button.

I need it for PostGreSQL and I am only testing SQL Server so that I can say that it is a PostGreSQL problem if it works for SQL Server. If it doesn't even work for SQL Server then I can say that the solutions doesn't work. That is why I did not try the preferred method for SQL Server, because the OLEDB for ODBC should work for MSSQL as well. Here is the code I just tried (copied exactly from the code on the link you posted with the exception of strconnection details and the recordset source query):


Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim strConnection As String

   strConnection = "ODBC;DSN=SAP_PG;UID=username;PWD=xxx;DATABASE=dbname"
   'Create a new ADO Connection object
   Set cn = New ADODB.Connection

   With cn
      .Provider = "MSDASQL"
      .Properties("Data Source").Value = strConnection
      .Open
   End With

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM Products"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .CursorLocation = adUseClient
      .Open
   End With

   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
End Sub

Any other thoughts?

what you are doing at the moment you are connecting to the backend and asking for that specific data and displaying it on a form.

If you want to edit the dipslayed data from the form, you have to update table using update method of recordeset ie. recordset.update field,value

I think it's "the exception of strconnection details" that make the difference.  You're not using OLEDB, you're using ODBC via a DSN.

Those details are in this part of the code in the MS linked article:
With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = "MySQLServer"
      .Properties("User ID").Value = "sa"
      .Properties("Password").Value = ""
      .Properties("Initial Catalog").Value = "NorthwindCS"
      .Open
End With

I cannot address how to do this with PostGreSQL, though you may be able to find out how in the PostGreSQL topic area:  https://www.experts-exchange.com/Databases/PostgreSQL/

If this works just as expected with linked tables, why have you explicitly stated that you cannot use them?

If you look towards the bottom of that page there are details for doing it through an ODBC connection  - Do an on the page search for "Requirements for ODBC"

This is the string connection that they use there:
strConnection = "ODBC;DSN=MyDSN;UID=sa;PWD=;DATABASE=Northwind"

As you'll see mine mirrors theirs with the excpetion of the details, my dsn name, my username, my password and my database.

There are a couple reasons that I don't want to use Linked Tables.

1) When I open the form using a linked table it takes a lot longer to open then when connected via an ADO Recordset.
2) We are planning on moving off of Access after I finish moving the backend off, and the migration will be much easier if the connections are all being done through ADO.

Oh, yes, sorry....

I'm out of ideas then, because you've explicitly stated you don't want to use the only thing you know (so far) will work (linked tables).

Hakim,

I want to use the form in datasheet view to add and edit the data. I don't want to pop up another window to type in the new data or changed data.

I reread the webpage you posted and it gave those examples of OleDB providers for Access 2002 and higher. Access 2000 does not support them :-(
According to the article it is impossible to have an ADO updateable recordset in Access 2000 except for the SQL Server OleDB provider.