24.08.2007MS Access: Let lines in Reports grow with it's content

The problem is the following: You create a report with MS Access with a text field. To display the whole text, you have to set the “CanGrow” property to “Yes” (on the section and on the text-field). When running the report that has vertical lines as column seperator you have an ungly effect: The line keeps its size, as this has been fixed in the design view.

One might think that the easy solution is to adjust this length at runtime. First of all I need to declare what “runtime” means. As this problem occures in every “Detail”-Section we must dive in the events a section provides. There are three of them “OnRetreat”, “OnFormat” and “OnPrint” (apart all the mouse events, which are defenitly not suitable).

The Retreat-Event occurs “when Microsoft Access returns to a previous report section during report formatting” (Source: VBA Help). Not exactly what we’re looking for.

The Format-Event occurs “when Microsoft Access determines which data belongs in a report section, but before Access formats the section for previewing or printing”. It sounds good, but at this point we don’t have our data in the section and are therefore unable to determine the correct height.

Finally the Print-Event “after data in a report section is formatted for printing, but before the section is printed”. Here we are, we have our data which is not yet printed. The problem is that at this point all elements are already formatted, all we can do is add new elements and that’s why we can’t just adjust the height of each line by setting it via

 Me.myLine.Height = Me.Height '''this does not work in the OnPrint event, myLine has already been formatted

The simple solution is to draw a new line and use your already placed line as a template:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    Dim EndOfLine As Double
    EndOfLine = Me.Height       ''height of section (where the line stops)

    ''becasue it's not allowed in such a late state to change lines, we draw new one's based on the old
    ''myLine
    Me.Line (Me.myLine.Left, 0)-(Me.myLine.Left, EndOfLine)

End Sub;

30.06.2007MS Access and not updatable Recordsets

The whole thing is just plain stupid: Someone in the department I work in has created an MS Access Database to track our customers. Above this application are some forms to insert the data. Now we already have much of the data in various excel sheets etc. So my task was to write a function (or method or sub or whatever you call it in your world) to get some values from an excel sheet and insert it to a form, just like a user would do it. Like that the user has the possibility to check and change the data before it gets saved in the database. Now if you bind the form elements to parts of a query, Access gets your data as a so called snapshot via MS Jet (your communication channel to the DB). Snapshot data is not updateable, so if you try to assign your own values to form elements an error message pops up and that’s it.

My first solution was to ignore that fact and simply update my data directly to the database. But then all nice features like the “undo” option (to reset a record) or the possibility to check values before they get inserted are lost. The solution I came up is simple, but you need to check your status very exact to keep a usable form.

I have a form with my data, I have a save button, I have an exit button and I have an update button. There is a special button to start the gathering of data from the excel sheet. Now If you want to change the value of a textbox on the form you need to cut the textbox off the data (ControlSource = ""), then you can freely change the value.

Now you can’t simply update the current record, because the textbox is no longer in the recordset. This means that you have to update the value directly in the database when the save button is pressed. Then reattach the ControlSource and Requery and Repaint your form.

When you want to Undo your changed, just don’t save your values in the DB and reassign the ControlSource and Refresh the form, the old value will appear.

14.05.2007Convert fixed length column to Excel

I had to convert the output from sqlplus to an excel sheet. The problem was, that I had no influence on the creation, so I could spool the file directly to excel.

All I got were spool files like that:

      CLASS_ID SUPER_CLASS_ID   COUNT(*)
============== ============== ==========
       128           8765         18
       128           8765        131
       128           8765          7

My idea was to take the line used to seperate the column titles and the data as the data that gives me the length of each column (refered as the column line in my function below). Then just simply extract a block from start to end (as given) based on the gathered column length information.

Maybe someone can use this piece of VBA code:

Public Sub convertFixed()
    Dim sFile As String
    Dim iCount As Long
    Dim iSubCount As Long
    Dim iLastCount As Long
    Dim i As Integer
    Dim Length() As Integer
    Dim sValue As String

    Dim iStart As Integer
    Dim iEnd As Integer
    Dim iColumn As Integer


    ''clear previous data
    Cells.ClearContents

    ''choose the file with the windows file dialog
    With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Choose log file to convert"
    .InitialFileName = ThisWorkbook.Path
    .InitialView = msoFileDialogViewDetails
    .Filters.Clear

    ''the filter is set to .log .spool and .txt files
    .Filters.Add "Log file", "*.log, *.spool, *.txt"

    .ButtonName = "Load"
    If .Show = -1 Then
        sFile = .SelectedItems(1)
    Else
        ''stop execution if no file is selected
        Exit Sub
    End If
    End With


    ''we need the following informtion (gather with InputBoxes)
    ''line to scan the column lengh (space delimiteted) -> line number
    ''begin of block (line number)
    ''end of block (line number)
    iStart = InputBox("Enter line number where block starts:", "Line number of Start")
    iEnd = InputBox("Enter line number where block ends:", "Line number of End")
    iColumn = InputBox("Enter line number of fixed length specification", "Line number for fixed length")

    ''the first loop is to get the correct column length
    'open the file
    Open sFile For Input Access Read As #1
    iCount = 1
    ''loop until end of file (EOF) is reached
    Do Until EOF(1)
    Line Input #1, sLine
    If (iCount = iColumn) Then
        ''exit if the column line (to scan the length) is reached
        Exit Do
    End If
    iCount = iCount + 1
    Loop
    ''close the file
    Close #1

    iCount = 0
    iSubCount = 1
    iLastCount = 0
    ''sLine now contains a line that gives us the fixed length of the columns
    sLine = RTrim(sLine)
    Do Until iSubCount > Len(sLine)
    If (Mid(sLine, iSubCount, 1) = " " Or iSubCount = Len(sLine)) Then
        ''the Length array stores the length of each column (needs to be ReDim'ed with each new column)
        ReDim Preserve Length(iCount)
        Length(iCount) = iSubCount - iLastCount
        iLastCount = iSubCount
        iCount = iCount + 1
    End If
    iSubCount = iSubCount + 1
    Loop

    ''open the file to extract the block based on the column length stored in the Length-Array
    Open sFile For Input Access Read As #1
    iCount = 1
    iSubCount = 1

    ''loop until the end of the block is reached
    Do Until iCount > iEnd
    Line Input #1, sLine
    iLastCount = 1
    If (iCount >= iStart) Then
        For i = 0 To UBound(Length)
            sValue = Trim(Mid(sLine, iLastCount, Length(i)))
            ''prevent application errors with adding of "'" in front of special characters
            If (Left(sValue, 1) = "=" Or Left(sValue, 1) = "-" Or Left(sValue, 1) = "+") Then
                sValue = "'" & sValue
            End If
            ''insert the data to the current Excel sheet
            Cells(iSubCount, i + 1).Value = sValue
            iLastCount = iLastCount + Length(i)
        Next i
        iSubCount = iSubCount + 1
    End If
    iCount = iCount + 1
    Loop
    ''close the file
    Close #1

End Sub

27.12.2006Excel to CSV with Python

I wanted to handle a simple task: convert a bunch of XSLs to CSVs. Well I wrote a small Script in VBScript and it worked. Then I needed to change the List separator, so I did that in my System settings, and then my Script didn’t work anymore, because it just ignored my changed settings.

I googled a lot and didn’t find the solution (only people with the same problem). It seems that VBScript (or WSH) is using it’s own locale that you can not change.

So I began to look for alternatives. Python can handle the COM interface when you install PyWin32 (the version must match the python version you’re using). So I just rebuild my script with python and it’s COM-Interface. Et voil�: it takes the user defined settings and converts my files in the way I want them.

#import win32 stuff
from win32com.client import Dispatch
#import other needed
import glob,sys,os

path = os.path.split(sys.argv[0]) #gather the path of the script
path = path[0]
path = path + '\\' #append \ at the end of path (when using *NIX you must use / instead
files = glob.glob('*.xls') #get list of files

xlApp = Dispatch("Excel.Application") #create Excel COM-Object
xlApp.DisplayAlerts = False
for file in files:
    xlWB = xlApp.Workbooks.Open(path + file)
    xlWB.SaveAs(path + file.replace(".xls",".csv"), 6 ) #6 = xlcsv
    xlWB.Close()
xlApp.Quit()
del xlApp

27.12.2006Information about Oracle Directories

I know what I write is quite newbieish, but I needed information about a directory I’ve created, means which path it’s pointing to:

select * from all_directories dir;

There simply is no userdirectories, and with allobjects I didn’t get what I wanted.

12.12.2006SQL Start-Script generator

Sometimes it’s so simple:

import glob
files = glob.glob('*.*');
f=open('start_script.sql', 'w')
f.write('PROMPT This is an automatic generated start script\n');
f.write('PROMPT\n');
f.write('SPOOL start_script.LOG\n\n');
for filename in files:
    if filename == 'start_script.sql':
        continue
    f.write('PROMPT Starting script ' + filename + '\n');
    f.write('@@' + filename + '\n');
f.write('\nPROMPT\n');
f.write('PROMPT Finished.\n');
f.write('SPOOL OFF\n');
f.close();
exit();

Explanation:

First get a file list of the current directory (glob.glob(‘.’), this won’t return you directories since they don’t have an extension, if your files doesn’t have an extension, either: your fault). Then open a file called start_script.sql which will held our start-script (pretty self-explanatory). Then we generate a “@@” line for each file (this means if you have non-SQL-files in that directory you have to clean-up your script or edit it that it only takes .sql files (tip: glob.glob(‘*.sql’)). The start-script itself is excluded but the python script will be included (can easily be made excluded as well). After the loop the file gets closed and that’s it.

07.11.2006Delete duplicate records with Orcle

Don’t ask me why I needed to do that, but I had a lot (about 14 Mio.) duplicate records in a table.

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

helped me out. (thanks to www.devx.com)

Next time I write my queries to filter those out before inserting them…

02.11.2006PISA-Test

Danke Baba fürs schicken des Artikels ;)

War so im Tagi (weiss leider nicht genau von welchem Tag):

18.10.2006Need to generate a GUID with Oracle?

I just searched the web for this simple task, and it was difficult to find.

Maybe anyone working a bit with Oracle knows this, but it helped me, maybe I can help someone out there too, so here it is:

select sys_guid() from dual

This generates a 32 character GUID (fits best in a RAW(16) field).

24.01.2006Psychologie vs. Psychiatrie

Wie das so ist, wenn man gemütlich zusammen sitzt, vorzugsweise mit gutem Essen, spricht man über Gott und die Welt und plötzlich:

Was ist eigentlich der Unterschied zwischen einem Psychologen und einem Psychiater?

Solche Dinge brennen auf der Zunge, man gibt sich der Unwissenheit zu erkennen, hat aber gute Aussichten nach dem Gespräch mehr Wissen zu haben, denn schon das Fernsehen hat uns gelehrt: “Wer, wie was; wieso, weshalb, warum? Wer nicht fragr bleibt dumm!”

Nun frei nach Kopf, die Definition die ich aus dem Abend mitgenommen habe:

Psychologe

befasst sich mit dem menschlichen Verhalten, wie wir reagieren, wie wir ticken.

Wann gehe ich dahin?

Einen Psychologen besuche ich, wenn ich mich beispielsweise in einer Lebenskrise befinde und dafür Hilfe will.

Psychiater

befasst sich mit psychischen Krankheiten wie Psychosen, Sucht usw.

Wann gehe ich dahin?

Meist werde ich von einem Psychologen oder dem Hausarzt an einen Psychiater verwiesen. Es handelt sich dabei ebenfalls um einen Dr.med., der sich spezialisiert hat.


enabled: