I made an interesting script here. It uses ADODB to access a Table or Query in
a specified MSAccess Data Base. The structure of the data is replicated in a
Powerpro structure (defined with the class plugin). The structured records are
added to a 1-dimensional vector.
The class plugin is unloaded at the end because otherwise Powerpro crashed when
the script was rerun. Possibly storing structured data in a local vector was
not expected. But this beats a 2d vector for easily referencing values.
Yahoo may have wrapped some lines.
Regards,
Sheri
;2010-03-09 by Sheri Pierce
;interfaces an MSAccess table or query to Powepro
;records are stored in a single-dimension vector with 1 row per entry
;vector entries are in the format of a Powerpro structure (from class plugin).
;uses com .72, class,vector and regex plugins
;tested with Powerpro 4.917
;uses ampersand as the expression follows character
;MSAccess app need not be open
;===================
local strdb=env("userprofile")++?"\My Documents\MSAccessData\SheriTest.mdb"
;this script expects a predefined query or table from the data base
;sql could be used, see e.g. sqlstmt in OpenAccessView function
;brackets req if table or query name has embedded spaces
local qname=?"[Addresses]"
static dbvars
local vdata=MakeVector(strdb, qname)
win.debug("variable_names:", dbvars)
local firstvar=dbvars.word(1,"\x20")
local testvar="elem."++firstvar
win.debug("Sample values in powerpro vector")
for each elem index i in vdata
win.debugshow("example: elem."++firstvar++" from record #"++i, &(testvar))
;following works with my Addresses table
;win.debug(elem.lastname++","++elem.firstname++" from "++elem.city++","++ ;;+
;elem.stateorprovince++" "++elem.postalcode++" //"++elem.emailaddress)
;above works for my Addresses table
endfor
class.unload ;;Powerpro Crashes on 2nd usage without this
dbvars=""
quit
;=====================
Function MakeVector(strdb, qname)
local com_type, com_status
local mydb=OpenAccessData(strdb)
local myview=OpenAccessView(qname, mydb)
local myviewstruct=MakeStructure(myview)
local vdata=RecordsToVector(mydb, myview, myviewstruct)
Myview.Close
MyDb.Close
MyView.Release
MyDb.Release
com.unload
quit(vdata)
;=====================
Function OpenAccessData(strdb)
local com_type, com_status
Local mydb = com.Create_Object("ADODB.Connection")
mydb.Open(?"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="++strdb)
quit(mydb)
;=====================
Function OpenAccessView(qname, mydb)
local com_type, com_status
Local MyView=com.Create_Object("ADODB.RecordSet")
local sqlstmt=?"SELECT * FROM "++qname
MyView.Open(sqlstmt, MyDb, 3)
com.localcopy(mydb)
quit(myview)
;=====================
Function MakeStructure(myview, mydb)
local com_type, com_status
com.localcopy(myview)
com.localcopy(mydb)
local fieldnames
for (local j=0; j<MyView.Fields.Count;j++)
fieldnames=fieldnames++MyView.Fields(j).Name++" "
endfor
fieldnames.=trim("\x20", 3)
;;pp structure doesn't like odd characters in field names
fieldnames=regex.pcrereplace(?"[/$...@#]",fieldnames,"_")
local myviewstruct=class.definestruct("myrecord", fieldnames)
static dbvars=myviewstruct.variables
quit(myviewstruct)
;======================
Function RecordsToVector(mydb, myview, myviewstruct)
local com_type, com_status
com.localcopy(mydb)
com.localcopy(myview)
local rcount=MyView.RecordCount
local ccount=MyView.Fields.Count
local VData=vec.create(rcount)
MyView.MoveFirst
for(local i=0; i<rcount; i++)
if (MyView.EOF)
Break
local rec = myviewstruct.new()
for each word myword index j in myviewstruct.variables
local myvarname="rec."++myword
do("&(myvarname) = MyView.Fields(j).Value")
endfor
vData[i]=rec
MyView.MoveNext
endfor
quit(vdata)
;======================
