'''[Microsoft] [http://office.microsoft.com/access/%|%Access]''' is a [database] system 



** See Also **

   [a form for Access Database]:   

   [migrating ms access to other databases using XML]:   



** Tcl Programs for Access **

   [TDBC]:   

   [Tclodbc]:   

   [SQL Relay]:   



**  Other Programs for Access **

   [http://www.sourceforge.net/projects/mdbtools/%|%MDB Tools]:   a package of libraries and utilities that allow Unix-like systems to natively read Microsoft Access database (MDB) files.  Dissects access files.  I believe that it offers an [API] which can be pretty easily wrapped and though it is not completely stable (according to traffic on their mailing list) seems to do a pretty good job.  They also offer a rudimentary ODBC driver."



** Description **

'''Jet''' is Access's persistence back-end, and the aspect of Access most
likely to interest Tcl developers immediately.  [Microsoft Windows%|%Windows]
has bundled Jet for several years now--write a Jet-dependent application, and
it should work fine on any Windows host since [Windows 95] (?).

In 2002, Jet was renamed to '''MSDE'''.  [http://mdbtools.sourceforge.net/]
might explain more.  It's also called "MDB" and "MSDB".

[RS]:  A simple way to interact with Access is via [CSV] files (see also the
links there) - plain text files with comma-separated values (in German locale,
Access does not allow to use commas though, because it is considered decimal
separator, so use semicolons instead; Excel accepts commas though...




** `[database configure]`  **

[Scott Gamon]: I'm pasting in this c.l.t. post by [Kevin Kenny]


quoting [Kevin Kenny]:   

You can use [database configure] to create an Access database - in fact, you
don't even need Access on the system.  (You do need Jet, but I don't think I've
ever seen a Windows box without it.)

Try the following code.  It creates an empty MDB file at the location the
user gives and then opens it.

======
package require Tk
package require tclodbc

# Prompt the user for a database to create
# (For opening an existing database, use tk_getOpenFile instead of
# tk_getSaveFile)

set types {
    {{Access Databases} {*.mdb} }
}
set fileName [tk_getSaveFile \
   -defaultextension .mdb \
   -filetypes $types \
   -initialdir ~ \
   -title "Create Database"]

# Quit if the user cancels.

if { ! [string compare {} $fileName] } {
    exit
}

# Create the database.  (Omit this if opening an existing database)

set driver {Microsoft Access Driver (*.mdb)}
database configure config_dsn $driver \
    [list CREATE_DB=\"[file nativename $fileName]\" "General"]

# Connect to the database.

set connectString DRIVER=$driver
append connectString \; DBQ=[file nativename $fileName]
append connectString \; {FIL=MS Access}
append connectString \; EXCLUSIVE=Yes
puts $connectString
database db $connectString
======



** Dump a .mdb Database **

[RS] 2008-06-18: Here's a cute little tool to dump a table of a .mdb database
in to stdout in semicolon-separated format:

======
#!/usr/bin/env tclsh

set usage {
   usage: mdb2csv.tcl mdbfile table > csvfile
   Dump a MS Access database table to stdout in CSV format.
   MS Access must be running for this to succeed.
}
if {[llength $argv] < 2} {puts stderr $usage; exit 1}

proc main argv {
   package require dde
   foreach {mdbfile table} $argv break
   set request "$mdbfile;TABLE $table"
   set it [dde request MSAccess $request All]
   foreach line [split $it \n] {
       puts [string map {\t ;} $line]
   }
}

main $argv
======



** Misc **


[etdxc]:  Just a quick note. When using tclodbc to work with Access memo
fields, if you insert a record which contain a memo field that contains a large
amount of data, you may get a problem rereading it. TclOdbc returns all the
data associated with the memo, spurious or otherwise. In a rush (as always) I
found the easiest solution is to store the memo as a two element list (or as
two separate fields), index 0 contains an integer 'size' of the memo and index
1 the memo itself. Use `[lrange]` to extract the actual stored text.

Of course there may be a (lot) better method. If so, please let me know.


<<categories>> Database | Application | Windows