FiveTech Software tech support forums

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.)

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

EMG

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

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

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

Rick Lipkin wrote: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 ) )

Rick Lipkin wrote: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

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

EMG

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

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

Can't you just scan the recordset?

EMG

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

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

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

EMG

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

This is working fine here:
EMG