Query Access data using Excel via ODBC

[sdannerr]

I am having difficulties with querying data from Access 2000 using Microsoft Excel. We use security on all of our databases which I think might be part of the problem. When I go to create a query in Excel I am told that it cannot connect to the table. A few tables work fine but not all which is also strange. If I try to access the database from Crystal Reports I am told that each table has no fields which of course is not the case.

To me this sounds like an ODBC problem. Does anyone have any insight into what might be going on?

 

[winstonep]

Have you setup the ODBC DSN with an username and password?

I haven't got any files to try this with. Its the only thing I can think of.

Paul Winstone

http://www.logicacmg.com

paul.winstone@logicacmg.com

 

[sdannerr]

I do not have a username and password filled in for my ODBC connection. Does it use a default user then?

 

[catbebe]

Hi,

I do faced this problem before. What I do is log-off current user and signed in as power user (or the Highest authority user) for the ODBC connectivity.

 

[nohri]

When you are in Crystal reports, on left click "create new connection" and then click Access/excel. The CONNECTION box will appear. Check the "secure logon" after completing database type and then enter the information (password, path etc.). This would allow you to link tables from databases protected via with system.mda files.