access MS Access from Oxygen?

[forummarcel]

Good Morning Everyone, Dear XML experts

I noticed that some database connections are only available for the enterprise version, however i haven't found any clear statement about the connection to MS access.

Is there a way i could access my Access-DB from my Dita-Code in Oxygen, and how could i accomplish that?
Thanks in advance for any hepful advise.
Marcel

[Radu]

Dear Marcel,

If for the MS Access database you create in the Windows operating system an ODBC data source you can try to configure a JDBC-ODBC data source in Oxygen:

http://www.oxygenxml.com/doc/ug-oxygen/ ... ction.html

and then use the File menu->Import->Database data to import it to generic XML. Then possibly apply an XSLT stylesheet to produce DITA from it.

This should work in any flavor of Oxygen (professional or enterprise).

For Oxygen 17 (which we plan to release in April-May this year) we plan to implement a special "convert" protocol which would allow you to refer in the DITA Map or in DITA topics directly to other resources like Excel spreadsheets, HTML content and so on and to have that converted on-the-fly to DITA when publishing. We will also consider providing support for Access to XML and then to DITA conversion using the convert protocol. I will try to update this forum post with more details when we release 17.0.

Regards,
Radu

[forummarcel]

Dear Radu
Thanks for this promising reply.

Unfortunately i couldn't setup the database connection to access from my system. although the installation of the AccessDatabaseEngine worked, the user-dsn of the system is not changed accordingly. It seems there are some driver issues in regards 32 or 64 systems (i use win 7, 64 bit).

Have a nice evening.
Marcel

[Radu]

Hi Marcel,

Setting up ODBC connections to MS Access might be quite tricky, you should try to google for your problem, maybe you will find something.
Another possible way to import the content might be to open the table in MS Access, select the entire table contents, copy and then open in Oxygen a DITA topic in the Author editing mode and paste in it. Oxygen will try to see if the clipboard contains an HTML flavor and try to convert it to DITA.

Regards,
Radu

[forummarcel]

Good evening Radu

Unfortunately even with google i couldn't find out a solid solution to that. I am looking for a way to integrate live-MS-access-data into my workflows, and a simple import resp. c/p wouldn't be the preferred solution.

According to your above statement, i hope that in your next release, Version 17, a more straight forward connection to MS access would be available. Having read the oxygen documentation, i conclude that such DB-Connections are nowadays already available for popular DB-standards such as MySQL, postgreSQL, etc.

Please correct if i misunderstood. Otherwise i am looking forward to Version 17:)

Have a good evening.
Marcel

[Radu]

Hi Marcel,

Even if we manage to make the special Oxygen 17 "convert" protocol work with Access databases, unfortunately it will still need an ODBC data source configured for it. Right now Oxygen cannot extract content from an MS Access database without having an ODBC connection configured in the operating system.

Regards,
Radu

[forummarcel]

Hi Radu

Ok. The windows environment is now ok i think. But still i cannot establish a proper connection with a test-db from oxygen. probably the problem lies in the URL-String from the connection panel of oxygen:
URL: jdbc:odbc:C:/neu/test-odbc.accdb

having entered the above statement resulted in an error message referring to a missing source name and unspecified standard driver (although in the DNS entries the access drivers seem all be there, and the test database is specified).

From java codes in the internet i found this string, however i am not sure how to enter it in the URL-Field
jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:/neu/test-odbc.accdb

Any idea how this finally can be solved?

Best regards,
Marcel

[Radu]

Hi Marcel,

Assuming you have a 64-bit OS and you installed the 64-bit MS Access Drivers and the 64-bit version of Oxygen, when you define an ODBC data source in Windows you specify a name for it like MY_SOURCE.

Then when you open Oxygen in the Preferences->Data Sources you can configure a new connection of type JDBC-ODBC with an URL like jdbc:odbc:MY_SOURCE.

Then you can use the Oxygen File->Import->Database data menu to import data from the table as XML.

Regards,
Radu

[forummarcel]

Thanks for the description. Unfortunately i still cannot make an import. Following are my tools:
-Windwos 7, 64 Bit
-Oxygen 15.0
-Access 2007 (i heard there is no 64 bit odbc driver for this ms office tools)
-ODBC-driver: So i use the 32-bit driver located under C:\Windows\SysWOW64 (Yes the naming is really confusing too)), and i am able to define the connection for ms-access under the system odbc-sources as expected.

In Oxygen, on the other hand, i configured an jdbc-odbc bridge with the correct naming to a simple access test database. When i try to connect under the Oxygen import Window, the following error-message appears, which again indicates to a driver problem: "java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application"

I assume this is happening since oxygen is a 64 application and cannot call the 32-bit driver properly. is there a solution to this one?

Thanks again.
Marcel

[Radu]

Hi Marcel,

Actually you can download an install a 64-bit driver for ODBC from here:

http://www.microsoft.com/en-us/download ... x?id=13255

I installed it, tested to access an ODBC data source from Oxygen (64-bit kit) and it worked for me.

Or if you want to use the already installed 32-bit ODBC driver, just make sure you download the 32-bit version of the Oxygen installation kit from our web site in order to try to connect to it.

Regards,
Radu

[forummarcel]

hi radu, thanks for the links. i tried this one, but i can't finish the installation because it tells me that i have various 32bit MS Office products installed.

So installing the oxygen 32bit version is a valid option. can i install this version whilst keeping my 64bit installation untouched?

Best regards,
Marcel

[Radu]

Hi Marcel,

i tried this one, but i can't finish the installation because it tells me that i have various 32bit MS Office products installed.

I know about this one, I had to uninstall all my Office viewers to get the driver to install.

So installing the oxygen 32bit version is a valid option. can i install this version whilst keeping my 64bit installation untouched?

When the 32-bit version is installed it will automatically be installed in the other 32-bit Program Files folder. At some points it will ask you if you want to associate certain file types with it or if you want to create start menu and Desktop shortcuts for it and you can avoid doing that.

Anyway, the same preferences will be used in the 32-bit version because preferences are stored in the user home.
What you should avoid to do would be to have both the 32 bit and 64 bit Oxygen applications opened at the same time because the last closed Oxygen will overwrite the preferences saved by the first one.

Regards,
Radu

[forummarcel]

Hi Radu
Thanks for your reply. I could install the 32bit-Version of Oxygen without any problems, and the Import from an access-DB worked straight away. Thanks a lot for the really helpful advise.

Have a good afternoon.

Marcel

[forummarcel]

Hello Oxygen 17
How about the announced convert protocol and to what extend it has been implemented in the new release of oxygen 17?

Thanks in advance for the update.
Marcel

[Radu]

Hi Marcel,

We ship Oxygen 17 with the convert protocol but unfortunately we did not have the time to implement a processor which would convert database tables to XML so it would not yet be interesting for your Access conversion. I will try to increase the issue's priority.

Regards,
Radu