Insertar registros en un Dbf desde tabla MSAccess

[Jorge_T]

Hola,

Agradecería algún ejemplo desde fivewin de como añadir registros a un dbf desde una tabla de Access (mdb).


Muchas gracias, saludos,

[RBLANCO]

#include "FiveWin.ch"
#include "ado.ch"

STATIC oConnection, oRSet, oError, sSQLQuery

//------------------------------------------------------------------------------
FUNCTION MAIN()

SET DECIMAL TO 0

oConnection:=CreateObject( "ADODB.Connection" )
TRY
oConnection:open("Provider= MicroSoft.Jet.OLEDB.4.0;Data Source=C:\_DWH\DWH.MDB;")
CATCH oError
MsgAlert(" No se pudo establecer conexión con la base de datos..","Atención")
RETURN .F.
END

TRY
oRSet := CreateObject( "ADODB.RecordSet" )
CATCH oError
MsgStop( "No se ha podido crear el OBJETO"+ oError:Description )
RETURN .F.
END

oRSet:CursorLocation:=adUseClient;oRSet:LockType:=adLockReadOnly;oRSet:CursorType:=adOpenForwardOnly;oRSet:ActiveConnection( oConnection )

sSQLQuery := "SELECT * FROM CONSULMARKET"
TRY
oRSet:Open( sSQLQuery, oConnection )
CATCH oError
MsgStop( "No se ha podido crear el RECORDSET CONSULMARKET "+ oError:Description )
RETURN .F.
END

SELECT 1
USE MARKET

oRset:MoveFirst()

WHILE !oRset:EOF
VPP1:=SPACE(1)
VPP2:=SPACE(1)
VPP3:=SPACE(1)


VPP1:=oRset:Fields( "campo1_access" ):Value
VPP2:=oRset:Fields( "campo2_access" ):Value
VPP3:=oRset:Fields( "campo3_access" ):Value

IF VALTYPE(VPP2)='U'
VPP2:=0
ENDIF

IF VALTYPE(VPP3)<>'C'
VPP3:=SPACE(100)
ELSE
VPP3:=ALLTRIM(VPP3)
ENDIF

APPEND BLANK
REPLACE c1_DBF WITH ALLTRIM(STR(VPP1)) ,;
c2_DBF WITH ALLTRIM(STR(VPP2)) ,;
c3_DBF WITH ALLTRIM(VPP3)


oRset:MoveNext()
ENDDO

oRset:Close()

SELECT 1
USE

[Rick Lipkin]

Jorge_T

RBLANCO has a good example .... the code below is another way to check if a sql value is null all wrapped up in a single statement ..

Code: Select all  Expand view

a->name := if( empty( oRs:Field("name"):Value), space(10), oRs:Field("name"):Value))

 

Rick Lipkin

[nageswaragunupudi]

Very simple using FWH functions. Just a very few lines of code.

This is an example where the structures of both the access table and the dbf are same. This sample reads all records from access table and inserts them all into dbf table.

Code: Select all  Expand view

oCn   := FW_OpenAdoConnection( "yourdb.mdb" )
oRs   := FW_OpenRecordSet( oCn, "mytable" )
aData := RsGetRows( oRs )
USE DEST.DBF NEW VIA "DBFCDX"  // EXCLUSIVE OR SHARED
DEST->( FW_ArrayToDBF( aData ) )
 

More complex case.

This example reads three fields "NAME", "CITY" and "DATE" from the access table for condtion where STATE='NY' and inserts these records into DBF with field names "CUST", "TOWN" and "INVDT"

Code: Select all  Expand view

oCn   := FW_OpenAdoConnection( "yourdb.mdb" )
oRs   := FW_OpenRecordSet( oCn, "SELECT " + cMdbFieldList + " FROM mytable WHERE STATE = 'NY'" )
aData := RsGetRows( oRs )
USE DEST.DBF NEW VIA "DBFCDX"  // EXCLUSIVE OR SHARED
DEST->( FW_ArrayToDBF( aData, cDbfFieldList ) )