Advice on Database use of MS Access files within OpenOffice

[McKiltie]

I am an old dog trying to learn new tricks. I have been very impressed with OpenOffice but one aspect is totally confusing me, databases. My needs are small. I have a data base file constructed on MS Access and I wish to use this file from within OpenOffice. If I cannot get this to work lots of my friends will not be getting a Christmas card / letter this year. So this is a serious problem.

I have read some of the advice on the forum re databases but unfortunately they were beyond my comprehension.

I would appreciate simple, very simple, advice on how to load in my Access database file to allow me to undertake a simple mail merge operations to enable me to make printable labels and letters.

Thanks in advance, Wee McKiltie

[JohnV]

Here's my version of the very easiest way to use the info in your Access db in OO.

1.Open the Access file so that you are in the table view and click the gray box at up upper left of your data so that all records are selected, then copy.
2.Open a new OO spreadsheet and paste. (These two steps avoid having to use the Access Export command, but see below*)
3.Now when you save the spreadsheet you need to do two things, (a) save it to the following directory, “Drive:\OOinstallDirectory\user\database\biblio” and (b) click the drop down menu by File Type and choose dBase. (Saving to this directory avoids the need to register the db with OO.)
4.Close the file and ignore OO's warnings about saving to a different format.

Now you can open a new Writer doc, press F4, click the + by Bibliography, the + by Table and then your file name and you will see its contents ready to work with.

* If you don't find the File > Export command intimidating then use it, select dBase III as the type, save to the directory in step 3 and ignore all other steps. The Export command does not delete the Access table and is really the quickest way. It's just a matter of what your comfortable with.

[Ezzelin]

I have another question about Access files: is there any way to import the forms from them? I have a database that has a lot of different tables and it's not enough to just be able to edit them. The forms are needed. Thanks.

[McKiltie]

Dear JohnV, Thanks for your helpful advice. I hope to try out your suggestion today.

Your President is visiting the UK today. If I bump into him I will let him know how helpful you have been! Thanks again, Wee McKiltie

[McKiltie]

Dear JohnV,

I tried out your recommendation and it worked perfectly. Thanks for the advice.

I find it strange that OpenOffice has a direct equivalent for MS Word, Excel and PowerPoint but not Access. I for one would find the transition much easier if there was an “Access” version within OpenOffice. Do you know if there is one planned in a future release?

Time for bed. Thanks again, Wee McKiltie

[JohnV]

Hum, how to answer this for an "old dog"?

It's true that the current OO does not have its own database however it provides a pretty good way to connect to other dbs.

There is direct support for dBase (up to, I think, version IV) which is what you are using with the file copied from Access. You can set up such a file directly in Calc. Instead of copying from Access just put the field names (column headers) in the first row and the field data in the rows beneath, then save as dBase and register this file's directory with OO as a data source (all other dbf files saved to the same directory will also be available).

OO allows you to set up a connection to almost any db including Access but this procedure may be more than you want to go through which is why I didn't mention it before. I don't know how to do this but somewhere there is a help file that someone else may point you to.

If you just need simple db tables stick with dBase. If you set them up in OO I'm pretty sure Access will be able to read them.

[dfrench]

http://dba.openoffice.org/testcase/test.html#msaccess

I view MSACCESS as a package of a single-user database , query, forms and report generators. Some may argue about the 'single user' but sharing an access database across an enterprise is not healthy
The only part that OOo does not provide is a OOo-specific database (that would be rather against the OO ethic and it can be argued that there are plenty of 'open' databases anyway). What it does provide is a common route to the common database market place via odbc and jdbc.

[Stair]

OK so I have successfully connected to a "single user" access database however what I really need is to connect to a shared database with a simple user rights scheme controlled by workgroup file co-located with the database file (never mind the wisdom of this, its just the way it is!)

So far nothing I have tried has succeeded, I keep getting the complaint that the workgroup file cannot be found.

Is it actually possible to connect to a shared access database with user rights, if so how?

Thanks

Stair