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
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
# 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 NILtest
