• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 795
  • Last Modified:

ADO form recordset not updateable

I am trying to connect an SQL backend to an Access form via code and for some reason the form is never updateable.
I am trying specifically using PostGreSQL, but I tried also with SQL Server and had the exact same problem.

Here is my code:

private sub Form_Load()
Dim conn as new adodb.connection
dim rs as new adodb.recordset

conn.open("DSN=DSNNAME;UID=userid;pwd=password")
rs.cursorlocation=adUseClient
rs.open("select * from table1"),conn, adopenKeySet, adLockOptimistic
set me.recordset=rs

End Sub

The recordset appears and I can navigate through them, but there is no new record navigate button and I can not edit any of the data. Again I have tried this with both PostGreSQL and SQL Server with the same results. I have also tried all the different combinations of cursor and lock types. I have superuser access on the PostGreSQL and trusted connection access on the SQL Server logged in as administrator.  

Any thoughts woulod be greatly appreciated.



0
szacks
Asked:
szacks
1 Solution
 
szacksAuthor Commented:
And I specifically do not want to use Linked Tables.
0
 
pique_techCommented:
Does your table1 have a primary key?  If not, neither recordsets nor linked tables will be updateable.
0
 
szacksAuthor Commented:
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
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
pique_techCommented:
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?
0
 
szacksAuthor Commented:
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
0
 
niblickCommented:
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?
0
 
pique_techCommented:
You're using the wrong kind of connection configuration to get updateable recordsets.  See here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;281998

I cannot explain why the debugging you did shows otherwise.

I found this by using Google:
http://www.google.com/search?sourceid=navclient&ie=UTF-8&q=ado+form+recordset+access+updateable

I think this at least explains the WHY of your question.  Hopefully the MS article can suggest some HOWs for you.
0
 
szacksAuthor Commented:
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.
0
 
szacksAuthor Commented:
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?
0
 
HakimCommented:
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

0
 
pique_techCommented:
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:  http://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?
0
 
szacksAuthor Commented:
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.
0
 
szacksAuthor Commented:
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.
0
 
pique_techCommented:
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).
0
 
szacksAuthor Commented:
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.
0
 
szacksAuthor Commented:
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.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now