How Do I Change Display Font of Fields using VBA?

Dim_jbt · #1 (**permalink**) 11-03-10, 13:15

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...

tcace · #2 (**permalink**) 11-03-10, 14:05

right off the bat, I see a problem with your logic:

Quote:

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.

Dim_jbt · #3 (**permalink**) 11-03-10, 14:27

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...

Sinndho · #4 (**permalink**) 11-03-10, 14:55

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.

tcace · #5 (**permalink**) 11-03-10, 14:57

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!

Dim_jbt · #6 (**permalink**) 11-03-10, 15:22

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech