FiveTech Support Forums

// AccessDB.prg
// creating an access database from code
// creating an xbrowse from ADO
// allowing edit each cell

#include "FiveWin.ch"
#include "Xbrowse.ch"

STATIC lOK

//-------------
Func Main()

Local catNewDB,xProvider,xConnect,cFile,aDir,dExe,cDefa,mStart
Local oCn,cSql,oErr,oRsUser,cLOGIN,Saying
Local cTitle,oWnd1,oBrw,oCol,nYear


lOK := .F.

//-- get timestamp on .exe //

cFILE := GetModuleFileName( GetInstance() )
aDIR  := DIRECTORY( cFILE )
dEXE  := aDIR[1] [3]

// where .exe started from is default directory //

mSTART := RAT( "\", cFILE )
cDEFA  := SUBSTR(cFILE,1,mSTART-1)

aDIR := NIL
SET DEFA to ( cDEFA )

SET DELETED on
SET CENTURY on
SET 3DLOOK on

nYEAR := ( year( DATE() )-30 )
SET EPOCH to ( nYEAR )

Ferase( cDefa+"\Rick.mdb" )

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE   := cDEFA+"\Rick.mdb"
xPASSWORD := "dec2011"

// create the adox object
Try
   catNewDB := CreateObject("ADOX.Catalog")
Catch
   MsgInfo( "Could not Create ADOX object")
   Return(.f.)
End try

// create the table Rick.mdb
Try
  catNewDB:Create('Provider='+xProvider+';Data Source='+xSource+';Jet OLEDB:Engine Type=5;Jet OLEDB:Database Password='+xPASSWORD )
Catch
  MsgInfo( "Could not create the table "+xSource )
  Return(.f.)
End Try


// global connection string
xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD

Try
  oCn  := CREATEOBJECT( "ADODB.Connection" )
Catch
  MsgInfo( "Could not create the ADO object for connection")
End Try

TRY
   oCn:Open( xCONNECT )
CATCH oErr
  MsgInfo( "Could not open a Connection to Database "+xSource )
  RETURN(.F.)
END TRY


cSQL := "CREATE TABLE USERINFO"
cSQL += "( "
cSQL += "[USEREID] char(18) NOT NULL, "
cSQL += "[USERID] char(8) NULL, "
cSQL += "[READONLY] char(1) NULL, "
cSQL += "[WRITEONLY] char(1) NULL, "
cSQL += "[SUPER] char(1) NULL, "
cSQL += "[LASTLOG] datetime NULL, "
cSQL += "[CREATEDATE] datetime NULL, "
cSQL += "[PASSWORD] char(10) NULL, "
cSQL += "CONSTRAINT PK_USERINFO PRIMARY KEY ( USEREID )"
cSQL += " )"

// create the table Userinfo
// with primary key

Try
   oCn:Execute( cSQL )
Catch
   MsgInfo( "Table USERINFO Failed" )
   Return(.f.)
End try

oCn:Close()
oCn := nil

cLOGIN := UPPER( WNetGetuser() )+space(8) // fivewin
cLOGIN := SUBSTR(cLOGIN,1,8)

// open the Userinfo table record set
// append the first record
// could have also used a connection and the INSERT command

oRsUser := TOleAuto():New( "ADODB.Recordset" )
oRsUser:CursorType     := 1        // opendkeyset
oRsUser:CursorLocation := 3        // local cache
oRsUser:LockType       := 3        // lockoportunistic

// check for very first user

cSQL := "SELECT * FROM USERINFO"
TRY
   oRsUser:Open( cSQL, xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening USERINFO table here" )
   RETURN(.F.)
END TRY

If oRsUser:eof
   oRsUser:AddNew()

   oRsUser:Fields("UserEid"):Value    := "011111111111111111"
   oRsUser:Fields("UserId"):Value     := cLOGIN
   oRsUser:Fields("ReadOnly"):Value   := "Y"
   oRsUser:Fields("WriteOnly"):Value  := "Y"
   oRsUser:Fields("Super"):Value      := "Y"
   oRsUser:Fields("CreateDate"):Value := dtoc(DATE())+" "+time()
   oRsUser:Fields("LastLog"):Value    := dtoc(DATE())+" "+time()
   oRsUser:Fields("PassWord"):Value   := "ADMIN"

   oRsUser:Update()
Endif

cTITLE := "User Information Browse"

lOK := .F.

DEFINE WINDOW oWnd1                        ;
      FROM 5,5 to 30,75                    ;
      TITLE cTITLE                         ;

@ 0, 0 xBROWSE oBrw of oWnd1               ;
       RECORDSET oRsUser                   ;
       COLUMNS "USERID",                   ;
               "READONLY",                 ;
               "WRITEONLY",                ;
               "SUPER",                    ;
               "CREATEDATE",               ;
               "LASTLOG"                   ;
       COLSIZES 80,80,80,80,80,80          ;
       HEADERS "UserId",                   ;
               "Read",                     ;
               "Write",                    ;
               "Super",                    ;
               "Created",                  ;
               "LastLog"                   ;
       AUTOSORT AUTOCOLS FASTEDIT LINES CELL    // use fastedit if you want to edit cells

       oWnd1:oClient := oBrw

       oBrw:lHScroll := .f. // turn off horiz scroll bar

       oBrw:lFooter   := .t.
       oCol           := oBrw:aCols[ 1 ]
       oCol:bFooter   := { || Ltrim( Str( oBrw:KeyNo() ) ) + " / " + LTrim( Str( oBrw:KeyCount() ) ) }
       oCol:cHeader   := "UserId"
       oBrw:bChange   := { || oCol:RefreshFooter() }

       // allow edit of each field //
       AEVAL( oBrw:aCols, { |o| o:nEditType := EDIT_GET } )

       oBrw:CreateFromCode()

ACTIVATE WINDOW oWND1 ;
         ON INIT( oBrw:SetFocus(), .F. ) ;
         VALID ( IIF( !lOK, UserClose(.T.,oWnd1,oRsUser ), .F. ))

Return(nil)

//-------------------------------
Static FUNCTION UserClose( lCLEAN,oWnd1,oRsUser )

LOCAL lOK1

cDEFA := SET(7)

IF lCLEAN = .T.
   lOK  := .T.    // static close flag
   lOK1 := lOK  

   try
      oRsUser:CLose()
   catch
   end try

   oWnd1:End()

ENDIF

RETURN( lOK1 )

 // -- end

#include "FiveWin.Ch"
#include "xbrowse.ch"

//----------------------------------------------------------------------------//

function Main()

   local oRs, oDlg, oBrw, oFont

   XBrNumFormat( 'E', .t. )

   oRs   := OpenRecordSet()
   if oRs == nil
      ? 'Open fail'
      QUIT
   endif

   DEFINE FONT oFont NAME 'TAHOMA' SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 700,400 PIXEL FONT oFont

   @ 10,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg ;
      COLUMNS 'First', 'City', 'State', 'Salary' ;
      DATASOURCE oRs ;
      AUTOSORT FASTEDIT CELL LINES NOBORDER

   WITH OBJECT oBrw
      :nEditTypes    := EDIT_GET
      :nStretchCol   := 1
      //
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//

static function OpenRecordset()

   local oCn, cStr, oRs

   cStr     :=  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ;
                "c:\fwh\samples\xbrtest.mdb;User Id=admin;Password=;"

   if ( oCn := FW_OpenAdoConnection( cStr ) ) != nil
      oRs   := FW_OpenRecordSet( oCn, "CUSTOMER" )
   endif

return oRs

//----------------------------------------------------------------------------//