Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Looking Up" Access from Excel using SQL.REQUEST
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.RE QUEST("DSN=MS Access Database;DBQ=C:\Northwind.mdb",,,"SELECT ProductName FROM Products WHERE (ProductID=5)") Regards, D.Davies |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Looking Up" Access from Excel using SQL.REQUEST
Have you tried putting in a user id and password
'C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\MSQUERY\XLODBC.XLA'!SQL.RE QUEST("DSN=MS Access Database;DBQ=C:\Northwind.mdb;UID=Duncan;PWD=abcd" ,,,"SELECT ProductName FROM Products WHERE (ProductID=5)") -- Regards, Tom Ogilvy "Duncan Davies" wrote: 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.RE QUEST("DSN=MS Access Database;DBQ=C:\Northwind.mdb",,,"SELECT ProductName FROM Products WHERE (ProductID=5)") Regards, D.Davies |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Looking Up" Access from Excel using SQL.REQUEST
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 -- - K Dales "Duncan Davies" wrote: 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.RE QUEST("DSN=MS Access Database;DBQ=C:\Northwind.mdb",,,"SELECT ProductName FROM Products WHERE (ProductID=5)") Regards, D.Davies |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Looking Up" Access from Excel using SQL.REQUEST
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 "K Dales" wrote: 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 -- - K Dales "Duncan Davies" wrote: 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.RE QUEST("DSN=MS Access Database;DBQ=C:\Northwind.mdb",,,"SELECT ProductName FROM Products WHERE (ProductID=5)") Regards, D.Davies |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Looking Up" Access from Excel using SQL.REQUEST
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. |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Looking Up" Access from Excel using SQL.REQUEST
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 -- - K Dales "Duncan Davies" wrote: 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 "K Dales" wrote: 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 -- - K Dales "Duncan Davies" wrote: 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.RE QUEST("DSN=MS Access Database;DBQ=C:\Northwind.mdb",,,"SELECT ProductName FROM Products WHERE (ProductID=5)") Regards, D.Davies |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access to Excel using SQL.REQUEST | Excel Discussion (Misc queries) | |||
Excel Access and Oracle | Excel Discussion (Misc queries) | |||
Excel Spreadsheet from Access. List of names changes as names are | Excel Discussion (Misc queries) | |||
MS Access in Excel | Excel Worksheet Functions | |||
Excel user desires to learn ABC of Access | Excel Discussion (Misc queries) |