OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Base and a Constantly Changing DBF file

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
donavan01
Newbie
Newbie


Joined: 31 Dec 2007
Posts: 4

PostPosted: Mon Dec 31, 2007 7:32 am    Post subject: Base and a Constantly Changing DBF file Reply with quote

Ok this may have been answered somewhere but to be honestly I don't even know what I need so its hard for me to search for it. Here is what I have going on lets see if anyone out there can answer this one. I have a 20 year old system that is tied to some lab equipment that we use for coal testing. The equipment runs back to an old win98 PC which is running a DOS program that has long since been abandoned by the manufactures. I can't upgrade the program because it will mean upgrading the hardware which is somewhere in the neighborhood of $100k at least. The program saves everything in what I believe is a dBase IV file (.dbf) and using Calc or Excel I can easily see the data inside the .dbf file. What I am trying to do, if I can figure out a way other than writing my own custom app, is to do a daily export of the data from the dbf file into a Base DB, then create some spiffy forms to mess with the data. Each daily the dbf file is wiped out by the ancient program and a new on is created. Obviously I can import it using calc or excel but this is time consuming and the other employees here aren't very good with computers which means that I would have to do this every day ... not an optimal situation. My database experience is somewhat limited and I haven't used open office much at all but I do understand DBs and have a decent knowledge of SQL so I should be able to follow along even if I have to do a little research. Mainly what I am looking for is a way to import the dbf file into an already existing database automatically via a script or macro or something. Can this be done using Base or am I just asking the impossible?

Thanks for your help.

Donavan
Back to top
View user's profile Send private message
newuser4
General User
General User


Joined: 17 Jul 2006
Posts: 49

PostPosted: Mon Dec 31, 2007 8:09 am    Post subject: Reply with quote

I do not suggest to import data into a Base file using the embedded HSQL db engine, it's the slowest SQL engine I've ever used.

I'd try connecting to your Dbf file using the ODBC driver, it's fast, it supports SQL and it doesn't require primary keys. Download the Jet Engine 4.0 for Windows 98 from Microsoft web site, install it, create a new data source selecting ControlPanel -> ODBC -> New ->Microsoft ODBC driver for Dbase and browse to your dbf file.

Then you will be able to create a new Base file from ooo using file-> new-> database-> connect to an existing datbase -> odbc -> browse and choosing the dsn you've just created.

From Base interface you will be able to create new tables and append existing records to another table using SQL commands.
Back to top
View user's profile Send private message
donavan01
Newbie
Newbie


Joined: 31 Dec 2007
Posts: 4

PostPosted: Mon Dec 31, 2007 8:42 am    Post subject: Reply with quote

I am not limited to windows 98 I have a win2k server box and a couple of XP machines would it be better to run this on one of the newer OS's, I am coping the file out to one of the other machines anyways for backup purposes. Would Jet be my best bet here or something like MSDE or SQL server. Like I said I'm not very good with databases so bare with. Also let me make sure I am getting what you are saying. Set up the database engine on the 98 box using jet4( or whatever) ... and transfer the DBF file into a table (say Table1) on my DB. Table1 would then be linked back to the DBF file and will replace the data as the original DBF file changes. Therefore I would need to move the data over to a new table that is not linked to the DBF file using some SQL query. Then the next day I would just do it all over again minus the linking and all that. Do I have this correct?
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Mon Dec 31, 2007 11:42 am    Post subject: Reply with quote

Assuming the new daily dBase file has a consistent name try File > New > Database then Connect to Existing Database with the type in the drop down as dBase and browse to the files directory - for dBase you do not browse to the file itself. Now any dBase file, old or new, in that directory will be available but your consistently named one should update automatically if it replaces the old one.
Back to top
View user's profile Send private message
donavan01
Newbie
Newbie


Joined: 31 Dec 2007
Posts: 4

PostPosted: Mon Dec 31, 2007 12:02 pm    Post subject: Reply with quote

JohnV: I tried doing what you said it it seems to work fine. Only one question being that it had me create a new database how do I go about getting the daily data to update this new data base will it append to it our just over right it? Do I need to do some SQL to copy the data to a new table or database? And if so is there a way to get ooBase to script this to do it automatically. Ok so I guess that was more than one question.
Back to top
View user's profile Send private message
newuser4
General User
General User


Joined: 17 Jul 2006
Posts: 49

PostPosted: Mon Dec 31, 2007 10:43 pm    Post subject: Reply with quote

Records cannot be copied between tables with SQL commands while using the dbase driver which comes along with Ooo. You should be able do it manually right clicking the existing table and selecting copy/paste option.

The wizard will guide you into creating a new table or appending record into existing one.

With Microsoft ODBC driver SQL is supported, so you could create a simple query like the following:
Code:
INSERT INTO bigtable (field1, field2, field3)
   SELECT sourcefield1, sourcefield2, sourcefield3 FROM dailytable
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Tue Jan 01, 2008 8:30 pm    Post subject: Reply with quote

What I explained would simply overwrite the old file with the new one. If you need to append the new data to the old then you probably want a macro that does the SQL work for you and that's beyond me.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group