Miscellaneous Database Tips

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 17:04, 27 February 2009 (edit)
Sardisson (Talk | contribs)
(Microsoft Access-related tips - add Ed's latest find)
← Previous diff
Current revision (06:58, 28 February 2009) (edit) (undo)
Sardisson (Talk | contribs)
(redirect this to the new MS Access page, since that's why we linked to this page most of the time)
 
Line 1: Line 1:
-{{fixme}}+#redirect [[Working with Microsoft Access databases]]
-<!--These tips are copied from the old Configuring and Using Databases page-->+
-The following are miscellaneous database-related tips which currently do not have enough content to warrant a full article and which have no other home.+
- +
-==dBase import tips==+
-===Connecting to <code>dbf</code> files in NeoBase===+
-You can use [[NeoBase|Base]] to connect to one or more <code>dbf</code> files.+
-#Go to the {{Menu|File}} menu and choose {{Menu|New}} and then {{Menu|Database}}+
-#In the first pane of the {{Window|Database Wizard}}, Choose {{prefName|Connect to an Existing Database}}+
-#Set the drop-down to {{prefName|dBase}}+
-#Click {{Button|Next>>}}+
-#In the next pane, click on the {{Button|Browse}} button+
-#Navigate to the '''folder''' in which the <code>dbf</code> files are found+
-#*You cannot choose a file, you must select a folder.+
-#Click {{Button|Open}}+
-#Click {{Button|Next>>}}+
-#In the {{Section|Save and Proceed}} pane, select the settings as desired+
-#Click on {{Button|Finish}}+
- +
-===Importing into Calc===+
-The table structure of the <code>dbf</code> file for all the fields containing leading apostrophes say they are characters. This can easily seen if you import the DBF file and look at the first row. All columns have a header defining it. C is for characters (text), N would be numeric.+
- +
-So Calc imports the values correctly as Characters. As the number format for the default template is set to numeric, a apostrophe is placed in front of the value to make it a text.+
- +
-To change such text values to real numeric values, you have several choices:+
- +
-They are described in [[Converting a Text String to a Number in Calc|this article]] in NeoWiki.+
- +
-You can also change the field setting in the DBF from C to N and re-import the file.+
-<!--* use the Search and replace functionality. +
-*#Open the search and replace dialog and enter '.*' (without the quotes) into the search field.+
-*# Type '&' (also without quotes) into the replace field.+
-*# Now press on the {{button|More}} button and click the option {{prefName|Regular expression}}. +
-*# Click on {{button|Replace All}} and all the apostrophes should be gone.+
-Left in comments, just in case…+
--->+
- +
-===Issues with DB2K files===+
-older <code>dbf</code> files in the DB2K format are not compatible with NeoOffice or OpenOffice.org. When the connection is made, most fields will show <OBJECT> for data in Base and #N/A in Calc. If you have a Windows machine available, the solution is to use the [http://www.dbf2002.com/dbf-converter/ dbf-converter] program to convert these files to dBase-IV format, which NeoOffice does support.+
- +
-==Microsoft Access-related tips==+
-Well, a lack of drivers will definitely cause files to not be openable. Anything that requires the MS ODBC bridges won't work for sure, including Access files.+
- +
-'''''N.B.''' To our knowledge, '''no one has tried''' any of the potential solutions below to verify that they work with NeoOffice.''+
- +
-*An ODBC driver that works with MS Access on a Mac is the [http://www.actualtechnologies.com/product_access.php Actual] ODBC driver. It is a commercial one ($29), but it works with Access. (There was a [http://bugzilla.neooffice.org/bug.php?op=show&bugid=1535 report] that NeoOffice 2.0 Alpha worked with Actual's '''MySQL''' ODBC driver, but no one has tried the '''Access''' ODBC driver.) +
- +
-*The [http://www.mysql.com/products/tools/migration-toolkit/ MySQL Migration Toolkit] for the Mac can be found in the [http://dev.mysql.com/downloads/gui-tools/5.0.html MySQL GUI Tools Bundle] and should be able to be used to move data from an MS Access database to a MySQL one (which NeoOffice will happily deal with). (The Migration Toolkit is known to work with Access files on Windows; however, the migration process appears to require [http://dev.mysql.com/doc/migration-toolkit/en/mysql-migration-toolkit-accessprep.html preliminary preparations] to be done in Access before the migration can begin.)+
- +
-*[http://www.csv-jdbc.com/stels_mdb_jdbc.htm StelsMDB] is a commercial ($110; 30-day free trial) MDB JDBC type 4 driver that allows users to perform SQL queries and other JDBC operations on Microsoft Access database files (MS Access 2000, 2002, 2003, XP versions). Presumably NeoOffice could use the JDBC driver to connect to the Access database.+
- +
-*[http://www.hxtt.com/access.html HXTT Access Pure Java JDBC (1.2, 2.0, 3.0, 4.0) Drivers for MS Access] is a commercial ($300+; trial available) Type 4 JDBC driver that supports Access versions from 95, 97, 2000, XP, 2002, to 2003. Presumably NeoOffice could use the JDBC driver to connect to the Access database.+
- +
-*[http://forjamari.linex.org/projects/mdb2odb/ mdb2odb] is/was a Spanish project to develop an Access-to-OpenDocument Database converter. The project's binaries were available for Debian/Ubuntu, so it is unknown if the code compiles on Mac OS X (or if the project still exists).+
- +
-Other options are discussed on the Trinity forum: [http://trinity.neooffice.org/modules.php?name=Forums&file=viewtopic&p=23436#23436], [https://trinity.neooffice.org/modules.php?name=Forums&file=viewtopic&p=30559#30559], [https://trinity.neooffice.org/modules.php?name=Forums&file=viewtopic&p=51075#51075].+
- +
-==Related External Links==+
-*[http://wiki.services.openoffice.org/wiki/MSA-Base_Faq OOo wiki article on using Base to connect to Access] (currently requires Windows)+
- +
-{{botlangbarEN|[[Fr:Astuces diverses pour les Bases de Données|Français]]}}<!-- [[It:|Italiano]] [[Es:|Español]]}}-->+
- +
-[[Category:NeoOffice]][[Category:Tips and Hints]]+

Current revision

  1. redirect Working with Microsoft Access databases
Personal tools