topic MS Access via linked server in Application Development and Automation Discussions

MS Access via linked server

Former Member — Fri, 05 Aug 2005 11:18:05 GMT

Hi Everybody,

I have a requirement to do transactional reads on a MS Access database. I could not find any standard way to link SAP to Access (please let me know if someone else has done this)

As a workaround we created a linked server (Access) to the MS SQL Server on which SAP runs here. The idea was to then use native SQL to query this linked database (since SQL Server links with Access with no problem) to get the data from Access.

So far so good, but when running the query with OPENQUERY I get a short dump with the message 'Remote access not allowed for Windows NT user activated by SETUSER'. Bugger!

Any of you brainy guys out there have any ideas?

Thanks in advance,

Gerald

Re: MS Access via linked server

Former Member — Fri, 05 Aug 2005 12:03:44 GMT

Hello gerald,

I have already done something like that but with a SQL server DB.

You must insert in the table DBCON the inforamtion about your SQL server.

See oss notes 178949 for further information.

Re: MS Access via linked server

Former Member — Fri, 05 Aug 2005 12:26:48 GMT

Hi,

Chek this link:

You can use ADO COM Objects. Please refer in the ABAP Documentation about using of COM Objects.

Also, u can export the data from MS Access to a text file and then uploading it from the GUI frontend, (if nothing works)

Best Regards,

Anjali

Re: MS Access via linked server

Former Member — Fri, 05 Aug 2005 13:20:22 GMT

Hi barca, Anjali

The problem with using a multiconnect as barca suggests is that it seems to be restricted to certain databases only (Oracle,Informix,Microsoft SQL Server,DB2/RS6000 etc.) but not a Access database.

Anjali, your suggestion might be viable, but on first inspection it looks like all the function modules in FuGr: MSDB is for exporting data to a access database and not reading from it. I will investigate this further, but not sure if I'll get it to read.

The read needs to happen very regularly, so manually downloading the data won't work in this case. Thanks anyway you two

Gerald