ADODB.Recoredset not defined

[Riley]

Hi all, hope I can get some guidance here. I'm not an experienced
programmer so please bear with me.

I've been able to get the block of code below to work as an Add-in in
excel but it turned out to be quite slow, so when I came across Excel-
DNA I figured I'd give it a go to see if I could improve on it. The
code is meant to work like a VLookUp in excel but instead of
referencing an excel spreadsheet it will reference an access table on
a shared network drive.

-------------------------------------------------------------------------------------------------------
Option Explicit

Dim adoCN As ADODB.Connection
Dim strSQL As String

Const DatabasePath As String = "C:Bids&ContractsDB.mdb"

'Function argument descriptions
'LookupFieldName - the field you wish to search =
'LookupValue - the value in LookupFieldName you're searching for =
'ReturnField - the matching field containing the value you wish to
return =

Public Function DBVLookUp(TableName As String, _
LookUpFieldName As String, _
LookupValue As String, _
ReturnField As String) As Variant

Dim adoRS As ADODB.Recordset

If adoCN Is Nothing Then SetUpConnection

Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & _
" FROM " & TableName & _
" WHERE " & LookUpFieldName & "='" & LookupValue & "';"
'If lookup value is a number then remove the two '

adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
If adoRS.BOF And adoRS.EOF Then
DBVLookUp = "Product not Found"
Else
DBVLookUp = adoRS.Fields(ReturnField).Value
End If
adoRS.Close
End Function

Sub SetUpConnection()
On Error GoTo ErrHandler
Set adoCN = New Connection
adoCN.Provider = "Microsoft.Jet.OLEDB.4.0" 'Change to 3.51 for Access
97
adoCN.ConnectionString = DatabasePath
adoCN.Open
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub
-----------------------------------------------------------------------------------------------------------------

I've tried to convert the above VBA code in Visual Studio 10 Express
and now have the following code.

-----------------------------------------------------------------------------------------------------------------
Option Explicit On
Imports ExcelDna.Integration

Public Class DBVLookUpAddin

Dim adoCN As Odbc.OdbcConnection
Dim strSQL As String

Const DatabasePath As String = "C:Bids&ContractsDB.mdb"
'Function argument descriptions
'LookupFieldName - the field you wish to search =
'LookupValue - the value in LookupFieldName you're searching for =
'ReturnField - the matching field containing the value you wish to
return =

Public Function DBVLookUp(TableName As String, _
LookUpFieldName As String, _
LookupValue As String, _
ReturnField As String) As Object

Dim adoRS As ADODB.Recordset

If adoCN Is Nothing Then SetUpConnection()

adoRS = New ADODB.Recordset
strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & _
" FROM " & TableName & _
" WHERE " & LookUpFieldName & "='" & LookupValue
& "';"
'If lookup value is a number then remove the two '

adoRS.Open(strSQL, adoCN, adOpenForwardOnly, adLockReadOnly)
If adoRS.BOF And adoRS.EOF Then
DBVLookUp = "Product not Found"
Else
DBVLookUp = adoRS.Fields(ReturnField).Value
End If
adoRS.Close()
End Function

Sub SetUpConnection()
On Error GoTo ErrHandler
adoCN = New Odbc.OdbcConnection
adoCN.ConnectionString = "Microsoft.Jet.OLEDB.4.0" 'Change to
3.51 for Access 97
adoCN.ConnectionString = DatabasePath
adoCN.Open()
Exit Sub
ErrHandler:
MsgBox(Err.Description, vbExclamation, "An error occurred")
End Sub

Private Function adOpenForwardOnly() As Object
Throw New NotImplementedException
End Function

Private Function adLockReadOnly() As Object
Throw New NotImplementedException
End Function

End Class
------------------------------------------------------------------------------------------------------------

This code does not seem to kick out any errors in VS10E but when I put
this in the .dll file I get the following errors.

-------------------------------------------------------------------------------------------------------------
There were errors when compiling project:
C:\Documents and Settings\HP_Administrator\Local Settings\Temp
\obufpnz2.0.vb(20,0) : error BC30002: Type 'ADODB.Recordset' is not
defined.
C:\Documents and Settings\HP_Administrator\Local Settings\Temp
\obufpnz2.0.vb(24,0) : error BC30002: Type 'ADODB.Recordset' is not
defined.
-------------------------------------------------------------------------------------------------------------

Hopefully that makes sense.

Any suggestions? Again I'm not a real programmer, I just pretend to
be one, so I may be completely off base here.

Thanks

[Govert van Drimmelen]

Hi Riley,

It looks like you are putting the code into a .dna file, which Excel-
DNA then compiles on-the-fly, giving you those errors.
(The conversion to VB.NET will probably be easier if you use an IDE
like Visual Studio or SharpDevelop.)

To tell the compiler which additional references you need in the .dna
file, you add some <Reference> tags.
I'm not sure what references are required for the ADO libraries you
want to use.

I'd suggest this:
- Get the code working as a VB.NET console application (totally
independent of Excel) by starting a new Console project in either
Visual Studio (the VB.NET Express edition is free) or SharpDevelop,
which is also free.
- Once that part works, we can consider how to implement it in an
Excel add-in.

You're welcome to post here for help getting started with the plain
VB.NET project too, I just want to clearly separate the issues.

----

For easy, high-performance access to data in an MDB, I recommend you
rather use DAO than ADO (for 64-bit support you need the new ACEDAO
libraries).

The pattern for this kind of lookup functino would then be to do a one-
time setup of a Recordset pointing to a table (so use just the table
name instead of a Select query) then set the Recordset.Index to your
lookup index.
Every lookup action then does a Recordset.Seek to position to the
right record from where you return the data.

Following this scheme, simple lookups from a local database should
return at a rate of 20000 - 50000 per second. Over the network it
would be a bit worse, depending on your .mdb size.

But I'd suggest we figure all of this out before integrating with
Excel.

Regards,
Govert

[Riley]

Thanks for the reply Govert. I'll try to follow your suggestions,
although it may be beyond my limited programming abilities.

Expect to see further posts from me.

Cheers!
Riley