Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Location
    Texas
    Posts
    3

    Unanswered: How Do I Change Display Font of Fields using VBA?

    Here is some background:
    -Using Access 2003
    -Small Data Warehouse Project
    -Importing raw data into a temp table for processing

    I have a VBA procedure that:

    1. Creates a temp table with two fields (RawRec Text(200), RecID AutoIncrement)
    2. Uses the DoCmd.TransferText to import a text file report
    3. Uses a "Do While not rs.EOF" to processes the data

    For development and clarity purposes I would like to see the report in the table using Courier New font (8 point). You can do this by clicking "Format", "Font" on the menu. I can't seem to find the VBA equivelent for this. Does anyone know?

    The table is dropped each time a report is imported to keep the db small and clean so I have to set this font every time... It would be nice if I could import and then set up fonts, display width, etc. When you do this manually, Access askes if you want to "save" when you close the table. Seems there aught to be a way to do this w/ VBA.

    Thanks in advance...

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    right off the bat, I see a problem with your logic:
    The table is dropped each time a report is imported to keep the db small and clean
    Unless you are compacting each time, the space consumed by the table is still part of the db file, even after you drop it - a lovely little feature of Access called ballooning.

    Why not use one temp table with an extra field - a boolean indicating the record is used and simply clear the data field (and set the boolean to false) each time you are done with it. New data comes in, start at the beginning and if you run out of rows, add new ones. It will never be larger than the largest set you imported and there's no ballooning or compacting to worry about.

    Now, for the rest of your problem, I'm not clear on what you want to do - set the table font? Are you processing the data and then viewing the table or copying the table data out?

    There is a setting in the application options to what fonts to use for new objects. That will set the font when a table is created. You could also use a simple form (to XLS) or report (to RTF) that sets the fonts - have the code run the OutputTo method for you and save yourself that step.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Nov 2010
    Location
    Texas
    Posts
    3

    Followup to change display font & AutoIncrement Fields

    Thanks for the reply... I had thought about using a table that was not dropped each time, however I ran accross some issues with this.

    Data is imported weekly and 3 time on month end. (Weekly reports, and two monthly reports.) When I import a txt file into the table, for some odd reason it does not keep the data in the same order as the text file. (??) So I added an autoincrement field to the table. The first text field is 200 char in width and then the autoincrement field. I can sort on the autoincriment field to keep the data in order, which is required to process the data because recordsets are spread accross multiple rows in the report. Well, it turns out that Access will not let you reset an autoincrement field back to 1 when you delete all the data unless you compact and repair. So, I just drop the table with sql: DROP TABLE "tblRawRec" and when the user clicks the "import report" button I add the table with SQL: "CREATE TABLE tblRawRec ([RawRec] Text(200), [RecID] AutoIncrement);" This way the autoincrement is always 1 to the number of records in the report (Usually around 11,000).

    So - if you know how to get a text import into a table and keep the data in the same order of the text file, I could use a static table and just delete all the data with SQL statement, import, then number the records with a loop. (This was my first plan of attack, but access keep reordering my data for some reason.)

    Or - if you know how to reset an autoincrement field to start at 1 after all the data has been deleted that would work as well.

    The Option Setting solved my font issue, so this post really more about data management philosophy...

    Here's the code I am using:

    Code:
    Private Sub cmdImpTxt_Click()
        Dim strSQL As String
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim tbl As DAO.TableDef
        Dim RecNo As Long
        Dim i As Integer
        
        'Set up working environment...
        DoCmd.SetWarnings False
        DoCmd.Hourglass True
        
        'Initialize variables
        Set db = CurrentDb
        i = 0
        strSQL = "DROP TABLE tblRawRec;"
        
        'Delete the tblRawRec table if it is still in the db
        For Each tbl In db.TableDefs
            If tbl.Name = "tblRawRec" Then
                db.Execute strSQL
                Exit For
            End If
        Next
        
        'Create a new tblRawRec to import the report...
        strSQL = "CREATE TABLE tblRawRec ([RawRec] Text(200), [RecID] AutoIncrement);"
        db.Execute strSQL
        
        'Import text report here...
        DoCmd.TransferText acImportFixed, "ImpTxtRpt", "tblRawRec", "C:\7966_p1009.txt", False
            
        'Clean-up and exit
        Set db = Nothing
        Set rs = Nothing
        DoCmd.SetWarnings True
        DoCmd.Hourglass False
    
    End Sub
    Note: the file location is just hard coded for testing...

    Thanks for the assistance...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    One easy solution would consist in using a copy of a model table with the proper format settings as the temp table instead of creating a new one from scratch.

    I've tried to manipulate the AccessObject that represents a table in the AllTables collection, but to no avail: the properties of such an object are not accessible. If anyone knows the solution, I'd be glad to hear about it.
    Have a nice day!

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ahh, I see. I rarely use the TransferText method, although you could try linking and processing line by line using a recordset object. At 11,000 rows, this could get time consuming.

    Since TransferText will only append, what I suggested will not work.

    Glad the immediate problem is solved!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  6. #6
    Join Date
    Nov 2010
    Location
    Texas
    Posts
    3

    Import Method

    I am exploring a better way to process the data. The db is currently procesing the using the Microsoft Scripting Runtime's textstreamobject. And yes, process the text file line by line is slow. It takes about 1.5 min to processs a 11,000 line file. That's why I thought if I brought it into a table and then processed the table it might speed things up a bit.

    Thanks for the help.

Tags for this Thread

Posting Permissions

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