MS Access on Wine with ODBC
FedoraForum.org - Fedora Support Forums and Community
Results 1 to 1 of 1
  1. #1
    lmo Guest

    MS Access on Wine with ODBC

    Running msaccess 97 in wine can be accomplished with seemingly 99% full functionality*.

    I happened to have an old CDROM of MS Office 97.
    I wanted to install access to see if it could be used as a gui interface to postgresql.
    After many failed attempts to get the darn thing to work, I nearly gave up trying to install it, and on wine.

    Recently, I found this nice howto on installing MS Office 97 in wine at
    http://wine-review.blogspot.com/2007...inux-with.html

    I haven't found (or looked for) any details on later Offices / Accesses.

    As suggested, run msaccess 97 with wine windows version set to: Windows 98
    Having wine windows version set to Windows 98 during the install
    of Office 97 is probably a very good idea.

    Any comments about access requiring internet exporer installation
    are myths.

    I chose Custom installation to enable ODBC capability.
    Change Option for 'Data Access' and check the box:
    Data Access Objects for Visual Basic.

    Not sure that was necessary, but the description says:
    Code:
    Objects used by custom Visual Basic 
    applications to access external data. 
    Includes ODBCDirect functionality.
    Running msaccess 97 in wine needs DCOM98.EXE which is downloadable
    from a google search. (from M$).
    This is mentioned in the above howto.
    However, I added the /C switch which specifically asks for the install path.
    wine DCOM98.EXE /C
    Accept liscense OK
    In path to install DCOM98.EXE choose:
    c:/windows/system32
    Accept ovewrite "Yes to ALL"

    I installed DCOM98.EXE both before and again after installing MS Office 97
    because I do not know which files office installs or whether it reverts
    any of the dcom files. I don't know -- it worked!

    If you need to install DCOM98.EXE, then you will see that access
    will always start minimized until you do.

    If you are very good or very lucky, you will not encounter the
    following problem while running MS Access 97:
    Code:
    ************************************************** 
    * The dynamic-link library Commdlg failed: error code '0x1003'. 
    * The printer driver for the selected printer may be incorrectly installed. 
    * ... bla, bla, bla, bla ... OK 
    **************************************************
     Reason: The registry may not have the default cups printer in quite the right place. 
    (I found this tip from extensive google search, but cannot find it again) 
    
    Solution: Run regedit in wine and manually add some entries in the right place. 
    
    Namely, 'HKEY_LOCAL_MACHINE/System/CurrentControlSet/Control/Print/Environments' 
    Probably has the correct entries under 'Windows NT x86/Drivers/Version-3/xxx' 
    But maybe not under 'Windows 4.0/Drivers' 
    
    So the key xxx and values must be manually added under 
    'Windows 4.0/Drivers' 
    
    where xxx = default cups printer name (which incidentally, must be operable) 
    The name (xxx) above, may be determined by typing localhost:631 into the browser
    address bar and clicking on the Printers tab. 
    Choose the name displayed directly to the left of (Default Printer). 
    If there is only 1 printer, use that name. 
    
    For example: xxx = HP_deskjet_3320 
    
    In case 'Windows 4.0/Drivers' does not have HP_deskjet_3320, 
    a new key named HP_deskjet_3320 should be added under Windows 4.0/Drivers 
    
    and at least these string names/values be added under 
    Windows 4.0/Drivers/HP_deskjet_3320: 
    : Configuration File - wineps16 
    : Data File - <datafile?> 
    : Datatype - RAW 
    : Driver - wineps16 
    
    Once doing that, msaccess 97 can design/run reports and edit/use VBA code.
    If VBA code looks garbled, then change the font for modules to a
    readable font under Tools-->Options-->Module


    * One annoying non-function of wine msaccess 97 is in the use of
    PASS-THRU-QUERY
    Eg., you cannot set the recordsource for a form or report to anything
    that is not pre-defined in access as a query.
    Reportedly, this problem persists in access 2007 as well.
    A kludge workaround is to use VBA to redefine the sql in
    a pre-defined PASS-THRU-QUERY.
    Using a PASS-THRU-QUERY in combination with VBA to rewrite the sql in it
    is the only cool way to use msaccess forms/query_input_data to send the
    query to an external database such as PostgreSQL in it's native tonge.
    Very cool things are possible.


    Q: Can ms access be run from wine and use ODBC to hook up to PostgreSQL (or MySQL) ?
    A: Yes


    How about PostgreSQL.
    If PostgreSQL is not already installed:
    yum groupinstall "PostgreSQL Database"
    yum install pgadmin3

    !!! DO NOT DO THIS if you already have a PostgreSQL database !!!
    vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv vvvvvvv
    service postgresql initdb
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^
    !!! DO NOT DO THIS if you already have a PostgreSQL database !!!

    su - postgres
    cd data
    cp pg_hba.conf{,.orig}

    Use editor on pg_hba.conf to tweak it from:
    (Don't do this if you know what you are doing)
    Code:
    # "local" is for Unix domain socket connections only 
    local all all ident sameuser 
    # IPv4 local connections: 
    host all all 127.0.0.1/32 ident sameuser 
    # IPv6 local connections: 
    host all all ::1/128 ident sameuser
    Use editor on pg_hba.conf to tweak it to:
    (Don't do this if you know what you are doing)
    Code:
    # "local" is for Unix domain socket connections only 
    local all all trust 
    # IPv4 local connections: 
    host all all 127.0.0.1/32 trust 
    # IPv6 local connections: 
    host all all ::1/128 trust
    I am a dummy and setting the permissions to trust is much easier to
    deal with as long as I am willing to admit to being a dummy and
    I promise to only use PostgreSQL on the local machine.
    Don't do this if you know what you are doing.

    exit from 'su - postgres' with
    Code:
    exit
    su -
    service postgresql restart

    Set up a unixODBC default datasource with a database named test
    which will be made available to a 'user' named test:
    (access will pick this when you 'Get External Data' and 'Link table'
    and select files of type 'ODBC Databases')
    Database must match Username for this to work -- I think.
    (Note: On Fedora, unixODBC is installed along with the yum groupinstall "PostgreSQL Database" above.
    Others may need to install it separately.
    The configuration file odbc.ini is in the /etc directory on Fedora systems.
    The odbc.ini file might be in the /etc/unixodbc directory on other systems.)

    Use an editor to modify /etc/odbc.ini, adding:
    Code:
    [PostgreSQL] 
    Description = Test to Postgres 
    Driver = PostgreSQL 
    Trace = Yes 
    TraceFile = sql.log 
    Database = test 
    Servername = localhost 
    UserName = test 
    Password = 
    Port = 5432 
    Protocol = 6.4 
    ReadOnly = No 
    RowVersioning = No 
    ShowSystemTables = No 
    ShowOidColumn = No 
    FakeOidIndex = No 
    ConnSettings = 
    
    [Default] 
    Driver = PostgreSQL
    exit from su -
    Code:
    exit
    Pick pgadmin III from the menu, or at the terminal type
    pgadmin3
    File-->Add Server
    Properties:
    Code:
    Name: PostgreSQL 
    Host: localhost 
    Port: 5432 
    Maintenance DB: postgres 
    Username: postgres 
    Store password: uncheck 
    OK
    Higlight Databases
    Edit-->New Object->New Database
    Code:
    Name: test 
    Encoding: LATIN1 
    OK
    Highlight Login Roles in the Object browser window
    Edit-->New Object-->New Login Role
    Code:
    Role name: test 
    OK

    Click on database test
    Click the little SQL icon on the taskbar
    Enter the following and click the right-pointing green arrow (execute query):
    Code:
    create table test ( 
    fld_one serial, 
    fld_two varchar(50), 
    fld_three varchar(50) 
    )

    Enter the following and click the right-pointing green arrow (execute query):
    Code:
    grant select,insert,update,delete on test to test
    Enter the following and click the right-pointing green arrow (execute query):
    Code:
    grant usage on test_fld_one_seq to test
    Now, in wine, use access to use PostgreSQL

    From the menu, Microsoft Access
    or as regular user in a terminal:
    Code:
    wine .wine/drive_c/Program\ Files/Microsoft\ Office/Office/MSACCESS.EXE
    This will create a file test.mdb in the home directory.
    Code:
    Create a new Blank Database and name it test.
    Now to create a link table to that test database:
    Code:
    File--> Get External Data --> Link Tables
    In the 'Files of Type' chooser, scroll down to 'ODBC Databases'
    A 'Link Tables' box will pop up.
    Scroll down to ... you guessed it ... test
    Code:
    Highlight 'test' and click OK.
    A 'Select Unique Record Identifier' box will pop up
    and show 'Fields in table test'
    Code:
    Highlight fld_one and click OK
    Now table test will exist as a link table via unixODBC.
    Open test
    Enter into fld_two and fld_three
    hello world
    - and -
    have fun

    Q: Can openoffice.org-base access the same ODBC database too?
    A: Yes


    Openoffice Base can access the same ODBC database in much the same way.
    Some say that Ooo base can replace MS Access although it is a little clunkier.
    Both could be used according to preference.
    Last edited by lmo; 28th October 2007 at 07:40 PM.

Similar Threads

  1. MS Access on Wine issue
    By Daveda in forum Using Fedora
    Replies: 2
    Last Post: 21st August 2009, 09:19 PM
  2. Firebird ODBC
    By kipman in forum Servers & Networking
    Replies: 6
    Last Post: 2nd March 2009, 02:59 PM
  3. Replies: 0
    Last Post: 19th November 2006, 09:24 AM
  4. New to *nix - in an odbc nightmare
    By TomBodet in forum Servers & Networking
    Replies: 26
    Last Post: 26th May 2006, 05:39 PM
  5. WINE IE6 access to secure sites
    By Tangled_Kid in forum Using Fedora
    Replies: 0
    Last Post: 23rd August 2005, 07:24 PM

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
  •