Jamie
Ah, the joys of the being THE network admin (I never complain to
myself.).<g
Would you mind sketching out how you would set that up? I'd be interested
in learning a better way. Thanks.
--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.
"jamieuk" wrote in message
om...
Using Dick's function as a UDF in a formula in R1 and copying down to
R65536 would result in 65536 queries, 65536 trips across the network,
etc (and the connection is never closed).
The solution obviously works for you but if you get performace issues
(or complaints from your network admin or DBA!) bear in mind it is
possible to do this in one hit: *one* query to create a recorset for
all rows, then use update the spreadsheet using CopyFromRecordset or
some other method.
--
"Dick Kusleika" wrote in message
...
Stephen
Here's how I'd do it: First create a module level variable for an ADODB
connection object. Set a reference to Microsoft ActiveX Data Object x.x
under Tools - References. Next, create a sub that opens the ADODB
connection. Create a sub that closes the ADODB connection which you
should
call from your Workbook_BeforeClose event. Finally, create function
that
creates a recordset and pull the data out of there. Here's an example.
Dim Conn As ADODB.Connection
Sub EstablishConnection()
Dim MyConn As String
Set Conn = New ADODB.Connection
MyConn = "DSN=MS Access 97 Database;DBQ=C:\Dick\db1.mdb;"
MyConn = MyConn & "DefaultDir=C:\Dick;DriverId=281;"
MyConn = MyConn & "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
Conn.Open MyConn
End Sub
Function GetValue(Rng1 As Range, Rng2 As Range) As Variant
Dim MySql As String
Dim rs As ADODB.Recordset
If Conn Is Nothing Then
EstablishConnection
End If
MySql = "SELECT `Table 1`.number, `Table 1`.name, `Table 1`.TheDate "
MySql = MySql & "FROM `C:\Dick\db1`.`Table 1` `Table 1` "
MySql = MySql & "WHERE (`Table 1`.number='" & Rng1.Value & "') AND "
MySql = MySql & "(`Table 1`.name='" & Rng2.Value & "')"
Set rs = Conn.Execute(MySql)
rs.MoveFirst
GetValue = rs.Fields(2).Value
End Function
Sub TermConnection()
Conn.Close
Set Conn = Nothing
End Sub
You could create an destroy the ADODB connection inside of the function,
but
that will increase your processing time and may slow you down. I think
it's
better to open the connection once and close it once and leave it open
while
the workbook is open.
You call the function like
=GetValue(A1,B1)
and the recordset returned inside the function should contain the proper
value, because as you say, there is only one combination of the first
two
fields in the database.
Give it a try and let me know if you need any clarification.
--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.
"Stephen Goldfinger" wrote in message
...
Thanks for taking the time to reply. Your assessment is right on the
mark. Here is an example (I hope they line up well.)
Lookup Lookup
Column 1 Column 2 Result
-------- -------- ------
A1 A2 R1
B1 B2 R2
C3 C3 R3
Explanation: Given the combination of values (A1 and A2), I can
determine the SINGLE result value, R1. Each combination of Column 1
and
Column 2 will return a singleton value.
Given what I know of Excel, I can write a query that takes the values
of
the first row (i.e. A1 & A2) to then go get the resulting value R1. I
can then write another query for (B1 & B2) to get me R2. I don't want
to have to write multiple queries. I want one query (or a function)
that can take the Column 1 and Column 2 values for each row and get me
the Result value for that particular row.
I hope this clarifies the question.
Thanks.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!