Creating acces table from dbf table

[Franklin Demont]

Hello ,

1) Next code gives as error :

Error description: (DOS Error -2147352567) WINOLE/1007 Syntaxisfout in velddefinitie. (0x80040E14): Microsoft JET Database Engine
Args:
[ 1] = C CREATE TABLE FrankDemont ( Id COUNTER PRIMARY KEY, FIRST VARCHAR ( 20 ), LAST VARCHAR ( 20 ), STREET VARCHAR ( 30 ), CITY VARCHAR ( 30 ), STATE VARCHAR ( 2 ), POSTNR VARCHAR ( 4 ), ZIP VARCHAR ( 10 ), HIREDATE DATETIME, MARRIED INT, AGE NUMERIC ( 2, 0 ), SALARY NUMERIC ( 9, 2 ), NOTES VARCHAR ( 70 ) )

The goal is to copy a dbf-file to a accessfile. In this code customer.dbf from samples is used.

2) When the acces file is created , which code should be used to populate the acces file ?
Frank

Code: Select all  Expand view

# include "fivewin.ch"
# include "dbstruct.ch"
# include "ado.ch"

#define STRIM( cStr, nChr ) Left( cStr, Len( cStr ) - nChr )
#define NTRIM( nNumber ) LTrim( Str( nNumber ) )


PROC MAIN()
LOCAL  aFlds
USE CUSTOMER
aFlds := DBSTRUCT()
//aFlds := {{"Frank","C",10,0},{"Getal","N",10,2}}
IF ! File("Test.mdb")
   FW_CreateMDB( "Test.mdb")  // FWH1305
END
AddTable("JET","FrankDemont",aFlds)
RETURN

*****************************************************************************************************************************

FUNCTION SQLEXEC( cQuery )

    LOCAL cCns := "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source=Test.mdb"//"Your connectionstring here"
           
    LOCAL oCn := CREATEOBJECT( "ADODB.Connection" )
    //cCns := "Provider='ACE.OLEDB.12.0'; Data Source=Test.mdb"   // doesn't work
    oCn:CursorLocation = adUseClient

    oCn:Open( cCns )

    //? cQuery
    oCn:Execute( cQuery )

    oCn:Close()

    RETURN NIL

FUNCTION ADDTABLE( cMot, cTab, aFld  )

    LOCAL cQuery := "CREATE TABLE " + cTab + " ( "

    LOCAL cType

    LOCAL i

    IF cMot == "JET"
        cQuery += "Id COUNTER PRIMARY KEY, "
    ELSEIF cMot == "MSSQL"
        cQuery += "Id INT IDENTITY PRIMARY KEY, "
    ELSEIF cMot == "MYSQL"
        cQuery += "Id SERIAL, "
    ENDIF

    FOR i = 1 TO LEN( aFld )
        cType = aFld[ i, DBS_TYPE ]

        DO CASE
            CASE cType = "C"
                cQuery += aFld[ i, DBS_NAME ] + " VARCHAR ( " + NTRIM( aFld[ i, DBS_LEN ] ) + " ), "
            CASE cType = "N"
                cQuery += aFld[ i, DBS_NAME ] + " NUMERIC ( " + NTRIM( aFld[ i, DBS_LEN ] ) + ", " + NTRIM( aFld[ i, DBS_DEC ] ) + " ), "
            CASE cType = "D"
                cQuery += aFld[ i, DBS_NAME ] + " DATETIME, "
            CASE cType = "L"
                cQuery += aFld[ i, DBS_NAME ] + " INT, "
            CASE cType = "M"
                IF cMot == "JET"
                    cQuery += "[" + aFld[ i, DBS_NAME ] + "]" + " MEMO, "
                ELSEIF cMot == "MSSQL"
                    cQuery += "[" + aFld[ i, DBS_NAME ] + "]" + " TEXT, "
                ELSEIF cMot == "MYSQL"
                    cQuery += aFld[ i, DBS_NAME ] + " TEXT, "
                ENDIF
        ENDCASE
    NEXT

    cQuery = STRIM( cQuery, 2 ) + " )"

    SQLEXEC( cQuery )

    RETURN NIL
 

[Rick Lipkin]

Frank

[ 1] = C CREATE TABLE FrankDemont ( Id COUNTER PRIMARY KEY, FIRST VARCHAR ( 20 ), LAST VARCHAR ( 20 ), STREET VARCHAR ( 30 ), CITY VARCHAR ( 30 ), STATE VARCHAR ( 2 ), POSTNR VARCHAR ( 4 ), ZIP VARCHAR ( 10 ), HIREDATE DATETIME, MARRIED INT, AGE NUMERIC ( 2, 0 ), SALARY NUMERIC ( 9, 2 ), NOTES VARCHAR ( 70 ) )

There is no VarChar field type in MS Access that I am aware of .. Char(30) will work. Below is some pure ADO code that will create a Database called Test.mdb and a Table called Customer and then import all the records from the Customer.Dbf.

Note .. the code below is a bit long ( not very elegant ) but it should provide a good example on how to create a .mdb with the .jet provider as well as converting a .dbf file to a table within the Test.mdb ( access ) database.

I am sure you can modify your code to generate the Create Table script from your .dbf and automate that process.

Rick Lipkin

Code: Select all  Expand view

// Ado test to create MS Access database and import
// Customer.dbf data

#Include "FiveWin.ch"

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

Local cDefa,cFile,aDir,nStart,nYear
Local xConnect,xSource,xPassword
Local catNewDb,Saying,oRsCust,cSql,oErr

SET DELETED on
SET CENTURY on
SET 3DLOOK on

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


REQUEST DBFCDX
rddsetdefault ( "DBFCDX" )

//-- get timestamp on .exe //

cFILE := GetModuleFileName( GetInstance() )
aDIR  := DIRECTORY( cFILE )

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

nSTART := RAT( "\", cFILE )
cDEFA  := SUBSTR(cFILE,1,nSTART-1)
aDIR   := NIL

SET DEFA to ( cDEFA )

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE   := cDEFA+"\Test.mdb"
xPASSWORD := "password"
xCONNECT  := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD

If .not. File( cDefa+"\Test.Mdb" )

   Saying := "WARNING .. the Database File "+cDefa+"\Test.Mdb"+chr(10)
   Saying += "does NOT exist. If this is your First time using this"+chr(10)
   Saying += "program .. Please proceed, otherwise contact your Administrator"+chr(10)
   Saying += " "+chr(10)
   Saying += "Do you wish to proceed to create the NEW Database ?"+chr(10)

   If MsgNoYes( saying )
   Else
      Return(.f.)
   Endif

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

   // create the table Test.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

   CatNewDb := nil

 *  cSay := "Creating Table Customer"
 *  oSay:ReFresh()
 *  SysReFresh()

   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 Customer"
   cSQL += "( "
   cSQL += "[CustomerEid]    Counter NOT NULL, "     // primary key
   cSql += "[First]          Char(20) NULL ,"
   cSql += "[Last]           Char(20) NULL ,"
   cSQL += "[Street]         Char(30) NULL, "
   cSQL += "[City]           Char(30) NULL, "
   cSQL += "[State]          Char(2)  NULL, "
   cSQL += "[Zip]            Char(10) NULL, "
   cSQL += "[HireDate]       DateTime NULL, "
   cSQL += "[Married]        Yesno    NULL, "
   cSQL += "[Age]            Integer DEFAULT 0, "
   cSQL += "[Salary]         Money   Default 0, "
   cSQL += "[Notes]          Memo     NULL, "
   cSQL += "CONSTRAINT PK_CUSTOMER PRIMARY KEY ( CustomerEid )"
   cSQL += " )"

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

   oCn:Close()
   oCn := nil

   If file( cDefa+"\Customer.dbf" )

    *  cSay := "Importing Legacy Customers "
    *  oSay:ReFresh()

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

      cSQL := "SELECT * FROM Customer"

      TRY
         oRsCust:Open( cSQL, xCONNECT )
      CATCH oErr
         Saying := "Can not open table CUSTOMER"
         Return(.f.)
      End Try

      Select 1
      Use ( cDefa+"\Customer.Dbf" ) via "DBFCDX" EXCLUSIVE
      Set Order to Tag Last

      Select Customer
      Go Top

      Do While .not. Eof()

         oRsCust:AddNew()

         oRsCust:Fields("First"):Value      := Customer->First
         oRsCust:Fields("Last"):Value       := Customer->Last
         oRsCust:Fields("Street"):Value     := Customer->Street
         oRsCust:Fields("City"):Value       := Customer->City
         oRsCust:Fields("State"):Value      := Customer->State
         oRsCust:Fields("Zip"):Value        := Customer->Zip
         oRsCust:Fields("HireDate"):Value   := Customer->HireDate
         oRsCust:Fields("Married"):Value    := Customer->Married
         oRsCust:Fields("Age"):Value        := Customer->Age
         oRsCust:Fields("Salary"):Value     := Customer->Salary
         oRsCust:Fields("Notes"):Value      := Customer->Notes

         oRsCust:Update()

         Select Customer
         Skip

      Enddo

      CLose Databases
      oRsCust:CLose()
      oRsCust := nil

   Endif
Endif

CLose Databases

MsgInfo( "Conversion Complete")

Return(nil)

// end

[Franklin Demont]

Rick,

I don't understand : the code from AddTable comes from this forum , i think from ernico. A few days ago i posted a problem (viewtopic.php?f=3&t=26608 , with this code) and it worked (after correction from enrico , protected word).

I don't see the difference. Maybe Enrico can tell us more.

Frank

[Rick Lipkin]

Frank

It appears that FW_CreateMDB creates the access database ( haven't seen the code ) .. and hopefully there are additional parameters to add a password.

Code: Select all  Expand view

IF ! File("Test.mdb")
   FW_CreateMDB( "Test.mdb")  // FWH1305
END
 

.. as far as creating a table ( within the .mdb ) from fields in a .dbf .. replace "VarChar" with "Char" for Ms Access .. "VarChar" is used for Sql Server not Ms Access.

Rick Lipkin

[Enrico Maria Giordano]

Franklin,

Hello ,

1) Next code gives as error :

Error description: (DOS Error -2147352567) WINOLE/1007 Syntaxisfout in velddefinitie. (0x80040E14): Microsoft JET Database Engine
Args:
[ 1] = C CREATE TABLE FrankDemont ( Id COUNTER PRIMARY KEY, FIRST VARCHAR ( 20 ), LAST VARCHAR ( 20 ), STREET VARCHAR ( 30 ), CITY VARCHAR ( 30 ), STATE VARCHAR ( 2 ), POSTNR VARCHAR ( 4 ), ZIP VARCHAR ( 10 ), HIREDATE DATETIME, MARRIED INT, AGE NUMERIC ( 2, 0 ), SALARY NUMERIC ( 9, 2 ), NOTES VARCHAR ( 70 ) )

FIRST and LAST are reserved words. If you must use them you have to enclose them in square brackets.

2) When the acces file is created , which code should be used to populate the acces file ?

Something like this:

Code: Select all  Expand view

FUNCTION MAIN()

    LOCAL oRS

    USE MYTABLE

    oRS = CREATEOBJECT( "ADODB.Recordset" )

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

    WHILE !EOF()
        oRS:AddNew()

        oRS:Fields( "MyField" ):Value = FIELD -> myfield

        oRS:Update()

        SKIP
    ENDDO

    oRS:Close()

    CLOSE

    RETURN NIL

EMG

[Enrico Maria Giordano]

Rick,

"VarChar" is used for Sql Server not Ms Access.

This is not true. VARCHAR works fine with Access.

EMG

[Franklin Demont]

Enrico , Rick

Thanks for the help.

Is a list of protected words available ?

FRank

[Rick Lipkin]

Enrico

I just tested that with my previous example .. and you are CORRECT .. the database is created using VarChar .. when you look at the table attributes VarChar does equate back to "Text"

Nice to know!!
Rick Lipkin

[Rick Lipkin]

Frank

When Creating or Selecting individual table attributes .. my rule of thumb is to always use brackets .. on every field... just my 2 cents worth.

Key,Counter I know are reserved.

Rick Lipkin

[Franklin Demont]

Enrico , Rick

Thanks , it is working now. I added to AddTable :

Code: Select all  Expand view

FOR EACH el IN aFld
        el[1] := ALLTRIM(el[1])
        IF LEFT(el[1],1)<>"["
           el[1] := "[" + el[1]
        END    
        IF RIGHT(el[1],1)<>"]"
           el[1] += "]"
        END    
NEXT
 

Frank

[Enrico Maria Giordano]

Franklin,

Enrico , Rick

Thanks , it is working now. I added to AddTable :

Code: Select all  Expand view

FOR EACH el IN aFld
        el[1] := ALLTRIM(el[1])
        IF LEFT(el[1],1)<>"["
           el[1] := "[" + el[1]
        END    
        IF RIGHT(el[1],1)<>"]"
           el[1] += "]"
        END    
NEXT
 

Frank

I reccomend you to not use reserved words as field names. You will likely have problems later.

EMG

[nageswaragunupudi]

It appears that FW_CreateMDB creates the access database ( haven't seen the code ) .. and hopefully there are additional parameters to add a password.

FW_CreateMDB(...) can create new MDB or ACCDB database. Right now there is no provision to specify a password.
In FWH13.06 we can specify a password as the second optional parameter

FW_CreateMDB( cDatabaseName, [ cPassWord ] )