how to get decimal places from fields collection in dao recordset

I am creating access tables dynamically from a recordset that is opened using an oracle query.  So if the user query returns 3 fields, I create an access table with 3 columns using names from the field collection of the recordset.  The problem I have is I cant see to find where the number of decimals is stored in the fields collection that is returned in the recordset.  I have looked through all the properties returned for each field and dont see one.  It returns a dbdecimal type, but I convert that to dbdouble and it defaults the decimal places to AUTO.  I need to know the number of decimals because ultimately, this will be formatted into an excel xls (via vba) file based on the table properties.  If I use AUTO for the number of decimals, when I format the vba code, I won't know how to format the cells properly.  Trying to automate as much of this process as possible to eliminate user manual effort.   I used access's transferspreadsheet to create the xls, but it doesn't do any cell formatting for various types.  I can set the number type with number of decimals if I can determine how many decimals the field should be.FYI, I'm using the oracle odbc driver for the oracle connection.

hope that makes sense.
Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
As far as I know, there are not any properties available in the DAO.Recordset object, or related objects like Field, that will explicitly tell you the precision and scale of the original data types used in the database.
Patrick MatthewsCommented:
If all you need to do is apply certain number formatting, that is easy to do...

Dim xlApp As Object, xlWb As Object, xlWs As Object

    Set xlApp = CreateObject("Excel.APplication")
    Set xlWb = xlApp.Worksbooks("path and name of the file you just exported with TransferSpreadsheet")
    Set xlWs = xlWb.Worksheets(1)

    With xlWs
        .Range("b:d").NumberFormat = "#,##0.00;(#,##0.00)" 'numeric with two decimal places, commas
        .Range("e:e").NumberFormat = "mm/dd/yyyy" 'US-style date
        .Range("f:f").NumberFormat = "$#,##0.00;($#,##0.00)" 'currency
        .Range("g:g").NumberFormat = "0.0%;(0.0%)" 'percent
    End With

    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing

    MsgBox "All done", vbOKOnly, "Yeah!"

Open in new window

marfi95Author Commented:
Thanks Matt.

My issue was not with the formatting part as I've done that before.  The issue was really how to determine how many decimals I need so I can use the correct string.  Since this is a dynamic application, lets say the oracle query submitted by the user is "select loan_amt from table".  Most amount fields are defined something like 10,2 in the database, however, at time the query is run, I dont know what fields were selected.   I was hoping something in the recordset field collection returned identified the number of decimal places in the returned data, so I could create my table as a double type and set the number of decimal places property (and thus excel formatting) correctly.
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

marfi95Author Commented:
I was afraid of that.  Do you know if ADO has what I'm looking for before I make the switch from DAO to ADO ?
Patrick MatthewsCommented:
Not sure if ADO is going to be any more helpful.  In know that in SQL Server you could get at that info by querying some system tables; presumably Oracle has something similar.

If you do not hear from any other Experts within a few hours, I suggest you use Request Attention to ask the Mods for help, and that you specifically ask that alerts go out to Oracle experts as well.

marfi95Author Commented:
Thats true, I could do that in some cases.  However, who knows what the user has requested in their query.  It could be a column straight from the table, could be a calculated field with an alias, etc.. Who knows.  

Thanks for your help.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.