andrew wrote:
Barbara Duprey wrote:
(Apologies in advance if this has already been responded to, just got
the confirmation for subscription and I can't find my original
question on the site, did not get anything cc'ed.)
I'm running OpenOffice.org 2.0.4 on a WinXP platform. I have a .dbf
of a fairly large and active table (several thousand records, a
couple hundred added or modified twice each month) linked to an .odb
that defines a number of queries. Most of the time when I use the
queries to modify or add records, everything works fine.
BUT sometimes updates are applied (behind the scenes, not where I'm
working) to the wrong records. This is extremely hard to detect and
correct. I suspect that the real issue is two-fold: (1) relative
record numbering is used to record the changes, and it gets skewed
when new records are added and sorted by the query into their
appropriate positions (based on a street-name, street-number
combination that is not unique), with the actual file not being
updated from the relative record data until some later time,
regardless of the use of the save actions from the query and from the
main page of the database; and (2) there is no primary key.
Another, and quite likely related, mystery is that after I've
completely closed all OpenOffice.org applications, and even restarted
Windows, the first attempt to bring up anything in OOo presents a
dialog about "recovering" some unnamed file. When I do that, it opens
up the .odb.
This issue is a real killer for me. Is there any way for me to make
the .dbf into a proper database table, with an auto primary key,
instead of a linked .dbf? Or do you have any other suggestions? Thanks.
Hi Barbara,
You can migrate your data to an embedded database in Base fairly easily.
You already have your .dbf file linked to a Base file, so the first
step is to create an empty embedded database file.
File>New>Database
At this point just click on 'Finish'
You are asked for a name for the new Base file - name it as you like.
This new, empty, database is open on your desktop.
Open your current Base file that links to the dbf file - most likely
on your MRU list- File>Recent Documents
Alright now you can use drag and drop to move your data.
In the new database select the Tables icon.
In the old database select the Table icon.
Drag your table from the Old to the New database.
The 'Copy Table' dialog opens.
On the first page make sure the following options are selected:
'Definition and Data'
'Create primary Key' - By default this new field will be named "ID"
and is an auto_incrementing INTEGER field placed as the first field in
the new table.
Leave the table name the same as it was in the old database so that
your queries can move over easily.
Select 'Next'
This is the 'Apply Columns' page - select all columns
At this point you can try just selecting 'Create', chances are fairly
good that the data will be migrated without a problem.
OK, now you will want to move over any queries that you have in the
old database.
Select the Queries icon in each database.
Drag and Drop each of the queries from the old to the new databse.
NOTE - this will not bring over the query name, so you most likely
want to edit each back to the original name.
Assuming that when you copies the table you used the same name these
queries should run unchanged.
Once small gotcha is this - Using the embedded database for a record
set to read/write it must have a primary key,
so if you have a query that is of type SELECT * FROM Table you will be
fine, but if you select the actual fields by name
you may have to edit the query to include the new ID field to make the
result set read/write otherwise it will be read_only.
If you have any forms you can also move them using the same drag and
drop technique.
Alright then, if you have gotten this far then all your data has now
been moved into the new Base file. You can work now with the
new database. Remember you will no longer be updating the .dbf file at
all, rather the data is held within the Base file as an embedded
HSQLdb database table.
Of course you are left with another problem now - multiple records
with the same Name / Address pair but different ID values,
you have to fix that based on your knowledge of your database.
A couple of other things - I see that Marc has answered your email
also and used the Calc module to act as a go between for this transfer.
This is a common action with many OOo users it seems, but I would
argue against doing this - for two reasons.
1 - You just don't need that extra step.
2 - Second, it can cause some data integrity problems - specifically
with Dates.
3 - 2.0.4 is far from being the most current version of OOo and in the
case of Base many bug fixes have been added in subsequent releases.
You may very well want to consider upgrading your installation to the
current release 2.2.1 ( although 2.3 is due out in about 6 weeks so
perhaps
you will want to wait for that release - one big reason to do so would
be the new report engine )
4 - I am not absolutely sure but I believe you can run Base against a
.dbf file without having a Java Runtime Engine (JRE) installed don
your machine,
you CAN NOT do so with the embedded database model. If you run into
problems be sure to check that you have a JRE installed.
Sincerely,
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Thanks, Marc and Drew -- I should have come here before I started on the
long and winding road that got me to this point, looks as if I have
enough to work with now to make this a real database. And yes, I'm
expecting to upgrade my version (got 2.0.4 just before 2.1 came out, and
there's been so much action on new versions I thought I'd wait a bit
more. 2.3 sounds like a very good bet to me, especially looking for
better report stuff. I haven't been able to do enough with the current
wizard-generated ones to get what I want, so I have a real kluge (copy
from query, paste-special into a Writer doc that has my header/footer,
copy and paste the data into an empty table that's set up as I want).
I've downloaded the OOo version with JRE, expect to do the same for 2.3.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
