WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
OpenOffice

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Mapping ADO to OpenOffice database

 Keywords: ADO OpenOffice LibreOffice
Developer: Stan Littlefield

I've attached the product of some intense hair-pulling... but don't bother downloading unless you plan to test. The script reads from a choice of 3 tables in the access .mdb file, saves them as a persisted xml recordset - and creates (1) a new OpenOffice Base file (2) a Table in that file corresponding to the xml structure (3) and in 1 instance inserts a record from the xml to the OO Table.

First, trying to get answers on how to do this in a scripting language is tricky on the OO forums. To be fair, many of the 'gurus' there a Linux or Mac programmers and don't even know what Windows or ADO are. So I had to carefully recruit some suggestions/pointers by logging in as a 'newbie' [user=laffdb] and asking basic directions, albeit throwing in phrases like 'Mspersist Recordsets' which finally got me started. After that, with apologies to Yoda - there was no do, there was just try:

  1. OpenOffice data tables don't work like Windows, no addnew - and what they offer as a workaround is not generic and requires a lot of hard coding.

  2. So I embarked upon creating "INSERT INTO.." SQL - again, there are problems with syntax and probably the version of OO you are using.

  3. Then there are conversion issues. The script has my best shots at converting Double and Timestamp fields.

  4. Finally it seems OO requires a primary key - so if one is missing (one of the samples in the .mdb has this) - it is created.
I'm going to be stuck working with OO, but will still create/manage Access files with ADOX and ADO {don't really need Office}. I am personally pleased I got this far, and will be more comfortable in the transition.

Based on days of searching and visiting forums and blogs I would postulate the script is the best start at integrating OO and ADO from a scripting perspective you will find. Hopefully it gets placed in the tech db as a future reference - it is heavily commented.

[ OO_XMLRS2BASE.zip ] 

Article ID:   W18156

Filename:   Mapping ADO to OpenOffice database.txt
File Created: 2019:08:14:09:27:28
Last Updated: 2019:08:14:09:27:28