How to use Names in VBA
"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.
--
|