Changing from DAO to ADO

[Roderick]

I wish people would make up their minds!! (Sorry, my rant!)

Let me explain:

I've created a multi-lingual template (English, French, Spanish) for the user to choose their selected language. When doing so, the template queries an Access database and populates all the Ribbon Bar labels, tips and supertips through the XML.

This is using DAO - and it works like a dream. Below is the existing code for just the Ribbon labels. The other two follow the same pattern.

Sub rxshared_getLabel(control As IRibbonControl, ByRef returnedVal)
Dim dbRibbonData As DAO.Database
Dim rdShippers As Recordset
Dim intRecords As Integer
Dim myString As String
Dim strSQL As String
Dim myLanguage As String
Dim myFieldCode As String
Dim rxMyLabel As String
Dim inifileloc2 As String

'control.id comes from my Ribbon XML shared callback
rxMyLabel = control.id

inifileloc2 = “FullPath” + “\” + “RibbonData.accdb”

Set dbRibbonData = OpenDatabase(Name:=inifileloc2)

'searches the Registry for the set language
myLanguage = GetSetting("GA", "Template Language", "Language")
‘For this example myLanguage is set at “English”
'search the database in the 'Template_Labels' table in the 'language' field for the 'control.id' record
strSQL = "Select " & myLanguage & " FROM [Template_Labels] where [Field_Code] = '" & rxMyLabel & "'"

Set rdShippers = dbRibbonData.OpenRecordset(strSQL, dbOpenDynaset)

'return the value back to the Ribbon XML
On Error Resume Next
returnedVal = rdShippers.Fields(0)

rdShippers.Close
dbRibbonData.Close

End Sub

Now comes the crunch: the client has - to cut a long story short - said "We want you to use an .xlsx file and access it through ADO! Why, when it's working so well? But that's another thing...

I've now got to convert the above code to use the client's requirements.

Thanks to Greg Maxey, who had previously made a post dealing with (sort of) just this subject, I have been trying to make the changes but have come to a grinding halt as I've run out of ideas on what to do next.

So far I've got this far:

Sub rxshared_getLabel(control As IRibbonControl, ByRef returnedVal)
'Requires reference to the "Microsoft ActiveX Data Object 2.8 Library."
Dim oConn As New ADODB.Connection
Dim oRecSet As New ADODB.Recordset
Dim strConnection As String
Dim strRange As String
Dim lngCount As Long

'control.id comes from my Ribbon XML shared callback
rxMyLabel = control.id

p_strPath = “FullPath”
strRange = "Template_Labels & “$]"

Set oConn = CreateObject("ADODB.Connection")
'Suppress first row.
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & p_strPath & "RibbonData.xlsx;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

oConn.Open ConnectionString:=strConnection

Set oRecSet = CreateObject("ADODB.Recordset")
'Read the data from the worksheet.
oRecSet.Open "SELECT * FROM [" & strRange, oConn, 2, 1
With oRecSet
.MoveLast
'Get count.
lngCount = .RecordCount
.MoveFirst
End With

.....

Where I'm struggling is in finishing it off: How can I read the label for the Ribbon (column 0) and then read the correct label text from column 2 (English) and pass this back to rxMyLabel at the top pf the procedure?

Could someone point me in the right direction, please?

Thanks.

Roderick

[Bob Phillips]

Why don't you just store the ribbondata in the same workbook, simple lookups then, no need for ADO. That is what I do with mine.

[Bob Phillips]

If you do intend to go with this separate workbook approach, I would suggest you read the whole thing into an array at ribbon onload, and then get the values from the array when wanted.

If you need some help, sample workbooks would help, with instructions as to the variables, such as how the language is determined.

[gmaxey]

Roderick,

I've attached a demo Word document and Excel file that mayhelp illustrate (two methods) of getting your ribbon attributes (e.g., label,tip and supertip) from Excel.

The first uses ADODB to retrieve each attribute individuallyusing SQL Select "From Somewhere" From "From Somewhere"Where "Something" = "Something"

For example we would select from the “Label” column From the“Sheet” Where the “ControlID” column value = “custTab1”


The second just gets all the data in one instance into anarray and then queries the array to provide the attributes.

Hope it helps.