Consulting

Results 1 to 11 of 11

Thread: Access Data retrieved using ADODB loses formatting.

  1. #1
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location

    Access Data retrieved using ADODB loses formatting.

    Hi,

    I have a form created in Microsoft Word that I populate with data from either and Excel Worksheet or Access Table. Some the data columns are formatted as currency and percentages. Using Excel and the following connection string, the data is returned in the correct format e.g., 50% or $123.34

    fcnConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & strFileName & ";" & _
              "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";" 'Excel 12.0 Xml;HDR=YES;IMEX=1
    Adding the IMEX=1 results in the actual formatted data being returned.

    However, with Access using:

    fcnConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                           & "Data Source=" & strFileName
    The data loses its formatting e.g., 50% appears as 0.5 and $123.34 appears as 123.34

    Is there some way to get data using ADODB such that the format of the data in the record is returned?

    Thanks.
    Greg

    Visit my website: http://gregmaxey.com

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    This may be better as a question on the Word Forum.
    Does the Word VBA also have an extensed properties for Access?
    I do have one suggestion, but it depends on if you will be doing any calculations with the data in word.
    Before transferring the data you could place the formatted data in Access in to a temporary table that uses strings instead of number values.
    Thus it would be stored as 50% rather than 0.5 etc.

  3. #3
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    OBP,

    I don't know what "extensed properties for Access" is. So I can't answer your question. Word or Excel, it seems the issue is finding the right connection string (like adding IMEX=1 to the Excel connection string) so the data returned is as it is displayed in the Access table.
    Greg

    Visit my website: http://gregmaxey.com

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Sorry that should have been extended properties.
    I do not know much VBA for manipulating Access from outside of it, which is why I suggested the Word forum.
    Have you conducted a search of the Word Forum for doing this?

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    OBP,

    Yes, I've looked through both Word and Excel groups but can't find any connection string that includes extended properties like "IMEX=1" for Access so that the data returned is converted to a string value representing the same value displayed in the database.

    Thanks.
    Greg

    Visit my website: http://gregmaxey.com

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    What happens if you use a query instead of the table, you can reinforce the formatting in the query or even convert it to a text string.

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    OBP,

    Yes, that would probably work. I'm not very familiar with Access at all (just use it as a data source). I've cobbled together the following SQL, which does result in the data coming over ADODB as strings:

    SELECT Table1.Code, Table1.Name, Table1.Rank, Format([Percentage],"Fixed") AS [Percent], Format([Value],"$#,##0.00") AS Val
    FROM Table1;

    The problem is that in order to create the query, the column names "Percentage" and "Value" are changed to "Percent" and "Val" (can't find a way to keep the same column names).

    Any suggestions for a better query structure? Thanks.
    Greg

    Visit my website: http://gregmaxey.com

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I know it is a bit around the houses but you could base another query on your new one and change the names back, the second one may not need the formatting.
    Your string only has to call the second query and it will run the first one automatically.

  9. #9
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    OBP, I couldn't figure out how to do what you suggested. When I attempted to create the new query based off the first query, it kept changing the column name to Expr1 and Expr2 and wouldn't let me rename them "Percentage" and "Value"
    Greg

    Visit my website: http://gregmaxey.com

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Here is an example
    Original SQL
    SELECT [File Paths].FilePath, [File Paths].FileName, [File Paths].keyfield, [File Paths].[FileName] AS FileName1
    FROM [File Paths];
    New SQL
    SELECT [File Paths Query].[FilePath], [File Paths Query].[keyfield], [File Paths Query].[FileName1], [File Paths Query].[FileName1] AS FileName
    FROM [File Paths Query];

    It switches the Field name FileName to Filename1 and then from Filename1 to Filename.

  11. #11
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    OBP,

    Thanks for all of your help. I decided it was easiest just to rename my base table fields "xPercentage" and "xCost" and then use as single Query using the field names required (changed Value to Cost as Value must be a reserved named):


    SELECT Table1.Code, Table1.Name, Table1.Rank, Format(Str([Table1.xPercentage]), "0.0#%") AS [Percentage], Format([Table1.xCost],"$#,##0.00") AS [Cost]
    FROM Table1;

    All seems to work for my purposes. Thanks again!1
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •