To All
Here is the finished code ..
1) Creates ( from code ) the Access ( 2003 ) Database Rick.Mdb with a password
2) Create the Table UserInfo inside the Rick.Mdb database with a primary key
3) Append a new record to the new UserInfo table
Rick Lipkin
- Code: Select all Expand view
// AccessDB.prg
// creating an access database from code
#include "FiveWin.ch"
//-------------
Func Main()
Local catNewDB,xProvider,xConnect,cFile,aDir,dExe,cDefa,mStart
Local oCn,cSql,oErr,oRsUser,cLOGIN,Saying
//-- get timestamp on .exe //
cFILE := GetModuleFileName( GetInstance() )
aDIR := DIRECTORY( cFILE )
dEXE := aDIR[1] [3]
// where .exe started from is default directory //
mSTART := RAT( "\", cFILE )
cDEFA := SUBSTR(cFILE,1,mSTART-1)
aDIR := NIL
SET DEFA to ( cDEFA )
Ferase( cDefa+"\Rick.mdb" )
xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE := cDEFA+"\Rick.mdb"
xPASSWORD := "aug2011"
// create the adox object
Try
catNewDB := CreateObject("ADOX.Catalog")
Catch
MsgInfo( "Could not Create ADOX object")
Return(.f.)
End try
// create the table Rick.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
// global connection string
xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD
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 USERINFO"
cSQL += "( "
cSQL += "[USEREID] char(18) NOT NULL, "
cSQL += "[USERID] char(8) NULL, "
cSQL += "[READONLY] char(1) NULL, "
cSQL += "[WRITEONLY] char(1) NULL, "
cSQL += "[SUPER] char(1) NULL, "
cSQL += "[LASTLOG] datetime NULL, "
cSQL += "[CREATEDATE] datetime NULL, "
cSQL += "[PASSWORD] char(10) NULL, "
cSQL += "CONSTRAINT PK_USERINFO PRIMARY KEY ( USEREID )"
cSQL += " )"
// create the table Userinfo
// with primary key
Try
oCn:Execute( cSQL )
Catch
MsgInfo( "Table USERINFO Failed" )
Return(.f.)
End try
oCn:Close()
oCn := nil
cLOGIN := UPPER( WNetGetuser() )+space(8) // fivewin
cLOGIN := SUBSTR(cLOGIN,1,8)
// open the Userinfo table record set
// append the first record
// could have also used a connection and the INSERT command
oRsUser := TOleAuto():New( "ADODB.Recordset" )
oRsUser:CursorType := 1 // opendkeyset
oRsUser:CursorLocation := 3 // local cache
oRsUser:LockType := 3 // lockoportunistic
// check for very first user
cSQL := "SELECT * FROM USERINFO"
TRY
oRsUser:Open( cSQL, xCONNECT )
CATCH oErr
MsgInfo( "Error in Opening USERINFO table here" )
RETURN(.F.)
END TRY
If oRsUser:eof
oRsUser:AddNew()
oRsUser:Fields("UserEid"):Value := "011111111111111111"
oRsUser:Fields("UserId"):Value := cLOGIN
oRsUser:Fields("ReadOnly"):Value := "Y"
oRsUser:Fields("WriteOnly"):Value := "Y"
oRsUser:Fields("Super"):Value := "Y"
oRsUser:Fields("CreateDate"):Value := dtoc(DATE())+" "+time()
oRsUser:Fields("LastLog"):Value := dtoc(DATE())+" "+time()
oRsUser:Fields("PassWord"):Value := "ADMIN"
oRsUser:Update()
Endif
oRsUser:CLose()
Saying := "Database Rick.Mdb was created Successfully"+chr(10)
Saying += "Table UserInfo was created Successfully"+chr(10)
Saying += "One record appended to Table UserInfo Successful"+chr(10)
MsgInfo(saying )
Return(nil)
// -- end
