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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Patrick MatthewsCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.