"Looking Up" Access from Excel using SQL.REQUEST

[Duncan Davies]

Hi,

I am trying to "lookup" values in a secured Access database from Excel using
the SQL.REQUEST function. When the database is not secured the function works
perfectly, but as soon as I apply User-Level Security it doesn't work.

Using the sample database "Northwind" as an example, the format of my
function 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)")

Regards,
D.Davies

 

[Tom Ogilvy]

Have you tried putting in a user id and password

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

 

[K Dales]

Securing the database also secures it against any ODBC access (as it should -
otherwise anyone with ODBC could read and or modify any of your data). I
presume you created a new workgroup for Access - if so, how are you joining
the workgroup? Do you log into the workgroup file using the Workgroup
Administrator, or do you specify it in the command line of your shortcut? If
you are not logged into the workgroup when ODBC tries to connect to Access
(using SQL.REQUEST) then ODBC logs you into Access as a member of the "Users"
group. You will only have the access levels granted to that group. You can
use the ODBC Administrator to modify the setting for MS Access databases to
specify a workgroup file (but this setting would affect all ODBC calls to
that DSN).

Although it addresses a different problem, there is useful info in this KB
article:
http://support.microsoft.com/kb/209120/en-us

 

[Duncan Davies]

This is now all academic - our company has recently upgraded to Excel 2003,
which apparently does not support XLODBC.XLA, the add-in containing
SQL.REQUEST.

My current workaround is to export the database table to an excel file (this
has to be done frequently as the database is updated daily). I then get the
info from the exported file using VLOOKUP. This works okay - it's just a pain
in the backside having to export the data all the time - every day usually.

Is there another solution to my problem, without having to become a
programming expert?

Duncan Davies

 

[DM Unseen]

Maybe get the data into a worksheet with Data->Import External
Data->New Database Query

Dm Unseen

PS. try to use excel's ODBC parameters for filtering.

 

[K Dales]

I wrote the following for my own purposes; it acts somewhat like SQL.REQUEST
by passing a SQL query through ODBC:

Public Function ODBCSQL(ConnectStr As String, _
SELECTStr As String, _
FROMStr As String, _
WHEREStr As String) _
As Variant

'
' Function to execute a SQL query in ODBC
' (NOTE: Only single value can be returned!)
'
' Parameters:
' ConnectStr is the ODBC Connection String
' SELECTStr, FROMStr, and WHEREStr are the corresponding sections
' of the SQL query (minus the words SELECT, FROM, and WHERE)
' The function pieces together the SQL Query as follows:
'
' "SELECT " & SELECTStr & " FROM " & FROMStr & " WHERE " & WHEREStr
'
' Possible error conditions:
' 1) Invalid SQL string - return an error code
' 2) No matching records - return the string "NOT FOUND"
' 3) Multiple fields - return the string "MULTIPLE FIELDS"
' 4) Multiple records - return the first record
' 5) Other VBA errors: The error number
'

' On Error GoTo ERR

' Set up object variables for ODBC connection and records
Dim ODBCcn As ADODB.Connection
Dim ODBCrs As ADODB.Recordset

' Variables for the SQL string and the returned value
Dim SQL As String
Dim ODBCData As Variant

' Variable to assign to ODBC fields
Dim ODBC1 As Variant

' Variable to count the number of records returned
Dim MRecords As Integer

ODBCData = ""
SQL = ""

'
' Set up the object variables for the ODBC connection to the ODBC
database
'

Set ODBCcn = New ADODB.Connection
Set ODBCrn = New ADODB.Recordset

'
' Open the ODBC connection.
'

ODBCcn.Open ConnectStr


'
' Send SQL query to return the recordset matching the specified
parameters
'

SQL = "SELECT " & SELECTStr & " FROM " & FROMStr & " WHERE " &
WHEREStr

Set ODBCrs = ODBCcn.Execute(SQL)

'
' Check immdiately for and end of file (no records found):
'

If ODBCrs.EOF Then

ODBC1 = "NOT FOUND"

'
' Otherwise get the first corresponding result value
'

Else

If ODBCrs.Fields.Count > 1 Then

ODBC1 = "MULTIPLE FIELDS"
Else

ODBC1 = ODBCrs.Fields(0).Value

End If

End If

'
' Set the function return value:
'

ODBCSQL = ODBC1

'
' Remember to close the connection and clear the object variables
'

ODBCcn.Close
Set ODBCrs = Nothing
Set ODBCcn = Nothing

Exit Function

'
' Error handler:
'

ERR:
ODBCSQL = ERR.Number

On Error Resume Next
If ODBCrs.State = adStateOpen Then ODBCrs.Close
If ODBCcn.State = adStateOpen Then ODBCcn.Close
Set ODBCrs = Nothing
Set ODBCcn = Nothing

End Function