Scheduled Import from MS Access?

[Former Member]

Hello Experts,

We have one scenario where the source database will be MS Access.

Requirement is to create a scheduled data import into MDM from MS Access database.

We are evaluating a possibility of directly importing data through Import Server.

But looks like there are some limitations with this approach...

1. Import Manager require path of MS Access file (.mdb or .accessdb), ODBC DNS option is no longer supported in MDM 7.1

- Don't know how import server will access .mdb file if it resides on different physical machine?

- Also it's not always possible to keep both MS Access file and Import Server on same machine.

2. Automatic processing option appears in Console Import Map properties.

- But do not have schedule time interval option (like Hourly, Daily, Weekly etc).

3. Import manager document talks about 64 character limit on Field Name Length.

- Hope this is a limit on Field Name length and not on Field Value length!

Please do share your valuable experience on such scenario.

Thanks & Regards,

Shaailesh.

[Former Member · Accepted solution]

as long as both the machines are on the same n/w and are accessible then what you can do is provide the entire logical path in the file ..

MSACCESSSYS_IP\C: ....

and see if it picks up ??

if you want to automate this process (scheduled job) write a simple program to extract the access DB and tranform into a XML and use ftp to put into the ready folder ..

then automatically Import server will pick up

thanks

-Adrivit

[Former Member · Accepted solution]

Hello Adrivit,

Thanks for your reply...

as long as both the machines are on the same n/w and are accessible then what you can do is provide the entire path

- I tried this but Import Server dose not pull data from access database.

if you want to automate this process (scheduled job) write a simple program to extract the access DB and tranform into a XML

- Actually, this Access database will be phased out in future. Hence we wanted to reduce the effort in routing Access data through some intermediate program or system.

[former_member205403 · Accepted solution]

Hi Shailesh,

I feel you can not schedule data import from MS ACCESS file using MDIS. Please check Note "Note 1009016 - Access, Excel, complex XML not supported on Win64 MDIS" for more details.

Best Regards,

Shiv

[Former Member · Accepted solution]

as i said before

for MDB -> XML

check these : free converter (effort reduced further )

• check with IT on usage on production environment though : u may run into fringed s/w usage issues **

http://www.perfectdatasolutions.com/access-to-xml-converter.html

http://www.brothersoft.com/ms-access-to-xml-convert-software-52036.html

thanks

-Adrivit

[Former Member]

Hi Shailesh,

You can schedule Import from MS Access using PI/XI. I mean Source MS Acess( using JDBC adater)>PIXI>Target XML file (MDM). In PI/XI, you cab schedule data from MS Access (daily, Weekly, monthly etc) using communication channel monitoring.

Regards,

Mandeep Saini

[Former Member]

Hello Shaailesh.

1)You can't import access file through import server only text or XML

Import server is working with inbound port

That is directory, which placed on the same physical mashine where sap import server has installed.

You should placed your source file in inbound directory.

One important thing - if somebody are opened your access file and import server try to start import it you can get error message because file has been opened by another process.

more is here(Page 252):

http://help.sap.com/saphelp_nwmdm71/helpdata/en/4b/71608566ae3260e10000000a42189b/content.htm

2) you can do it in mdis.ini file parameter "interval"

(Page 264):

http://help.sap.com/saphelp_nwmdm71/helpdata/en/4b/71608566ae3260e10000000a42189b/content.htm

3) you are right - length of field name should be not more than 64 symbols.

standard text field length - 255(333) symbols

or for large text - 2000 symbols

Regards

Kanstantsin Chernichenka

[Former Member]

Hello Kanstantsin,

Thanks for your reply,

Do you mean we need to copy Access DB file (*.mdb) in ready folder of Inbound Port on scheduled basis?

[Former Member]

Hello Shailesh

If you want automate your process

Make some script for :

- Open your Access DB file

- Save Access DB file in XML format

- Copy created XML file to your inbound directory.

Run that script by schedule

Sure, before that you should create map for import XML

Regards

Kanstantsin Chernichenka

Edited by: Kanstantsin Chernichenka on Apr 18, 2011 11:37 AM