Creating an Access database from Code

Creating an Access database from Code

Postby Rick Lipkin » Sat Nov 19, 2011 3:22 pm

To All

Ran into some VB code on creating a MS Access database from code .. which seemed fairly straight forward :

Code: Select all  Expand view

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


However, translating the code I get an error at the Server:CreateObject("ADOX.Catalog") line

Code: Select all  Expand view

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:
 


Here is my code .. any help would be appreciated ..

Rick Lipkin

Code: Select all  Expand view

#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)
User avatar
Rick Lipkin
 
Posts: 2665
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Creating an Access database from Code

Postby Rick Lipkin » Sat Nov 19, 2011 3:28 pm

To All

Actually solved my problem .. just remove the word Server from the CreateObject line and the code works ..

Here it is ..
Code: Select all  Expand view

// 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
User avatar
Rick Lipkin
 
Posts: 2665
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Creating an Access database from Code

Postby Rick Lipkin » Sat Nov 19, 2011 5:09 pm

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
User avatar
Rick Lipkin
 
Posts: 2665
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Creating an Access database from Code

Postby Armando » Sat Nov 19, 2011 5:17 pm

Rick:

Thanks to share your source code.

Regards
SOI, s.a. de c.v.
estbucarm@gmail.com
http://www.soisa.mex.tl/
http://sqlcmd.blogspot.com/
Tel. (722) 174 44 45
Carpe diem quam minimum credula postero
User avatar
Armando
 
Posts: 3218
Joined: Fri Oct 07, 2005 8:20 pm
Location: Toluca, México

Re: Creating an Access database from Code

Postby Rick Lipkin » Sat Nov 19, 2011 7:21 pm

Armando

Seems we will be soon have to change up our syntax to be able to connect to Access 2007-2010.. Done some more research today and it looks like the Microsoft Access Engine (ACE Engine) will replace Jet.

I have downloaded the MS ACE runtime and am trying to connect to a .accdt Access Table. The Connection strings look similar if you are using ADO ..

See this link :

http://msdn.microsoft.com/en-us/library/ff965871.aspx

Let me know if you have had any success with Access 2010 ..

Thanks
Rick Lipkin
User avatar
Rick Lipkin
 
Posts: 2665
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Creating an Access database from Code

Postby Antonio Linares » Sat Nov 19, 2011 8:51 pm

Rick,

Thanks! :-)
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
Antonio Linares
Site Admin
 
Posts: 42085
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 97 guests