14 Replies - 4636 Views - Last Post: 11 February 2011 - 03:23 PM Rate Topic: -----

#1 dilzniksan  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 02-September 09

Recordset Edit Method Fails

Posted 07 February 2011 - 04:39 PM

Here is a troublesome piece of code:

Dim TempID As Long
    TempID = NewRecord("worddocsclient", "ID"): DocID = TempID: ADocNumber = TempID
    Set WDSet = gDB.OpenRecordset("select * from worddocsclient where ID=" & TempID, dbOpenDynaset)
    
    WDSet.Edit // this fails, cannot find reference to errors thrown?

( recordset transactions )



WDSet can be edited and further business logic runs correctly, however the program containing above code (access file), has been run from a new machine and will not execute further business logic; the recordset IS populated with the correct data (NewRecord function inserts data) however the Edit function simply fails?

Is This A Good Question/Topic? 0
  • +

Replies To: Recordset Edit Method Fails

#2 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Recordset Edit Method Fails

Posted 07 February 2011 - 05:09 PM

Whenever I see this, it's because you think you're referring to an ODBC Recordset when in fact you're referring to an ADO Recordset, which doesn't have an Edit method. I would expect, however, that your code would fail on Line 3 if this were the case. Nevertheless, absent any direction from you on what sort of errors are thrown (perhaps you wouldn't mind sharing those), my best guess is that this is what has happened.

If this is in fact the problem, a simple way to fix it is to specify which type of recordset you're working with, thus:
Dim rs1 as DAO.Recordset
Dim rs2 as ADODB.Recordset
Otherwise the default recordset type will be used, and it will be the one that is specified higher in the reference list. (That's why you have the arrow that moves references up and down in the list, by the way.) Specifying the Component name from which the Recordset comes avoids any discrepancies between machines as to what references are in place or not.

HTH
Was This Post Helpful? 2
  • +
  • -

#3 dilzniksan  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 02-September 09

Re: Recordset Edit Method Fails

Posted 09 February 2011 - 01:54 PM

Weird thing is, no error is thrown, the function abruptly returns to the caller (and bypasses the remainder of the logic as well).

DAO is the recordset that is being used.

The call to OpenRecordset successfully returns the correct record and the corresponding object WDSet is populated.

This is perplexing because the method terminates so abruptly. Any VB error handling tips would help.
Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Recordset Edit Method Fails

Posted 09 February 2011 - 02:09 PM

Next thing I would do is check the errors collection. For help: http://support.microsoft.com/kb/209855
Was This Post Helpful? 1
  • +
  • -

#5 dilzniksan  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 02-September 09

Re: Recordset Edit Method Fails

Posted 09 February 2011 - 02:52 PM

Tried catching errors, none thrown so for kicks I commented out WDS.Edit and it appears that function returns regardless of the recordset.edit method.

Set WDSet = gDB.OpenRecordset("select * from worddocsclient where ID=" & TempID, dbOpenDynaset)
    Dim o As Integer
    o = WDSet(0) //object inspection; function fails immediately after
    'WDSet.Edit //commented out



It appears that the Recordset object is functioning fine?

What could cause execution to return so abruptly?
Was This Post Helpful? 0
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Recordset Edit Method Fails

Posted 10 February 2011 - 01:17 PM

Oh. First, make sure that you aren't accidentally running through an error handler. Do Tools/Options, select the General tab, and make sure Break on All Errors is selected. It's possible that you are having a problem setting your integer variable to the value in the first column in WDSet and you aren't getting the error to show.
Was This Post Helpful? 1
  • +
  • -

#7 dilzniksan  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 02-September 09

Re: Recordset Edit Method Fails

Posted 10 February 2011 - 03:05 PM

View PostBobRodes, on 10 February 2011 - 01:17 PM, said:

Oh. First, make sure that you aren't accidentally running through an error handler. Do Tools/Options, select the General tab, and make sure Break on All Errors is selected. It's possible that you are having a problem setting your integer variable to the value in the first column in WDSet and you aren't getting the error to show.


Thanks! Fairly new to VB so the 'Show all errors' was the crucial tip. The temporary 'o' was being set, but as it turns out the WDS.Edit function call was failing because of db permissions. Thanks again!
Was This Post Helpful? 0
  • +
  • -

#8 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Recordset Edit Method Fails

Posted 10 February 2011 - 03:27 PM

Glad we found it. That is very often a tripup. Thanks for letting me know that you've solved your problem.
Was This Post Helpful? 0
  • +
  • -

#9 dilzniksan  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 02-September 09

Re: Recordset Edit Method Fails

Posted 10 February 2011 - 04:16 PM

Ok, I recreated the database locally and it seems that permissions within the db are a non issue;

I ran GRANT ALL on db.* to user and the same block of code is now giving me

Run time error 3027: cannot update. database or object is read only.


What gives?

The recordset is being init'd correctly?

Set WDSet = gDB.OpenRecordset("select * from worddocsclient where ID=" & TempID, dbOpenDynaset)

Was This Post Helpful? 0
  • +
  • -

#10 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Recordset Edit Method Fails

Posted 10 February 2011 - 04:52 PM

Do you really need the functionality of an updateable recordset? There are typically better ways to handle updating a database via a recordset.

To further troubleshoot this, try issuing a sql server update command directly. Then, see if you can run a querydef object that has an action query. If you can't, then you know that it isn't the recordset type, but has to do with your SQL Server permissions.

Personally, I much prefer to run action queries to update a database, using a Snapshot-type cursor to move around in the data and refreshing the cursor as needed to update the changes. (Hard to get away with this if you're working with a large recordset.) I even more prefer to use ADO, because of the ability to update a recordset that can't update a database directly.
Was This Post Helpful? 0
  • +
  • -

#11 dilzniksan  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 02-September 09

Re: Recordset Edit Method Fails

Posted 10 February 2011 - 05:12 PM

View PostBobRodes, on 10 February 2011 - 04:52 PM, said:

Do you really need the functionality of an updateable recordset? There are typically better ways to handle updating a database via a recordset.

To further troubleshoot this, try issuing a sql server update command directly. Then, see if you can run a querydef object that has an action query. If you can't, then you know that it isn't the recordset type, but has to do with your SQL Server permissions.

Personally, I much prefer to run action queries to update a database, using a Snapshot-type cursor to move around in the data and refreshing the cursor as needed to update the changes. (Hard to get away with this if you're working with a large recordset.) I even more prefer to use ADO, because of the ability to update a recordset that can't update a database directly.


Permissions are solid on the db, can update records, etc; Ive even gone so far as to run the app with root credentials, still no dice. I will look into alternate solutions, but ultimately I will need to understand what differentiates my development environ from the working clients.

Edit: I forgot to mention above, that this error seems to be specific to my environ or newly created environs. I have made sure to use the same software (office 07), OS (win7), even the odbc driver (mysql 3.51) yet something is amiss.

This post has been edited by dilzniksan: 10 February 2011 - 05:29 PM

Was This Post Helpful? 0
  • +
  • -

#12 dilzniksan  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 02-September 09

Re: Recordset Edit Method Fails

Posted 10 February 2011 - 07:01 PM

I have an Access front end to a MySql front end; the Access file works on all of the client machines in the office I am working. However I have recently copied the .mdb file to a newly installed machine. This machine is identical to the other clients (same OS, office 07, and odbc connector), yet when I run the following code snippet:

 Dim TempID As Long
    TempID = NewMFBRecord("worddocsclient", "ID"): DocID = TempID: ADocNumber = TempID
    Set WDSet = gDB.OpenRecordset("select * from worddocsclient where ID=" & TempID, dbOpenDynaset)
     
    Dim o As Boolean
   'o = WDSet(0)
    o = WDSet.Updatable
    
   ' On Error GoTo er
    WDSet.Edit


The final line 'WDS.Edit' returns Error 3027 Cannot Update. Database or object is read-only.

Given that this code works elsewhere, I need to determine what is different between the working and non working environs.

This post has been edited by dilzniksan: 10 February 2011 - 07:02 PM

Was This Post Helpful? 0
  • +
  • -

#13 dilzniksan  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 35
  • Joined: 02-September 09

Re: Recordset Edit Method Fails

Posted 10 February 2011 - 10:35 PM

I believe I have solved this. I deleted all of the linked tables in access and recreated them. For some reason, the DAO.recordset objects are writable and I can proceed with development. WTF
Was This Post Helpful? 0
  • +
  • -

#14 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Recordset Edit Method Fails

Posted 11 February 2011 - 09:20 AM

Oh, you were using Access. LOL I had all sorts of squirrelly problems working with DAO and Access 10 years ago. Microsoft's solution to that was to suggest that we start using the improved OLE DB/ADO connections. While they were in fact improved, they are also a migration from an open standard (ODBC) to a proprietary one (OLE DB).

Wonder why the OLE DB providers work so much better than the ODBC ones, on the one product that Microsoft developed fully in-house (Access)? :)
Was This Post Helpful? 0
  • +
  • -

#15 Dogstopper  Icon User is offline

  • The Ninjaducky
  • member icon



Reputation: 2874
  • View blog
  • Posts: 11,047
  • Joined: 15-July 08

Re: Recordset Edit Method Fails

Posted 11 February 2011 - 03:23 PM

Duplicate topics have been merged.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1