Edit an Access database "in place"?

[turnstyle]

I've managed to convert an Access database to a Base database -- but is it possible to open/edit/save as an Access database?

Other things "talk" to this Access database, so the easiest first step is to replace Access as the front end, whilst letting everything else continue to work unchanged.

Possible?

Thanks! -Scott

[DrewJensen]

If you are running under Windows then yes, you can read and update tables in the MS Access MDB file -

When you create a new Base file you select

'Connect to existing database' on the first page of the wizard. In the drop down box just below this you will find an entry for MS Access - select this and go to the next page, just follow the prompts and your should be fine.

If you are under Linux..then no such support.

[turnstyle]

Thanks -- but as best as I can tell, the process you describe converts the Access database to Base -- the last step saves it as a Base file -- is there way to open/edit an Access file without having to convert it to Base? (am I missing something obvious?)

Thanks again, -Scott

[DrewJensen]

No it does not do that at all.

It creates a Base odb file that 'connects' to the mdb file as an external data source. You work with the Base file to gain access to the tables in the MS Access mdb file.

What you do not get is access to the MS Access forms, reports and vba modules. In the case of MS Access queries you will see them as 'views' in the Base file, which means that you can execute them - if they do not take run-time parameters- and work with a read only snapshot of the data they return.

In other words you can do just what you are describing in your first post and it does work under MS Windows, build a new front end while working with the original MS Access database for data storage.

That is exactly the MS Access support under OpenOffice.org.

[turnstyle]

ah, that's interesting!

So the new Base file, as created by 'Connect to existing database,' is actually a pointer to the origianl database, rather than a copy of it?

If I do have that right, that sounds like it should work to me -- is the path to the Access file configurable in the Base file? For example, what happens if I move the Access file? (or, if you'd prefer, please feel free to point me to wherever I should be RTFM -- my apologies & thakns -- it's still my first 24 hours with OOo!)

-Scott

[turnstyle]

Given the info that the Base file can act as a pointer to the Access file, I've created a Base file and I'd like to change a cell via Base, and then open the Access database to confirm the change.

But when I open the Base document (ie, pointing to the Access document) the table seems to be read only -- when I created the file, I didn't check the box to make it read only -- anything I should check?

(thanks -- fwiw, I'll be sure to help folks with OOo when it's my turn)

-Scott

[DrewJensen]

OK...

A couple of things could make it read only -

One in Base a table must have a primary key to be updated via the GUI - you can update tables without a PK via SQL still.

In Base, as I said before, queries inside the Access database appear as views and views are on the same list as tables - so you can miss that this is the case - these are always read only.

There is a section on the wiki that covers this fairly well - needs to get better however.
http://wiki.services.openoffice.org/wiki/MSA-Base_Faq

Be sure to follow the link for connections
http://wiki.services.openoffice.org/wik ... oft_Access

[turnstyle]

Thanks -- it was indeed the lack of primary key -- is it not possible to use Base to edit a table within an Access database that doesn't include a specified primary key?

[DrewJensen]

You must update the table via SQL or using a ResultSet. The primary key restraint is only for using GUI controls for update.

[turnstyle]

Sorry, one more thing...

In the Access database, there's a table with a "memo" field.

When I edit that table's field types in Base, Base seems to identify that field as type "SQL Null [ ]" whereas I guess I would have expected "Text [ LongText ]" -- is something wrong?

For example, if I try to add a primary key via Base (so that I can then edit the table), I then get an error about that SQL Null [ ] field ("the column could not be changed. should the column instead be deleted and the new format appended?"). And if I try to change that field to Text [ LongText ], same thing.

If I ok it, I lose all the data.

[DrewJensen]

Not sure on that one, I have seen Memo fields come in just fine and I have also heard of a few others with the same problem - I think this is an ADO issue and works properly with ODBC as the connection type.

I would recommend giving that a try.

[turnstyle]

Can you point me to how to use ODBC rather than ADO?

fwiw, I'd love to see the rise of OOo, but I'd say that these seeming little things are what probably keeps most from taking the plunge. For example, I want to use Base with my Access file, and have it "just work."

But, so far...

Problem 1: can't use Base to edit an Access database table that doesn't have a specified primary key. And yes, any "proper" table should have one, but non DBA types probably never add them, and even DBA types might not bother for a small/low-traffic database.

Problem 2: doesn't work with memo fields (at least not for me) and if I ok the dialog, it'll lose all the data in the memo field.

I hope that doesn't sound too negative -- it comes tantalizingly close -- and I really hope it crosses that "no brainer" threshold!

(and I'm still eager to keep trying your suggestions -- here I'm talking more about broader adoption).

Thanks, -Scott

[DrewJensen]

Doesn't sound negative at all - and doubt you would find many that would disagree with you.

I think the example of how to setup an ODBC connection belongs in the wiki page...I'll try to get that taken care of ASAP and post back here when I do..