a form for Access Database

JM 2005-Dec-11 Form for Access Database.

Simple form for the sample database Northwind.mdb, which is included with the Microsoft Access Installation.

Enters new information in the "Employees" table.

Illustrates the use of a combo box loaded with information in table "Employees"

Just to play with dates, it inserts today's date as the hire date.

If a secured database is used, just enable the variables: mdw,user and password with the appropriate values, and use the database connect call that uses parameters: SystemDB,UID and PWD.


 package require Tk

 proc store {} {
 global repTo

 set now [tclodbc::TclTimeToSqlTime [clock seconds]]
 db statement storeIT "INSERT INTO Employees (LastName,FirstName,HireDate,ReportsTo) VALUES (?,?,?,?)"\
                                             {{CHAR 20} {CHAR 10} {TIMESTAMP} {INTEGER}}

 if {[catch {storeIT run [list [.ln get] [.fn get] $now [lindex $repTo 0]]} errores] == 1} {
    puts "errores:\n$errores"
    update
    if {![string compare [lindex $errores 0] 23000]} {
              # datasource not found, create new
        tk_messageBox -message "Error, already entered"
    } elseif {![string compare [lindex $errores 0] 42000]} {
              # other error
               puts "Record(s) cannot be added; no insert permission on table"
    } else {
              # other error
               puts "error 3 $errores"
               error [lindex $errores 2]
    }
 } else {
    tk_messageBox -message "OK, Record Stored"
    .ln delete 0 end
    .fn delete 0 end
    focus .ln
    storeIT drop
 }

 }


 proc loadCombo {w sql} {
    global driver dbfile mdw user password db
    db statement stmt $sql
    set records [stmt run]
    stmt drop
    $w configure -values $records
 }


 package require BWidget
 package require tclodbc
 set connected no

 #driver
 set driver "Microsoft Access Driver (*.mdb)"
 #Database Path
 set dbfile "C:\\Program Files\\Microsoft Office\\office\\samples\\Northwind.mdb"
 #user account and where the workgroup information is
 #set mdw "C:\\My documents\\BasesDeDatos\\unfiled\\Control.mdw"
 #set user admin
 #set password ""

 if {![file exists $dbfile]} {
        tk_messageBox -message "Couldn't open:\n$dbfile"
        exit
 }

 # Database Connection
 database connect db "DRIVER=$driver;DBQ=$dbfile"
 #database connect db "DRIVER=$driver;DBQ=$dbfile;SystemDB=$mdw;UID=$user;PWD=$password"
 #---------------------------------
 label .lblln -text "Last Name:"
 entry .ln -width 20
 #---------------------------------
 label .lblfn -text "First Name:"
 entry .fn -width 20
 #---------------------------------
 label .lblcbo -text "Reports to:"
 ComboBox .cboRepTo -autocomplete yes -textvariable repTo
 #---------------------------------
 button .btn   -text "Save Record" -command store
 button .salir -text "Exit" -command {
 if {$connected == "yes"} {
        db disconnect
 }
        exit
 }
    
 pack .lblln .ln .lblfn .fn .cboRepTo .btn .salir
 loadCombo ".cboRepTo" "SELECT EmployeeID, LastName, FirstName FROM Employees ORDER BY LastName;"

Fetching backrefs...