Hi, A general statement. I had said I would open a page at the MSA-Base wiki page dealing with data transfer and have not had a chance to that yet. Glad I did not, in following this thread and the related threads there is a lot of very good information to be gleaned.
Assuming no objections I will still open that new page, and extract parts of the responses from these emails for it. Some specific comments below. >-----Original Message----- >From: Andreas Saeger [mailto:[EMAIL PROTECTED] >Sent: Wednesday, September 5, 2007 01:15 PM >To: [email protected] >Subject: [dba-dev] Re: problems importing MSA files into OO Base > >Marc Santhoff wrote: >> >> I wonder if this difficulties are >> - a special problem using ubuntu >> - only occur when copying from .mdb >> - or are similar when using other database types >> >> Anyone having similar experiences not using an access database? >> >> Marc > >Hi Marc, >Just a quick and dirty outline from my point of view. >Ubuntu's community built of OOo includes the (still experimantal) >mdb-sdbc driver. It allows read-only access to tables and some SELECT >queries. Nothing special, but limited. >Many users want to convert their old Access databases to something >usable (writable, with relations) when they move over from Windows to >Linux. At the current stage this requires a transfer to some other >db-engine, let's say OOo's native hsqldb. >Our office provides drag&drop or copy&paste import, which pops up a >wizzard. That wizzard has some problems related to date conversion. I >remember an error message like "Could not insert value "03/28/1999 >00:00:00" due to conversion error....", where the date was obviously a >US-American one, including a zero time fraction. The source was >MSAccess. I used Ubuntu's built of 2.0.2 at that time. >Another issue is an offset by two days when you import dates from >numeric spreadsheet cells. You should substract "2" or convert to ISO >strings before transfer. This issue has already been addressed in the main source code. Not sure if it is included with 2.3 ( I think so ) or 2.4. >Hsql import through csv requires at least two more or less complex SQL >commands. You have to be aware of all kinds of delimiters and -of >course- date strings. Also an issue being addressed by the developers - support for linking these via GUI. Not sure how far along that process is. There are also 1 possibly 2 people working on extensions to handle this. I have seen the early work of one of these, not really that far along but that was over a month ago now so maybe he is getting close. >No matter what kind of import you succeed with, you get unrelated tables >without auto-increment fields. Setting an existing field to >"auto-increment" will create a new field from scratch with new values, >not nessesarily matching with other tables' foreign keys. It is up to >the "typically unexperienced MSAccess user" to create new auto-ids, >matching with foreign keys in other tables. Not entirely true. For single column PK the key ( pun intended ) is to mark the field as being unique during the import and assign no PK. There can be no NULL values in the column either ( only one wold be possible anyway ). After this the column type can be changed to IDENTITY without having to drop and repopulate it. If you use the GUI to make the change, again do not assign it as the PK. Base will complain when you save, ignore it, DO NOT let it create a PK column. HSQL will automatically set this as the PK next time the tables are refreshed. Compound keys a not so easily handled. Drew
