Connect two MS Access Tables / Excel Tables

[Former Member]

Hello Gurus,

i am new to this VC Tool and i have a question. There are 2 big MS Access tables existing on our file server and i would like to use bot tables in the Visual Composer - of course i will try to connect the tables via unique key fields. Is this possible with the Visual Composer? The next question is the same but this time i want to use Excel files instead. In both file types i got some unique fields. The merge between all files should be possible.

What are the necessary steps. Please help.

Regards,

Memo

[Former Member · Accepted solution]

Hello Prakash,

thank you very much. But i cant find in this document anything about MS Excel or MS Access?

What is the correct page?

Best regards,

Memo

[Former Member · Accepted solution]

This shows how to setup a JDBC connection. There are JDBC drivers that exist for Excel and for Access. You will have to use a similar process to set up those drivers. I've used Vista JDBC driver for excel in the past.

[Former Member · Accepted solution]

Hello Prakash,

as i understand i must create this connection only once. After the installation any VC designer can access any Excel or Access document on any file server! Is this correct Prakash?

And the Vista JDBC driver is also included in NW2004s? Where can i get it if not?

Thank you in advance for your help.

Regards,

Memo

Message was edited by: Mehmetali Erbil

[Former Member · Accepted solution]

The Vista JDBC driver is a 3rd party driver. Just google for it.

Also, The file is specified in the connection of the driver, so this is not adhoc. You can make it adhoc by loading a file to a particular location the driver points to. This location can be in KM. Scott Cairncross wrote a how to guide on loading files and KM document integration with Visual Composer.

[Former Member · Accepted solution]

Hello Prakash,

thank you very much. This sounds great!

I will try now.

SAP is Great. Many problems will be solved.

Thank you,

regards

Memo

[Former Member · Accepted solution]

BTW: join the tables on MS Access side. VC does not offer a join-functionality. That also means you cannot join - let's say - an Excel-Sheet with and MS Access table on VC side.

Mario

[Former Member · Accepted solution]

Scott Cairncross wrote a blog on combining data via a service. This offers join capabilities:

/people/scott.cairncross/blog/2006/09/22/extending-visual-composer-through-web-services-vol-1

[Former Member · Accepted solution]

Hi Prakash,

you wrote about a how to guide "on loading files and KM document integration with Visual Composer". Where can I find it? Can you give me a link please?

Thank you.

Regards,

Christophe

[Former Member · Accepted solution]

Hello Christophe,

I'm not sure, may be you look for

<a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/a303e745-0d01-0010-b280-802bca844b8f">How To… Integrate BI Document Store and SAP Analytics Applications</a>.

Best Regards,

Marcel

[Former Member · Accepted solution]

Also, this document was written by Scott Cairncross, not me.

Cheers,

Prakash

[Former Member]

Hi,

I see that this question is marked "answered". Could you please show me step by step the way to connect Sap Portal to back-end system such as Access Tables or Excel tables ? I've tried to connect to Max DB database but it doesn't work. I think the problem is in "user mapping" step but I don't know how to resolve it.

Thank you very much.

T.Ti

[Former Member]

This is possible.

See this how to guide for setting up JDBC connectors:

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/92d80512-0a01-0010-32a3-cd3...