How to set up and Use an Access database

[Rick Lipkin]

To All

I have the need to setup and use an Access database .. here is my netuse function for DBFCDX .. what needs to change in order to use an Access database ??

Thanks
Rick Lipkin
SC Dept of Health, USA


REQUEST DBFCDX
rddsetdefault ( "DBFCDX" )
...
...


//-------------------------------
func NETUSE( CDATABASE, LOPENMODE, NSECONDS )

LOCAL FOREVER, RESTART, WAIT_TIME, YESNO

RESTART = .T.
FOREVER = ( NSECONDS = 0 )
YESNO := {"Yes" , "No"}

DO WHILE RESTART

WAIT_TIME = NSECONDS

DO WHILE ( FOREVER .OR. WAIT_TIME > 0 )

IF LOPENMODE
USE ( CDATABASE ) via "DBFCDX" EXCLUSIVE
ELSE
USE ( CDATABASE ) via "DBFCDX" SHARED
ENDIF

IF .NOT. NETERR()
RETURN(.T.)
ENDIF
INKEY(1)
WAIT_TIME--

ENDDO

* lock failed, ask to continue

IF MsgYesNo( "Cannot lock " + CDATABASE + ", retry ?" )
ELSE
EXIT
ENDIF

ENDDO

RETURN(.F.)

[Enrico Maria Giordano]

Do you want to use an RDD or directly via OLE?

EMG

[Rick Lipkin]

Enrico

I am wanting to open a access database with multiple tables within the database .. I was not aware of an RDD for Access .. but an RDD would be the best solution .. otherwise as you suggest .. perhaps OLE or maybe ODBC .. I will need to manipulate records and append and edit information.

Thanks
Rick Lipkin

[Enrico Maria Giordano]

This is an OLEDB sample to copy a field from a DBF to an MDB:

Code: Select all  Expand view  RUN

FUNCTION MAIN()

    LOCAL oRS

    USE CLIENTI

    oRS = CREATEOBJECT( "ADODB.Recordset" )

    oRS:Open( "SELECT * FROM Clienti", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=clienti.mdb", 0, 3 )

    WHILE !EOF()
        oRS:AddNew()

        oRS:Fields( "Cliente" ):Value = FIELD -> cliente

        oRS:Update()

        SKIP
    ENDDO

    oRS:Close()

    CLOSE

    RETURN NIL

EMG

[Rick Lipkin]

Enrico

Included is my first attempt in opening an Access .mdb with multiple tables .. Autimately what I want to do is open a listbox .. then double click on a line and relate ( select * for file_num = ) .. that part is to come.

I am having trouble defining the column's .. I have the oRs:Fields( "file_num" ):Value,; working because these are :text' .. but the ID columb is a LongInteger .. if I define it as Value .. I get a column with no data .. what are the object data type indentifiers .. for numeric, text, and dates ??

And .. how do I pass an object value like the 'file_num" to put in my sql statement to relate and spin off another listbox opening another table to return just those matching rows ??

Also .. I will have to edit some of the row data and build a dialog of values .. How would I define a column object like field->address ??

Thanks
Rick Lipkin


//----------------
#include "Fivewin.ch"
#include "Tcbrowse.ch"


FUNCTION MAIN()

LOCAL oRs, oErr

oRs := CREATEOBJECT( "ADODB.Recordset" )

TRY
oRS:Open( "SELECT * FROM LWMFS", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LWMFS.mdb", 1, 3 )
CATCH oErr
? oErr:Description
RETURN NIL
END TRY

WBROWSERECORDSET( oRs )
* TCBROWSERECORDSET( oRs )

oRs:Close()

RETURN NIL


STATIC FUNCTION WBROWSERECORDSET( oRs )

LOCAL oDlg, oBrw, nRec

DEFINE DIALOG oDlg SIZE 300, 300

@ 0, 0 LISTBOX oBrw FIELDS oRs:Fields( "id" ):Value,; // here
oRs:Fields( "file_num" ):Value,;
oRs:Fields( "file_name" ):Value,;
oRs:Fields( "address" ):Value;
HEADERS "Id_Num",;
"File_num" ,;
"File_name",;
"Address"
* ON RIGHT CLICK ( nRec := oRs:AbsolutePosition,;
* oBrw:Report( "TWBrowse report", .T. ),;
* oRs:MoveFirst(),;
* oRs:Move( nRec - 1 ) )

oBrw:bLogicLen = { || oRs:RecordCount }
oBrw:bGoTop = { || oRs:MoveFirst() }
oBrw:bGoBottom = { || oRs:MoveLast() }
oBrw:bSkip = { | nSkip | Skipper( oRs, nSkip ) }
oBrw:cAlias = "ARRAY"

ACTIVATE DIALOG oDlg;
ON INIT oDlg:SetControl( oBrw );
CENTER

RETURN NIL


STATIC FUNCTION TCBROWSERECORDSET( oRs )

LOCAL oDlg, oBrw, oCol, nRec

DEFINE DIALOG oDlg SIZE 300, 300

@ 0, 0 BROWSE oBrw //;
* ON RIGHT CLICK ( nRec := oRs:AbsolutePosition,;
* oBrw:Report( "TWBrowse report", .T. ),;
* oRs:MoveFirst(),;
* oRs:Move( nRec - 1 ) )

ADD COLUMN TO oBrw;
DATA oRs:Fields( "file_name" ):Value;
HEADER "File_name"

oBrw:lCellStyle = .T.

oBrw:bLogicLen = { || oRs:RecordCount }
oBrw:bGoTop = { || oRs:MoveFirst() }
oBrw:bGoBottom = { || oRs:MoveLast() }
oBrw:bSkip = { | nSkip | Skipper( oRs, nSkip ) }
oBrw:cAlias = "ARRAY"

ACTIVATE DIALOG oDlg;
ON INIT oDlg:SetControl( oBrw );
CENTER

RETURN NIL


STATIC FUNCTION SKIPPER( oRs, nSkip )

LOCAL nRec := oRs:AbsolutePosition

oRs:Move( nSkip )

IF oRs:EOF; oRs:MoveLast(); ENDIF
IF oRs:BOF; oRs:MoveFirst(); ENDIF

RETURN oRs:AbsolutePosition - nRec

[Enrico Maria Giordano]

Code: Select all  Expand view  RUN

@ 0, 0 LISTBOX oBrw FIELDS STR( oRs:Fields( "id" ):Value ),;

And .. how do I pass an object value like the 'file_num" to put in my sql statement to relate and spin off another listbox opening another table to return just those matching rows ??

"SELECT * FROM MyTable WHERE file_num =" + LTRIM( STR( oRs:Fields( "file_num" ):Value ) )

Also .. I will have to edit some of the row data and build a dialog of values .. How would I define a column object like field->address ??

oRs:Fields( "address" ):Value

EMG

[Rick Lipkin]

Enrico

Thanks .. i am close .. got the ID column to work .. now i am trying to pass the correct parameter to my function to spin off another recordset .. failing to open based on my parameter.

Example

..
..
ON DBLCLICK( _Cercla( oRS:Fields( "file_num" ):value ) ) // 010462


//---------------------------------------------
Static Func _Cercla( cFILENUM )

LOCAL oRs1, oErr, oBROW, oDLG1

// cFILENUM DOES = 010462 ..

oRs1 := CREATEOBJECT( "ADODB.Recordset" )

TRY
oRS1:Open( "SELECT * FROM CERCLA_INDEXING WHILE ASSOCIATED_FILE_NUM ="+cFILENUM , "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LWMFS.mdb", 1, 3 )
CATCH oErr
MsginFo( "failed to open table" ) // DIES HERE
* ? oErr:Description
RETURN NIL
END TRY

[Enrico Maria Giordano]

Code: Select all  Expand view  RUN

oRS1:Open( "SELECT * FROM CERCLA_INDEXING WHILE ASSOCIATED_FILE_NUM ='"+cFILENUM+"'" , "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LWMFS.mdb", 1, 3 )

EMG

[Rick Lipkin]

Enrico

Your parameter was PERFECT .. I did have a 'while' which should have been 'where' .. my dumb mistake there ..

Never would have figured ..

ASSOCIATED_FILE_NUM ='"+cFILENUM+"'"

Thanks a MILLION
Rick Lipkin

[Rick Lipkin]

Enrico

Another question .. within a recordset .. how can I 'locate' a specific record without changing the parameter of the select statement .. I would like to just do like :

seek '010462' or locate '010462' where 101462 is the file_num .. no indexes in the table that I know of by the way ..

Don't think it is that simple

RIck Lipkin

[Enrico Maria Giordano]

Can't you just scan the recordset?

EMG

[Rick Lipkin]

Enrico

2 more ( dumb ) questins ..

1) How would I do a table scan .. something like Locate for oRs:Fields( "file_num" ):Value = "010462' ??

2) Why when I try to set up a mdichild window instead of a dialog .. do I get a recordcount error ?? I have rem'd out the Window syntax and put back in the Dialog syntax and the code worked .. rem out the Dialog syntax and put the Window syntax and the browse seems to have problems defining the listbox.

Sorry to be such a pest on a holiday !!

Rick Lipkin



static Owndmdi

//--------------------------------------------------------
STATIC FUNCTION _LwmfsBrow( oRs, oWND )

LOCAL oBrw, nRec, oDLG

lOK := .F.

/*
DEFINE WINDOW oWndMdi ;
FROM 1,1 to 30,100 ;
Title "Docket System Browse";
Menu BuildMenu() ;
NoMinimize ;
NoZoom ;
of oWND ;
MDICHILD
*/
DEFINE DIALOG oDLG ;
FROM 1,1 to 30,100 ;
TITLE "Docket System Browse"

@ 0,0 LISTBOX oBrw FIELDS ;
STR( oRs:Fields( "id" ):Value ),;
oRs:Fields( "file_num" ):Value,;
oRs:Fields( "file_name" ):Value,;
oRs:Fields( "address" ):Value;
HEADERS "Id Numb",;
"File_num" ,;
"File_name",;
"Address";
SIZES 80,80,300,300;
-- of oWNDMDI ;
ON DBLCLICK( _Cercla( oRS:Fields( "file_num" ):value ) );
UPDATE

* oWNDMDI:ReFresh()
* oWNDMDI:SetColtrol( oBRW)

oBrw:bLogicLen = { || oRs:RecordCount }
oBrw:bGoTop = { || oRs:MoveFirst() }
oBrw:bGoBottom = { || oRs:MoveLast() }
oBrw:bSkip = { | nSkip | Skipper( oRs, nSkip ) }
oBrw:cAlias = "ARRAY"

ACTIVATE DIALOG oDlg;
ON INIT oDlg:SetControl( oBrw )
/*
ACTIVATE WINDOW oWNDMDI;
ON INIT oWNDMDI:SetControl( oBRW );
VALID ( IIF( !lOK, _LwmfsClose(.T.), .F. ))
*/


RETURN NIL

[Enrico Maria Giordano]

1) How would I do a table scan .. something like Locate for oRs:Fields( "file_num" ):Value = "010462' ??

oRs:Filter = "file_num = '010462''"

EMG

[Rick Lipkin]

Enrico

This is all new to me ( sorta like the excel thing about a week ago ) .. please bear with me .. on the second earlier item .. it seems the oBrw code blocks do not get evaluated if I use the Define Windows syntax .. any ideas there ??

Thanks
Rick Lipkin

[Enrico Maria Giordano]

This is working fine here:

Code: Select all  Expand view  RUN

#include "Fivewin.ch"
#include "Tcbrowse.ch"


FUNCTION MAIN()

    LOCAL oRs, oErr

    oRs = CREATEOBJECT( "ADODB.Recordset" )

    TRY
        oRS:Open( "SELECT * FROM Clienti", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=clienti.mdb", 1, 3 )
    CATCH oErr
        ? oErr:Description
        RETURN NIL
    END TRY

    WBROWSERECORDSET( oRs )
    TCBROWSERECORDSET( oRs )

    oRs:Close()

    RETURN NIL


STATIC FUNCTION WBROWSERECORDSET( oRs )

    LOCAL oWnd, oBrw, nRec

    DEFINE WINDOW oWnd

    @ 0, 0 LISTBOX oBrw FIELDS oRs:Fields( "Cliente" ):Value;
           HEADERS "CLIENTI";
           ON RIGHT CLICK ( nRec := oRs:AbsolutePosition,;
                            oBrw:Report( "TWBrowse report", .T. ),;
                            oRs:MoveFirst(),;
                            oRs:Move( nRec - 1 ) )

    oBrw:bLogicLen = { || oRs:RecordCount }
    oBrw:bGoTop    = { || oRs:MoveFirst() }
    oBrw:bGoBottom = { || oRs:MoveLast() }
    oBrw:bSkip     = { | nSkip | Skipper( oRs, nSkip ) }
    oBrw:cAlias    = "ARRAY"

    oWnd:oClient = oBrw

    ACTIVATE WINDOW oWnd

    RETURN NIL


STATIC FUNCTION TCBROWSERECORDSET( oRs )

    LOCAL oWnd, oBrw, oCol, nRec

    DEFINE WINDOW oWnd

    @ 0, 0 BROWSE oBrw;
           ON RIGHT CLICK ( nRec := oRs:AbsolutePosition,;
                            oBrw:Report( "TWBrowse report", .T. ),;
                            oRs:MoveFirst(),;
                            oRs:Move( nRec - 1 ) )

    ADD COLUMN TO oBrw;
               DATA oRs:Fields( "Cliente" ):Value;
               HEADER "CLIENTI"

    oBrw:lCellStyle = .T.

    oBrw:bLogicLen = { || oRs:RecordCount }
    oBrw:bGoTop    = { || oRs:MoveFirst() }
    oBrw:bGoBottom = { || oRs:MoveLast() }
    oBrw:bSkip     = { | nSkip | Skipper( oRs, nSkip ) }
    oBrw:cAlias    = "ARRAY"

    oWnd:oClient = oBrw

    ACTIVATE WINDOW oWnd

    RETURN NIL


STATIC FUNCTION SKIPPER( oRs, nSkip )

    LOCAL nRec := oRs:AbsolutePosition

    oRs:Move( nSkip )

    IF oRs:EOF; oRs:MoveLast(); ENDIF
    IF oRs:BOF; oRs:MoveFirst(); ENDIF

    RETURN oRs:AbsolutePosition - nRec

EMG