How to set up and Use an Access database

[Rick Lipkin]

Enrico

That seems a bit closer .. but I get an error after the listbox paints ..

Stack Calls
===========
Called from: win32ole.prg => TOLEAUTO:RECORDCOUNT(0)
Called from: access.prg => (b)_LWMFSBROW(60)
Called from: WBROWSE.PRG => TWBROWSE:DISPLAY(0)
Called from: CONTROL.PRG => TWBROWSE:HANDLEEVENT(0)
Called from: WINDOW.PRG => _FWH(0)
Called from: => WINRUN(0)
Called from: WINDOW.PRG => TMDIFRAME:ACTIVATE(0)
Called from: MAIN.PRG => MAIN(124)


here is the code ..

//-------------------------------------------
Func Access( oWND )

LOCAL oErr, oRs

oRs := CREATEOBJECT( "ADODB.Recordset" )

TRY
oRS:Open( "SELECT * FROM LWMFS ORDER BY FILE_NUM" , "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LWMFS.mdb", 1, 3 )
CATCH oErr
MsgInfo( "Error in Opening LWMFS table" )
RETURN NIL
END TRY

_LwmfsBrow(oRs ) //, oWND )

oRs:Close()
Return(nil)


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

LOCAL nRec, oDLG, oBTN1, oWND1, oBRW

lOK := .F.

DEFINE WINDOW oWnd1 ;
FROM 3,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;
COLSIZES 80,80,300,300;
HEADERS "Id Numb",;
"File_num" ,;
"File_name",;
"Address";
ON DBLCLICK( _Cercla( oRS:Fields( "file_num" ):value ) ) ;
UPDATE

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

oWND1:oClient := oBRW

ACTIVATE WINDOW oWND1

RETURN NIL

[Rick Lipkin]

Enrico

I see you are busy with Excel problems of your own .. I can zip up my files and send them to you .. difficult to troubleshoot this via the forum .. If you like you can send me a private message and I will send you the three files .. 2-prgs and the .mdb

Thanks
Rick Lipkin

lipkinrm29063@yahoo.com

[Enrico Maria Giordano]

Try to run my sample modified for your MDB and let me know if it works.

If it don't then please send me your MDB and I will try it with my sample.

EMG

[Rick Lipkin]

Enrico

I zipped up the 2-prg's and .mdb with two tables .. prox 1.6mg .. sent it to your provate e-mail ..

Let me know if you did not receive my message.

Rick Lipkin
lipkinrm29063@yahoo.com

[Enrico Maria Giordano]

I just tried your access.prg sample and it works fine here. I'm going to send you my EXE to test it there.

EMG

[Rick Lipkin]

To All

Is there a 'reference manual' for syntax on the object commands .. I want to know how to script a the syntax .. I know how to OPEN ( oRs:open( "SQL" ) .. for example

oRS:Insert ??
oRS:Delete ??
oRs:Add ??
oRs:Update ??

Just guessing there .. what I am trying to do in this snipit is to start a DELETE SQL query ( 'DELETE FROM table-name where search-condition ) to go out and DELETE all blank records with " " for a 'file_num' ??

Thanks
Rick Lipkin


//-----------------------
oRs := CREATEOBJECT( "ADODB.Recordset" )

TRY
oRS:Open( "SELECT * FROM LWMFS ORDER BY FILE_NUM" , "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LWMFS.mdb", 1, 3 )
CATCH oErr
MsgInfo( "Error in Opening LWMFS table" )
RETURN NIL
END TRY

// delete all global fule_num = blank

IF oRs:Fields( "file_num" ):Value = ' '
SAYING := "It appears that there are BLANK File Numbers"+chr(10)
SAYING += "Do you wish to DELETE them Now ?"+chr(10)

IF MsgYesNo( SAYING )
// PUT DELETE ACTION HERE
ENDIF
ENDIF

[Enrico Maria Giordano]

Is there a 'reference manual' for syntax on the object commands

The reference manual for ADO is in the MSDN.

oRS:Insert ??
oRS:Delete ??
oRs:Add ??
oRs:Update ??

Code: Select all

oRs:AddNew()  // appends a new empty record
oRs:Delete()  // deletes the current record
oRs:Update()  // commits the changes to the current record (mandatory)

Just guessing there .. what I am trying to do in this snipit is to start a DELETE SQL query ( 'DELETE FROM table-name where search-condition ) to go out and DELETE all blank records with " " for a 'file_num' ??

Code: Select all

FUNCTION MAIN()

    LOCAL oCn

    oCn = CREATEOBJECT( "ADODB.Connection" )

    oCn:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=clienti2.mdb" )

    ? oCn:Execute( "DELETE FROM Clienti WHERE Cliente LIKE 'A%'" )

    oCn:Close()

    RETURN NIL

EMG

[areang]

Hi Enrico !

Can I do this to open file on server directory:

Code: Select all

oCn:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server\datafiles\clienti2.mdb" ) 

Areang

[Enrico Maria Giordano]

I think so. Just try.

EMG

[Rick Lipkin]

Enrico

Sorry to both you once again .. I an trying to delete all blank file_num columns .. and I know the first record evals to NIL ..

I am getting a run time error and I just have no clue ??

Rick Lipkin

//------------------------------------
oRs := CREATEOBJECT( "ADODB.Recordset" )

TRY
oRS:Open( "SELECT * FROM LWMFS ORDER BY FILE_NUM" , "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LWMFS.mdb", 1, 3 )
CATCH oErr
MsgInfo( "Error in Opening LWMFS table" )
RETURN NIL
END TRY

// delete all global file_num = blank

oRs:MoveFirst()
cFILENUM := oRs:Fields( "file_num" ):Value

IF cFILENUM = nil
SAYING := "It appears that there are BLANK File Numbers"+chr(10)
SAYING += "Do you wish to DELETE them Now ?"+chr(10)

IF MsgYesNo( SAYING )
oRs:Execute( "DELETE FROM LWMFS WHERE FILE_NUM = 'NIL' " )
oRs:Update()
oRs:MoveFirst()
ENDIF
ENDIF

//-- error.log
========
Path and name: C:\FOX\DOCKIT\Dockit.Exe (32 bits)
Size: 1,420,288 bytes
Time from start: 0 hours 0 mins 2 secs
Error occurred at: 04/13/2007, 11:36:02
Error description: Error ADODB.Recordset/16389 E_FAIL: EXECUTE
Args:
[ 1] = C DELETE FROM LWMFS WHERE FILE_NUM = 'NIL'

Stack Calls
===========
Called from: win32ole.prg => TOLEAUTO:EXECUTE(0)
Called from: access.prg => ACCESS(34)
Called from: main.prg => (b)BUILDMENU(139)
Called from: MENU.PRG => TMENU:COMMAND(0)
Called from: WINDOW.PRG => TWINDOW:COMMAND(0)
Called from: MDIFRAME.PRG => TMDIFRAME:COMMAND(0)
Called from: WINDOW.PRG => TMDIFRAME:HANDLEEVENT(0)
Called from: WINDOW.PRG => _FWH(0)
Called from: => WINRUN(0)
Called from: WINDOW.PRG => TMDIFRAME:ACTIVATE(0)
Called from: main.prg => MAIN(124)

[Enrico Maria Giordano]

oRs:Execute( "DELETE FROM LWMFS WHERE FILE_NUM = 'NIL' " )

You can't use a recordset to execute a query. You need of a connection (see my previous sample). And please note that your query will delete all the records in which FILE_NUM is equal to the string 'NIL'. Use IsNull() function if you want to delete all the records with FILE_NUM equal to Null:

Code: Select all

oCn:Execute( "DELETE FROM LWMFS WHERE IsNull(FILE_NUM)" )

EMG

[Rick Lipkin]

Enrico

Thank you .. just never made the code distinction between an ( open ) recordset and an ( execute ) connection ..

I think I have all the recordset movements :

oRs:MoveNext() = skip
:MoveFirst() = go top
:MoveLast() = go bott
:Addnew() = Append
:Update() = commit
oRs:Fields( "FIELD" ):Value := 'MyValue' Replace

Difficult to use MSDN .. I got all of the above from searching this forum and your prior posts

Lets say I have a recordset filter with 10 rows on a related table.. and I want to update a new column with a new foreign key .. something like "INSERT INTO SECOND TABLE WITH "+"'"+cID+"'"+" FOR FILE_NUM = "+"'"+cFILE_NUM+"'" ) I am sure my syntax is incorrect .. will I need to release the recordset on the second table.. create a connection to the second table and run the query to update the 10 rows ?? ..


Two other ( hopefully last ) recordset questions ..

1) skip -1 ( possibly ) :MovePrior() ??
2) Delete current record ( possibly ) :DelCurrent() ??

Many thanks for ALL your help!!

Rick Lipkin

[Enrico Maria Giordano]

Lets say I have a recordset filter with 10 rows on a related table.. and I want to update a new column with a new foreign key .. something like "INSERT INTO SECOND TABLE WITH "+"'"+cID+"'"+" FOR FILE_NUM = "+"'"+cFILE_NUM+"'" ) I am sure my syntax is incorrect .. will I need to release the recordset on the second table.. create a connection to the second table and run the query to update the 10 rows ?? ..

Sorry, I don't understand. Can you rephrase, please?

Two other ( hopefully last ) recordset questions ..

1) skip -1 ( possibly ) :MovePrior() ??
2) Delete current record ( possibly ) :DelCurrent() ??

1) oRs:MovePrevious()
2) oRs:Delete()

EMG

[Rick Lipkin]

Enrico

Sounds complicated .. but not really .. that Access table I sent you had prob 6 more relational tables .. and they are all joined by the primary\foreign key of File_num.

This little application is an effort at cleaning up this data and instead of using file_num as the join .. use it as a way of identifying those rows that need to have inserted into them the ( matching ) ID sequence id for the lwmfs table.

The Lwmfs.mdb is the back end for a local intranet web application for our bureau . about 300 people. This application registers all the file documents that go into a certain file in the file room.

Over the years lots of bad data has been entered into this database and we are now at a point of re-structuring the relationships as we can no longer use file_num as a static column ( subject to change ).. then port all the data to MS Sql Server.

In the lwmfs table is a ID column which is numeric auto sequence and each of the relational tables have the same column .. but are not related to each other...

We want to stop the autoincrement of the relational ID columns and just use the ID column of the lwmfs table .. and identify the rows in each relational table .. and replace the ID column with the ID value in the lwmfs table.

What I am trying to do is write an application what orders the lwmfs table .. and identity all those relational rows in each of the other tables by their matching file_num .. then the user will chose a 'keeper' record in lwmfs and then I will fire off a cascade update to go out and re-order the relational tables with the matching ID from lwmfs.

What this program will do:

1) Allow a person to hi-lite a row in lwmfs as a keeper record ( may have multiple file_num rows with duplicate numbers .. that row becomes the unique 'keeper' record.

2) Go out and replace all the ID columns in the relational tables .. now joined by file_num to be the same ID of the keeper ( lwmfs ) record.

So I will have 2 SQL statements .. one as a gather the information and the other will be a replace statement for the same join .. only editing the ID column to match the keeper ID of the lwmfs table.

Looking for that magic INSERT sql statement and the connection string to update all the matching relational records and tables.

Hope this made better sense ..

Rick Lipkin

[Rick Lipkin]

Enrico

I am truly a 'fish out of water here' .. question after question and I truly appreciate your patience ..

Why when I try to open a recordset and the sql statement does not find any results .. can I not try to trap a 'no find' situation ..

If I find a matching file_num .. everything works fine.

Rick Lipkin

//---------------------------
oRs1 := CREATEOBJECT( "ADODB.Recordset" )
cSQL := "SELECT * FROM CERCLA_INDEXING WHERE ASSOCIATED_FILE_NUM ='"+cFILENUM+"' order by date_"

MsgInfo( cSQL )

TRY
oRS1:Open( cSQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LWMFS.mdb", 1, 3 )
CATCH oErr
MsginFo( "failed to open table" )
* ? oErr:Description
RETURN NIL
END TRY

oRS1:MoveLast() // trying to catch a nul recordset errors here


Path and name: C:\FOX\DOCKIT\Dockit.Exe (32 bits)
Size: 1,420,800 bytes
Time from start: 0 hours 0 mins 40 secs
Error occurred at: 04/13/2007, 19:33:40
Error description: Error ADODB.Recordset/16389 E_FAIL: MOVELAST
Args:

Stack Calls
===========
Called from: win32ole.prg => TOLEAUTO:MOVELAST(0)
Called from: => _CERCLA(164)
Called from: access.prg => (b)_LWMFSBROW(83)
Called from: CONTROL.PRG => TCONTROL:LDBLCLICK(0)
Called from: WBROWSE.PRG => TWBROWSE:LDBLCLICK(0)
Called from: WINDOW.PRG => TWINDOW:HANDLEEVENT(0)
Called from: CONTROL.PRG => TWBROWSE:HANDLEEVENT(0)
Called from: WINDOW.PRG => _FWH(0)
Called from: => WINRUN(0)
Called from: WINDOW.PRG => TMDIFRAME:ACTIVATE(0)
Called from: main.prg => MAIN(124)