Using COMSDK= based technology to give Office applications access to your legacy data.

&n= bsp;

The lat= est version of the COMSDK comes with a sample that allows you to access you RDD-based data from any COM-client. Examples of COM-clients are VBA (so the Office apps like MS-Word and MS-Excel) but also VB-script.

After installation of the COMSDK (downloadable from www.comsdk.com) you will find a subfolder in your COMSDK install folder called dbReader. This application is an example of a COM-ser= ver application written using the COMSDK functions and classes that provides ac= cess to your dbf’s using = VO’s VORDD functions. The object model is different from that of dbServer and somewhat optimized for doing queries resulting in = recordsets.

Installation=

You nee= d to import TS_DBREADER.AEF into your COMSDK project and compile and create an .= Exe. If you don’t have a COMSDK project yet, just double click on the .voprj file in the Project folder in the COMSDK instal= l.

&n= bsp;

Dependi= ng on how you want to use this component choose either of two scenario’s;

&n= bsp;

1. Inst= all in a separate folder and make sure all the required runtime files are there or

2. Crea= te in \Cavo27\bin and copy ts_dbreader.tlb there.

&n= bsp;

Obvious= ly in an application deployment scenario, you would install TS_dbReader.Exe and .Tlb in the folder where you also have the VO runtime = files and your application .exe and dll’s.=

&n= bsp;

After y= ou’ve created the TS_dbReader.exe you need to register this = comserver. Open a command prompt, CD to the folder you’ve created the .Exe in and type TS_dbReader /REGISTER,= this will register the component and the Typelibrary which is provided in a separate file (TS_DBREADER.TLB). You should get two = messageboxes confirming registration of the component= and the typelibrary.

Using

You are= now ready to start testing the dbReader, let’= s try it out by writing a little script in vb. Start Notepad and paste the follow= ing code:

&n= bsp;

Set IXPServer= =3DCreateObject("TS_DBReader.Server")

MsgBox IXPServer,,"IXPServer<= /span>(TS_DBReader.Server)"

&n= bsp;

Now Fil= e, Save As TestdbReader.VBS and ex= ecute this script by double clicking on this file from Windows Explorer.

You sho= uld see a messagebox, basically showing the ‘AsString’ of the dbreader<= /span> component, giving you some details about the comserver= .

&n= bsp;

You now= know, you’ve got a working comserver. Let’= ;s expand this script a little to open a dbf and skip through the records.

Add the= following code to your script (you might have to change the path to reflect your installation):

&n= bsp;

Set IXPServer= =3DCreateObject("TS_DBReader.Server")

MsgBox IXPServer,,"IXPServer<= /span>(TS_DBReader.Server)"

Set IXPRecord= setFactory =3D IXPServer.IRecordsetFactory(-1,"DBFCDX","C:\Cavo27\comsdk\DBReader= \TS_DBReaderTestFileCDX.DBF")

MsgBox IXPRecordsetFactory,,"= IXPRecordsetFactory"

&n= bsp; lShared=3DTrue

&n= bsp; lReadOnly=3DFalse

&n= bsp; Set IXPRecordset =3D IXPRecordsetFactory.IRecordsetEx(lShared,lReadOnly)

&n= bsp; MsgBox IXPRecordset,,"IXPRecordset"

&n= bsp;

&n= bsp; Set IXPCursor =3D IXPRecordset= .ICursor

&n= bsp; MsgBox IXPCursor,,"IXPCursor"

&n= bsp; Tmp =3D IXPC= ursor.Skip(+3)

&n= bsp; While Not IXPCursor.IsBOF

&n= bsp; = ; Set IXPRecord =3D IXPCursor.IR= ecord

&n= bsp; = ; MsgBox IXPRecord,,IXPRecord

&n= bsp; = ; Tmp =3D IXPC= ursor.Skip(-1)

&n= bsp; Wend=

Save an= d run, and as you can see all of the objects returned actually reveal most of their properties when you display them using the VB-MsgBox function.

Using t= he Cursor-object we can also retrieve Fieldvalues,= much like VO’s Fieldget():

MsgBox IXPCursor.FieldValue= (1),,"IXPCursor.FieldValue(1)"

&n= bsp;

Thera are lot’= ;s of other method’s and properties in the dbReader= object-model. You can have a good look at the TS_DBRea= der.VBS in the dbReader folder to get a feel for what&#= 8217;s there and how it can be used. You can set scopes, set orders do seeks etc. And, do= n’t forget! all of the source code is right there in TS_DB= Reader.AEF in case you need to change anything, or want to add stuff.

&n= bsp;

Ok, now= that we know how to use the component, let’s look at calling it from MS-Word = for example, and taking it just a bit further let’s make use of XML-technology in Office 2003 to implement what you might call the base ver= sion of a smart-document.

Office = 2003 allows us to attach XML-schema’s or XSD’s<= /span> to our documents and place labels in the document that ‘tag’ an area. Much like you would previously have done using fields or bookmarks. T= hese tag’s can be found using script and used as XML-elements off course. =

Here= 217;s a little snippet of a part of a word-document where these tags have been appl= ied, in this case the macro has already run and the data is therefore present in= side the tags.

&n= bsp;

The schema for the above can be something like this:

<?xml version=3D"1.0" encoding=3D"utf-8" ?>

<xs:schema= id=3D"XMLSchema1" targetNamespace=3D"http://tempuri.org/XMLSchema1.xsd" elementFormDefault=3D"qualified"

&nb= sp; xmlns=3D"http://tempuri.org/XMLSchema1.xsd" xmlns:mstns=3D"htt= p://tempuri.org/XMLSchema1.xsd"

&nb= sp; xmlns<= span lang=3DNL style=3D'font-size:10.0pt;font-family:"Courier New";color:fuchsia; mso-ansi-language:NL'>:xs=3D"htt= p://www.w3.org/2001/XMLSchema">

<= /span><= xs:element = name<= span style=3D'font-size:10.0pt;font-family:"Courier New";color:blue'>=3D"Ad= dress">

&n= bsp; <xs:comple= xType>

&n= bsp; <xs:sequen= ce>

&n= bsp; <xs:element <= /span>name=3D"GIVEN&qu= ot; type=3D"xs:string" />=

&n= bsp; <xs:element <= /span>name=3D"SURNAME&= quot; type=3D"xs:string" />=

&n= bsp; <xs:element <= /span>name=3D"GAB"= ; type=3D"xs:integer"<= /span> />=

&n= bsp; <xs:element <= /span>name=3D"TITLE&qu= ot; type=3D"xs:string" />=

&n= bsp; </xs:sequence>=

&n= bsp; </xs:complexType&= gt;

</xs:element<= /span>>

</xs:schema>

I won&#= 8217;t go into how to create schema’s and how to = attach them to word documents, there’s plenty of info on how to do that on <= span class=3DSpellE>msdn. I suggest starting here: http://ms= dn.microsoft.com/office/understanding/xmloffice, and here: http://msdn.microsoft.com/office/understanding/xmloffice/articles/defau= lt.aspx?pull=3D/library/en-us/dno2k3ta/html/odc_ofeditingxmlwordexcel.asp

&n= bsp;

Assumin= g we’ve got a document with a schema and tags in it, we can now write a macro that loops through the tags and fill’s them with data provided by dbReader.

&n= bsp;

Sub Test()

Dim IXPServer=

‘ Create object

Set IXPServer= =3D CreateObject("TS_DBReader.Server&= quot;)

‘ Get factory specifying file

Set IXPRecord= setFactory =3D IXPServer.IRecordsetFactory(-1, "DBFCDX", "H:\Cavo27a\comsdk\DBReader\TS_DBReaderTestFileCDX.DBF")

lShared =3D True

lReadOnly= =3D False

&n= bsp; ‘ Open recordset and= Cursor

Set IXPRecordset =3D IXPRecordsetFactory.IRecordset= Ex(lShared, lReadOnly)

Set IXPCursor =3D IXPRecordset.ICursor<= /p>

‘ Loop through all XMLNodes filling them

For i =3D 1 To ActiveDocument.XML= Nodes.Count

ufield =3D ActiveDocument.XMLNodes.Item(i).BaseName

uvalue =3D IXPCursor.FieldValue(i)

ActiveDocument.XMLNodes.Item(i).Text =3D uvalue

Next i<= /span>

End Sub

In a real scenario you would probably= use the Node-name to identify the field to get the value for (using uField as a parameter for the call to FieldValue), but other than that, that’s about all there is to it. I did not do th= is here because I did not have the XSD for TS_DBReader= TestFileCDX.DBF available.= While on that topic, did you know that in VO2.7 you can create XML and XSD files = for dbf’s using the dbserver editor?

Conclusion

This is= just a way to start using some of the newer technologies that became available in products like Office 2003 without having to do major re-writes. This allows= you to quickly satisfy some need your customers might have now and to gain some experience on XML, Office 2003 and schema-based development. The latter we = will see more and more in the next year or so. This will ensure you don’t = get stuck in the 32-bit environment and will be able to adept to the next generation of software development which look to= be highly XML-based.

&n= bsp;

List of= files required for TS_dbReader.Exe

&n= bsp;

TS_dbReader.TLB

VO27SYS= .DLL &= nbsp; (comes with VO 2.7b)

VO27RUN= .DLL &= nbsp; ( ,, )

*.RDD b= ased on what RDD’s you need to use

&n= bsp;

CPU27.D= LL &= nbsp; &nbs= p; (installed by COMSDK in \Cavo27\bin)

&n= bsp;