UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Get Scale Of A Decimal Column With VBA, Access 2010    
 
   
AlbertKallal
post Oct 31 2016, 02:41 PM
Post#1


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


Is it possible to get the “scale” value of a decimal column in an Access table?

I am looking to get these two values:



NEAR everything thing I see suggests that that I need to reference BOTH ADO, and ADOX. I really (but really really!) want to avoid those references (in fact it not going to happen!). I looking for a DAO solution.

Scoring the caves of the internet and doing archeological digs that would make Indiana Jones jealous, I have NOT found any setting that can return the scale value.




However, while in a cave running away from a huge round bolder trying to squash me like a bug, I HAVE discovered when dealing with a decimal column, then the collating property returns the Precision value. (the full size of the decimal column).

Since “size” property returns ONLY the size of the column, not the full length (precision), then grade school math done on the back of the treasure map can yield this elusive “scale” value.

So JUST in case I overlooked some cool treasure map on a far far away island with some sunken cargo ship full of secret plans to the Access database engine?

Anyone know if some settings exists to obtain the scale value?

Any reasonable far-fetched idea or having me join an expedition to some lost Temple of Access treasures on some far away island would be appreciated.



The current code I have for this solution is as follows:

CODE
   Dim db      As DAO.Database
   Dim f       As DAO.Field
  
   Set db = CurrentDb
   Set f = db.TableDefs("Table1").Fields("MyNum")

   Debug.Print f.Size
   Debug.Print f.CollatingOrder
  
   Debug.Print "scale = " & f.CollatingOrder - f.Size + 1
output:
16
19
scale = 4


Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
ADezii
post Oct 31 2016, 06:56 PM
Post#2



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


Another option would be:
CODE
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field

Set rs = New ADODB.Recordset

With rs
  .Source = "Table1"
  .ActiveConnection = CurrentProject.Connection
  .CursorType = adOpenKeyset
  .LockType = adLockOptimistic
    .Open
End With

Debug.Print "Precision: " & rs.Fields("MyNum").NumericScale
Debug.Print "Scale    : " & rs.Fields("MyNum").Precision

rs.Close
Set rs = Nothing

CODE
Precision: 4
Scale    : 19
Go to the top of the page
 
AlbertKallal
post Oct 31 2016, 08:34 PM
Post#3


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


At least your suggestion eliminates the need for an ADOX reference. So thank you kindly.

I am “resisting” having to add the ADO reference. I will make a “judgement call” on this. I could also consider late binding.

You solidify that “scale” is not available from DAO, and I likely not missed anything out in Access land.

The application is rather large – I take adding references to production applications with GREAT caution. While “most” code does prefix record sets with DAO, a lot of code does not. (so some risk occurs by introducing an ADO reference).

Everything tends to center on risk management. If I had no choice, then at least your solution is viable without having to use ADOX, but just ado.

It was somewhat of a long shot on my part to ask, but the UA community always cooks up some suggestion that can help others even if I don’t adopt your suggestion.

Again, thank you.

Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
Doug Steele
post Oct 31 2016, 08:56 PM
Post#4


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Albert: Could you not use Late Binding, thus eliminating the need for a reference for ADO? (Yes, I realize that you still need to work with ADO, but at least you avoid Reference [censored]!)
Go to the top of the page
 
ADezii
post Nov 1 2016, 05:07 AM
Post#5



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
so some risk occurs by introducing an ADO reference

Out of curiosity, what do you consider those risks to be?
Go to the top of the page
 
Doug Steele
post Nov 1 2016, 07:19 AM
Post#6


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Any time there's a different version of the reference between the client machine and the developer machine, you run the risk of the entire application failing (not just the part of the application related to the specific reference in question). That's why my standard advice is "starve the references" (i.e.: include just the bare minimum required to get Access to run) and use late binding.

Since there are so many different versions of ADO in the wild, it's particularly prone to versioning issues.
Go to the top of the page
 
ADezii
post Nov 1 2016, 09:38 AM
Post#7



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


@Doug Steele:
Thanks for the explanation, makes perfect sense. When I use ADO I always make an attempt to utilize the lowest Version that will get the job done.
Go to the top of the page
 
AlbertKallal
post Nov 1 2016, 05:10 PM
Post#8


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


Thanks Doug - I adopted my code example - but your suggest of late binding is a very reasonable solution.

And I appreciate the follow up and answering of "starving the references" - a great term and a simple way to explain that based on experience one simply avoids adding references "when" possible and without careful consideration.

And of course with a large complex application, even the "order" of the reference(s) (and especially ADO) can cause issues.

As a FYI to readers here?
With two recordset object types (ADO and DAO), then:

dim rst as recordset

In above, it often not clear what type of recordset will be returned. Will above return ADO or DAO? (you really don't know - but worse is for a large application there going to be "tons" of existing code - and that gives rise to hidden errors).

At least I have a working example - and one that sticks to DAO (ACE).

Again thanks to all the great folks here at UA - hugs!


Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
ADezii
post Nov 1 2016, 06:29 PM
Post#9



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
In above, it often not clear what type of recordset will be returned. Will above return ADO or DAO?

CODE
re: Dim rst as recordset

I has always been my notion that the Object Library referenced in this case (ADO or DAO) would be the 1st one listed in the Available References Dialog. Have I been wrong in my assumptions?
Go to the top of the page
 
Doug Steele
post Nov 1 2016, 06:37 PM
Post#10


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You are correct that the type of recordset returned would correspond to whichever reference was higher in the list. I believe Albert's post was that too many people use unqualified declarations like that, not knowing they might not be getting what they expect. (It was a real problem in certain versions of Access--2007, I think--where the ADO reference was inserted without many people realizing it.)

--------------------
Go to the top of the page
 
ADezii
post Nov 2 2016, 07:17 AM
Post#11



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


Thanks Doug for the clarification. As suggested by Doug, I completely eliminated the ADO Library Reference and used Late Binding. With couple of minor changes, the results were exactly the same.
CODE
Dim oRs As Object
Const conSTATIC_CURSOR = 3
Const conLOCK_OPTIMISTIC = 3

Set oRs = CreateObject("ADODB.Recordset")

With oRs
  .Source = "Table1"
  .ActiveConnection = CurrentProject.Connection
  .CursorType = conSTATIC_CURSOR
  .LockType = conLOCK_OPTIMISTIC
    .Open
End With

Debug.Print "Precision: " & oRs.Fields("MyNum").NumericScale
Debug.Print "Scale    : " & oRs.Fields("MyNum").Precision

oRs.Close
Set oRs = Nothing
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 05:54 AM