Access to Excel using SQL.REQUEST

[Duncan Davies]

For some time I have been trying to "lookup" values in a secured Access
database from Excel using the SQL.REQUEST function. This function works fine
when the database is not secured. Any help greatly appreciated.

Duncan Davies

 

[Douglas J. Steele]

What are you using as the connection string (the first property)?

 

[Duncan Davies]

Using the Northwind database as an example, the syntax I am using is as
follows:

='C:\Program Files\Microsoft
Office\OFFICE11\LIBRARY\MSQUERY\XLODBC.XLA'!SQL.REQUEST("DSN=MS Access
Database;DBQ=C:\Northwind.mdb",,,"SELECT ProductName FROM Products WHERE
(ProductID=5)")

 

[Douglas J. Steele]

When you say "secured", do you mean a database password, or proper
user-level security (which involves providing a user name and password)?

Have you provided the necessary security information in the DSN?

 

[Duncan Davies]

Douglas,

The database has proper user-level security (about 20 users).
When you say DSN, I do you mean the DSN statement in the formula?
If so, then, no I haven't done that (and don't know how to).

 

[Douglas J Steele]

From what I can see of how to use the SQLRequest function, you need to
define a DSN. You can do this through the Data Sources applet in the Control
Panel.

Since this is really an Excel question, not an Access one, you might also
check in one of the Excel newsgroups, such as
microsoft.public.excel.programming.