FiveTech Support Forums

Sub CreateAccessDatabase(sDatabaseToCreate)
    Dim catNewDB ' As ADOX.Catalog
    Set catNewDB = Server.CreateObject("ADOX.Catalog")
    catNewDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDatabaseToCreate & _
        ";Jet OLEDB:Engine Type=5;"
      ' Engine Type=5 = Access 2000 Database
      ' Engine Type=4 = Access 97 Database
    Set catNewDB = Nothing
 End Sub

Application
===========
   Path and name: C:\Fox\Rick\Access.Exe (32 bits)
   Size: 1,873,920 bytes
   Time from start: 0 hours 0 mins 4 secs 
   Error occurred at: 11/19/11, 10:13:29
   Error description: Error BASE/1003  Variable does not exist: SERVER
   Args:

#include "FiveWin.ch"

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

Local catNewDB,xProvider,cFile,aDir,dExe,cDefa,mStart


//-- 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 )

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE   := cDEFA+"\Rick.mdb"

catNewDB := Server:CreateObject("ADOX.Catalog")  // errors here on Server
catNewDB:Create('Provider='+xProvider+';Data Source='+xSource+';Jet OLEDB:Engine Type=5' )

Return(nil)

// AccessDB.prg
// creating an access database from code

#include "FiveWin.ch"

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

Local catNewDB,xProvider,cFile,aDir,dExe,cDefa,mStart


//-- 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 )

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE   := cDEFA+"\Rick.mdb"
*xPASSWORD := "aug2011"

catNewDB := CreateObject("ADOX.Catalog")
catNewDB:Create('Provider='+xProvider+';Data Source='+xSource+';Jet OLEDB:Engine Type=5' )

Return(nil)

 // -- end

// 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