"Tom Ogilvy" wrote ...

The dictionary object looks to be useful.
Is there a similar object or method for using multiple keys to add or
retrieve an item. Something like
Object.Add key1,key2,key3, item

Multiple keys - not that I am aware of.

Latest instalment in my continuing series, 'You Can Use ADO For This':

Sub test()

' Fabricate a disconnected ADO recordset
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")

With rs

.CursorLocation = 3 ' 3=adUseClient

' Item field to hold varient values/objects:
' 12=adVariant
.Fields.Append "Item", 12

' Key fields:
' 129=adChar, 35=MaxCharWidth
.Fields.Append "Key1", 129, 35
.Fields.Append "Key2", 129, 35
.Fields.Append "Key3", 129, 35

.Open

' Create sample data
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Tinatotac", "Name1", "Name2", "Name3")
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Livehulas", "Name2", "Name3", "Name4")
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Regisaver", "Name3", "Name4", "Name5")
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Hevitoxic", "Name4", "Name5", "Name6")
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Luxasonic", "Name5", "Name6", "Name7")
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Katewudes", "Name6", "Name7", "Name8")

' Locate items where Key is 'Name3'
.Filter = "Key1='Name3' OR Key2='Name3' OR Key3='Name3'"
MsgBox "Matching items:" & vbCrLf & .GetString

End With

End Sub

Where may find a list of objects that might be useful for creating a
relational database in Excel.

Excel interfaces with Access and other databases - so it might be best to
use a relational database to do relational database work.

And, if the OP means create a relational database from Excel ... yep, ADO again:

Sub Test2()

Dim Cat As Object
Dim rs As Object
Dim strConJet As String
Dim strSql1 As String
Dim strSql2 As String

' Amend the following constants to suit
Const PATH As String = "" & _
"C:\"
Const FILENAME_JET As String = "" & _
"New_Jet_DB.mdb"

' Do not amend following constants
Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH<FILENAME"

' Build connection string
strConJet = CONN_STRING_JET
strConJet = Replace(strConJet, "<PATH", PATH)
strConJet = Replace(strConJet, "<FILENAME", FILENAME_JET)

' Build sql statements
strSql1 = ""
strSql1 = strSql1 & "CREATE TABLE Pilots ("
strSql1 = strSql1 & " ID INTEGER NOT NULL PRIMARY KEY,"
strSql1 = strSql1 & " lname VARCHAR(35) NOT NULL,"
strSql1 = strSql1 & " fname VARCHAR(35) NOT NULL,"
strSql1 = strSql1 & " mname VARCHAR(35) NOT NULL"
strSql1 = strSql1 & " DEFAULT '{{NA}}'"
strSql1 = strSql1 & ");"

strSql2 = ""
strSql2 = strSql2 & "SELECT ID, lname, fname"
strSql2 = strSql2 & " FROM Pilots;"

' Create new Jet database
Set Cat = CreateObject("ADOX.Catalog")
Cat.CREATE strConJet

With Cat.ActiveConnection

' Create tables
.Execute strSql1

' Create some sample data
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (1, 'Livehulas', 'A')"
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (2, 'Katewudes', 'B')"
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (3, 'Hevitoxic', 'C')"
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (4, 'Norarules', 'D')"

' Query data
Set rs = .Execute(strSql2)
MsgBox rs.GetString

End With

End Sub


Jamie.

--