OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

creating table with code

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
emari2
Newbie
Newbie


Joined: 18 Nov 2009
Posts: 3

PostPosted: Wed Nov 18, 2009 1:13 pm    Post subject: creating table with code Reply with quote

Is it posible to create a table with code-00basic- inside an open datbase?
Back to top
View user's profile Send private message
QuazzieEvil
Super User
Super User


Joined: 17 Jan 2007
Posts: 599
Location: Houston, TX

PostPosted: Wed Nov 18, 2009 1:43 pm    Post subject: Reply with quote

Yes, here is an example takes column info in array:
:

Code:

Function createTable(Conn As Object,TableName As String, ColDescriptors As Object,PKCol As String) As Boolean
On Error Goto HandleError
   REM CREATE A TABLE WITH GIVEN NAMES, COLUMN DESCRIPTIOSN, AND PRIMAKY KEY COL SPEC
   Dim I As Integer
   Dim J As Integer
   Dim Col()
   Dim Tables As Object
   Dim TableDescr As Object
   Dim ColDescr As Object
   Dim KeyDescr As Object
   Dim Keys AS Object
   Dim KeyCols As Object
   Dim Columns As Object
   
   Tables=Conn.Tables REM ACCESS TABLES SUPPLIER
   TableDescr=Tables.createDataDescriptor() REM CREATE TABLE DESFRIPTOR
   TableDescr.Name=TableName
   Columns=TableDescr.Columns
   
   For I=0 To UBound(ColDescriptors) REM CREATE & APPEND COLUMNS
      ColDescr=Columns.createDataDescriptor()
      Col=ColDescriptors(I)
      With ColDescr
         .Name=Col(0)
         .Type=Col(1)
         .IsNullable=Col(2)
         .IsAutoIncrement=Col(3)
         If Col(4)>0 Then .Precision=Col(4)
         .Description=Col(5)
      End With
      Columns.appendByDescriptor(ColDescr)
   Next I
   
   REM NOW CREATE PRIMARY KEY
   Keys=TableDescr.Keys
   KeyDescr=Keys.createDataDescriptor()
   KeyDescr.Type=com.sun.star.sdbcx.KeyType.PRIMARY
   KeyDescr.Name=TableName & "_PK"
   KeyCols=KeyDescr.Columns
   ColDescr=KeyCols.createDataDescriptor()
   ColDescr.Name=PKCol
   KeyCols.appendByDescriptor(ColDescr)
   Keys.appendByDescriptor(KeyDescr)
   
   REM FINALLY, APPEND TABLE TO TABLE SUPPLIER
   Tables.appendByDescriptor(TableDescr)

   Conn.Parent.DatabaseDocument.store()
   createTable=True
HandleError:
   If Err<>0 Then
      createTable=False
      Exit Function   
   End If
End Function


Sample column descriptors array (of arrays)

Code:

   SwitchBoardColumns=Array(_
                  Array("ID",DataTypes.INTEGER,0,False,0,"SWITCHBOARD ID"),_
                  Array("MAINTITLE",DataTypes.VARCHAR,1,False,64,"SWITCHBOARD MAIN TITLE"),_
                  Array("LABELWIDTH",DataTypes.INTEGER,1,False,0,"MENU ENTRY LABEL WIDTH"),_
                  Array("VOFFSET",DataTypes.INTEGER,1,False,0,"VERTICAL OFFSET"),_
                  Array("VSPACE",DataTypes.INTEGER,1,False,0,"VERTICAL SPACING"),_
                  Array("BUTTONSIZE",DataTypes.INTEGER,1,False,0,"BUTTON SIZE"),_
                  Array("HSPACE",DataTypes.INTEGER,1,False,0,"HORIZONTAL SPACE"),_
                  Array("HOFFSET",DataTypes.INTEGER,1,False,0,"HORIZONTAL OFFSET"),_
                  Array("X",DataTypes.INTEGER,1,False,0,"X"),_
                  Array("Y",DataTypes.INTEGER,1,False,0,"Y"),_
                  Array("WIDTH",DataTypes.INTEGER,1,False,0,"WIDTH"),_
                  Array("HEIGHT",DataTypes.INTEGER,1,False,0,"HEIGHT")_
                  )
[/code]
_________________
Free Docs @ http://www.baseprogramming.com/resources.html
Book @ lulu.com http://www.lulu.com/content/2455551
Back to top
View user's profile Send private message Visit poster's website
emari2
Newbie
Newbie


Joined: 18 Nov 2009
Posts: 3

PostPosted: Thu Nov 19, 2009 11:37 am    Post subject: Reply with quote

Many thanks. Have you a solution for this?
Sub appProb()

On Error GoTo 1

Dim XLTable As TableDef

Set Db = OpenDatabase(mdbFile)
Set XLTable = Db.CreateTableDef("tblProbTemp")

XLTable.Connect = "Excel 5.0;DATABASE=" & xlsFile
XLTable.SourceTableName = "tblXLProb"
Db.TableDefs.Append XLTable

strSQL = "Insert into tblProb Select * FROM tblProbTemp"
Db.Execute strSQL

1:
Err.Clear
On Error Resume Next
Db.TableDefs.Delete "tblProbTemp"
Db.Close

Regards
Emilio
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group