Field default values prevent adding records in Datasheet view and ADO recordset problems

Question

I found that when having Default Values assigned in the properties of a table field, I have problems with the table, regardless of whether the table has a primary key.

First, in Datasheet View, I cannot add a record.  In Records group, New is grayed out, and on the Datasheet, (New) appears to the left of the last record, not an empty record to be added.

Second, default values also prevented basic ADO recordset operations - (table had 1 record):

Dim rsTest As New ADODB.Recordset
Dim strSQL As String
    
strSQL = "SELECT * FROM table"
rsTest.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rsTest.MoveFirst

The MoveFirst gave an error not having recordset object, and rsTest.RecordCount returned a value of -1.

Is this known behavior?  I worked around it, but it did seem strange.

--------------------------------------------------------------------------------------------------

Access 2007 SP2

References:

Microsoft ActiveX Data Objects 2.8 Library

Microsoft ActiveX Data Objects Recordset 2.8 Library

Answer 1

Even though you can manipulate data within an Access DB with ADO -- ADO is not native to Access and is best used when interfacing between Access and Sql Server to manipulate sql server data from Access (ADO emerged to compensate for the limitations of ODBC between Access and Sql Server since ODBC has firehose bandwith where Access has garden hose bandwith).  To best manipulate data within the Access DB you are better off using either DAO which is native to Access or DoCmd.RunSql... (also native to Access).  So rather than recommending a bunch of spaghetti code workarounds for your ADO problem -- I would recommend switching to DoCmd.RunSql... or use DAO.

---

Rich P

Answer 2

Did you set the option to prefetch Defaults from the database? If not

this may be the reason.

Another reason could be that Access doesn't recognize the primary key

for example because the datatypes are not recognized correctly or

identically matchable. If the table is linked can try to set the Primary

Key by a DDL statement on the linked table (this will not add a PK to

the source table, just let the linked table know what the primary key

is). Details see OH

CREATE UNIQUE INDEX [primarykey] ON [yourtable]([primarykey field])

HTH

Henry

 

On 03.05.2012 00:12, jsfcal wrote:

> I found that when having Default Values assigned in the properties of a

> table field, I have problems with the table, regardless of whether the

> table has a primary key.

 

Answer 3

In ADO, the cursor types and locations can change the availability of properties and methods (like MoveFirst, MoveLast).  For example: the RecordCount property is not valid for a forward only recordset, thus the return value of -1.  I would suggest that you change you CursorType to adOpenKeyset or adOpenStatic.

---

Brent Spaulding | Access MVP

Answer 4

BTW ... is this the actualy code you are having trouble with?  Or was this a mock up that is similar to what you are using.  If it is the latter, please post the real code (with sensitive information obfuscated).  The reason I ask is that ADO has this brilliant ability to silently coerce some properties to something that is valid for the provider, without you telling the user.

---

Brent Spaulding | Access MVP

Answer 5

Thanks everyone for responding.  First, unlike many questions, I had already found an acceptable solution, specifically not setting default values to fields.  I posted this mostly out of curiosity because I had never heard of this issue.  Second, the issue goes beyond programming and ADO, based on my inability to add records in the Datasheet View.

RichP123 - Many of my applications end up using SQLServer or Oracle to host the data, so ADO works better. I don't believe using ADO has to result in spaghetty code, although there are some issues, seemingly undocumented, per some of the comments in this thread.

Henry - I did set the option to fetch.  Regarding the primary key question, the issue arose either with or without a primary key.  Table was not linked.

Brent - I was only using RecordCount for diagnostics.  It is not in the code.  Most of my reference books go back a few years.  If you can suggest a good book that describes issues such as how cursor types and locations impact the availability of properties and methods, would be interested.  Same regarding ability to silently coerce some properties to something that is valid for the provider, without you telling the user.  Seems like secrets of the trade.  That was pretty much the actual code.  BTW - based upon something I must have done later, I can't duplicate the issue, so can't try changing CursorType to see if that fixes things.  Even the DataSheet seems OK.  What can I say?

Answer 6

>>  Many of my applications end up using SQLServer or Oracle to host the data, so ADO works better.  <<

Only if you connect directly.  If you go through the Jet/ACE database engine (ie: CurrentProject.Connection), you'll likely be better off using DAO.

>>  If you can suggest a good book that describes issues such as how cursor types and locations impact the availability of properties and methods, would be interested.  Same regarding ability to silently coerce some properties to something that is valid for the provider, without you telling the user.  Seems like secrets of the trade.  <<

I am sorry I don't have an indepth book on that.  I have however, written one! (at least the chapters on ADO and DAO) LOL! (Microsoft Office Access 2007 VBA) ... but the depth you seek will likely not be reached.  Since a lot of the things are indeed provider specific, the ADO api's implement methods to test for capabilities.  But with respect to 'silent coersion', I have only experienced that in MS Access -- but the caveat is that I have only used ADO (com) deeply in MS Access, some basic stuff in VBScript.  Here is a link to the ADO api programmers reference, that may help you out, but many times you have to 'just put it all together', as the docs will not spell all of our questions with a direct answer.

I would love to chat more, but I gotta run!

---

Brent Spaulding | Access MVP

Answer 7

Thanks again, Brent.  Will check out the links.