locked
Create table access database? RRS feed

  • Question

  • I have access database,one table Table1 and I wont to create another table but with the same structure as the table Table1 (columns: Broj iksice,Prezime,Ime,Broj indeksa,Broj pohadjanja).
    I used this code:

    Dim veza As OleDb.OleDbConnection = New OleDb.OleDbConnection(Global.Diplomski1.My.MySettings.Default.Studenti1Connection)
    Dim comm As OleDb.OleDbCommand = New OleDb.OleDbCommand("CREATE TABLE Marko ([Broj iksice] DOUBLE PRIMARY KEY UNIQUE, Prezime TEXT(30) NOT NULL, Ime TEXT(30) NOT NULL, [Broj indeksa] TEXT(20), [Broj pohadjanja] INTEGER)", veza)
                comm.ExecuteNonQuery()

    for creating table in the database.

    Code for creating table in the dataset Studenti1Data

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
            Dim Test As Data.DataTable = New Data.DataTable
            Novo(ComboBox1.Text, "nova")
            Test = Form2.Studenti1Data.Table1.Clone
            Test.TableName = "Marko"
            Form2.Studenti1Data.Tables.Add(Test)
            Form2.Studenti1Data.AcceptChanges()

        End Sub
    Where Novo(ComboBox1.Text, "nova") calls the code for creating table in the database.
    The dataset is on form2. The first time I open form2 everythig is OK. But when I close it and reopen, in Studenti1Data table Test (table name Marko) doesn't exist ,but the table Marko in the database is still present. I always clik on the save button it doesn't help this is the code:

    Me.Validate()

    Me.Table1BindingSource.EndEdit()
           
           Form1.Novo(Me.Studenti1Data.Tables.Item(Form1.Index(Form1.ComboBox1.Text)).TableName, "Update")

    Whats wrong?
    Help please...!

    Monday, January 29, 2007 7:05 PM

Answers


  • Not sure what type of problem you were having with adNumeric and adDecimal under ADOX. I would need to know what sort of error you are encountering. Creating a numeric data type with adNumeric is rather straightforward. You probably should be using adDouble instead of adDecimal for a floating point Access data type:

    Set col = New ADOX.Column
    With col
        .Name = "Col1"
        .Type = adDouble
    End With
    tbl.Columns.Append col

    For Jet SQL there are several data types you can use to define floating point columns but I would stick with Double.

    Yes, Access has been more or less de-emphasized as a .NET database solution and Microsoft is pushing their SQL Server technologies. SQL Server 2005 Compact Edition is a relatively easy distribution, but currently lacks the friendly user interface tools of Access.

    Tuesday, February 6, 2007 7:20 PM

  • In your example "Number" is a Jet reserved word. You should use a different name.

    Keep in mind that ADO was created to work with several different types of databases and that data types amongst databases will differ. Determining the correct data type value to use when creating a column or defining a parameter for a SQL query or stored procedure will often require trial and error.

    Wednesday, February 7, 2007 1:10 PM
  • If you need to create a new column and set the default value you can use Jet SQL. See the

    Wednesday, February 6, 2008 1:51 PM

All replies


  • Could you explain what you're attempting to accomplish in the Button6_Click event code? The code where you create a table looks OK but the the other code doesn't really make any sense to me.
    Tuesday, January 30, 2007 2:11 PM
  • The code where I create table is OK. On Button6 I add the test table to the dataset.
    Dim Test As Data.DataTable = New Data.DataTable creates new datatable to be added to the dataset
    Novo(ComboBox1.Text, "nova") executes this code:
    Dim veza As OleDb.OleDbConnection = New OleDb.OleDbConnection(Global.Diplomski1.My.MySettings.Default.Studenti1Connection)
    Dim comm As OleDb.OleDbCommand = New OleDb.OleDbCommand("CREATE TABLE Marko ([Broj iksice] DOUBLE PRIMARY KEY UNIQUE, Prezime TEXT(30) NOT NULL, Ime TEXT(30) NOT NULL, [Broj indeksa] TEXT(20), [Broj pohadjanja] INTEGER)", veza)
                comm.ExecuteNonQuery()

    Test = Form2.Studenti1Data.Table1.Clone Creates a structure of the Test table,same as in Table1

    Test.TableName = "Marko" Changes the name of the table in the dataset to match the one I created in the databse
            Form2.Studenti1Data.Tables.Add(Test) Adds the table to the Studenti1data dataset

            Form2.Studenti1Data.AcceptChanges() Accepts changes to the dataset.

    Dataadapter can not find the table in the database if I don't create the same table in the dataset.
    Everything works fine the first time I open form2 (dataset is on form2),when I close form2 and reopen it,Test  table (table name Marko) doesnt exist any more.But the table Marko in the database is stil in the database.
    What is the proble?
    Help please....!
    Thank you.
    Tuesday, January 30, 2007 4:16 PM

  • So you've created the table Marko in the Access database. What are you trying to do next? Do you want to add rows to the table? What are you using the Test DataTable in the Studenti1data DataSet for?
    Tuesday, January 30, 2007 6:26 PM
  • I wont to add rows or columns when ever I wont and view data in that table,like with a normal table.
    By reading help I understood that I need the same table in the dataset to comunicate with the table in the database.
    So when the table in the dataset disappers I can not add or delete or view or anything in the Test table in the database although it is there.
    What is the problem?
    Help please.
    Thank you!
    Tuesday, January 30, 2007 8:17 PM

  • Just add the new table to the DataSet using the DataAdapter. I created a new DataSet below but you can also use an existing Dataset:

    Dim ConnectionString As String

    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\My Documents\db1.mdb"

    Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)

    AccessConnection.Open()

    Dim AccessDataAdapter As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Marko", AccessConnection)

    Dim ds As New DataSet

    AccessDataAdapter.Fill(ds, "Marko")

    Wednesday, January 31, 2007 4:54 PM
  • Hello Paul,

    This is a little bit different. The Access dbs and it's tables are ADOX creations. These dbs/tables can be saved on the file system. Table(s) can also be deleted.

    For a newbie (me), the next step is to go to "Data >> Add New DataSource". Drop the created "DataSet"'s field(s) onto the Form's corresponding field(s). 
    Swell - for an  application not deployed. After that, while empty dbs/and its default tables can be created by the dozen, none of them can be attached to the Form's fields, rendering them useless.

    A little commercial program on my computer seemingly does this and more, without a hitch. It is based on Access. It back ups the db to any arbitrary location, can 'purge' it's data. It has a 'restore' button to bind a new or an old db to its GUI. It can also compact the db.  How could all these features implemented in code? 

    There are lots of people on the net scrambling after an answer. I would really appreciate your answer to get this monkey off my shoulder and get a rest.    

    Cheers,

     

    Saturday, February 3, 2007 5:44 AM

  • Could you rephrase your question. You describe to me what your application does but I can't really be sure what you are asking or determine where you need help?
    Saturday, February 3, 2007 2:04 PM
  • Thanks Paul,
    'Create a database
    Private Sub CreateAnAccessDataBase1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateAnAccessDataBase1.Click     'CREATE DB
            Dim Cat As ADOX.Catalog
            Cat = New ADOX.Catalog
            Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\AccessDB\NewMDB.mdb;Jet OLEDB:Engine Type=5")
            MessageBox.Show("Database was created")
            Cat = Nothing
        End Sub
    -----------------------
    'Create a table
    Private Sub CreateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateTable.Click                        
            Dim Cn As ADODB.Connection
            Dim Cat As ADOX.Catalog
            Dim Tablename As ADOX.Table
            Dim col As ADOX.Column = New ADOX.Column
            .....
            .....           
            Cn = New ADODB.Connection
            Cat = New ADOX.Catalog
            Tablename = New ADOX.Table
            Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\AccessDB\NewMDB.mdb")
            Cat.ActiveConnection = Cn
           Tablename.Name = "Table1"
            'col.Namespace = "ID"
            col.Name = "ID"
            col.Type = ADOX.DataTypeEnum.adInteger
            ......
            ......
            Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")
            Tablename.Columns.Append(col)
            .......
            .......
            'Tablename.Keys.Append("ID", ADOX.KeyTypeEnum.adKeyPrimary)
            Cat.Tables.Append(Tablename)
            MessageBox.Show("Table creation finished")
            'clean up objects
            Tablename = Nothing
            Cat = Nothing
            Cn.Close()
            Cn = Nothing
        End Sub
    ----------------------------------------------------------
    'delete a table

    Private Sub DeleteTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
    deleteTable.Click                                   
            Dim CT As ADODB.Connection
            CT = New ADODB.Connection
            Dim strSQL As String
            CT.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\AccessDB\NewMDB.mdb")
            strSQL = "DROP TABLE Table2"
            CT.Execute(strSQL)
            CT = Nothing
        End Sub
    ------------------------

    This is pretty much, what I have. My next step - in VB 2005 Express - is:
    1.  "Data>New Datasource>...."
    2.  Drop the "NewMDBDataSet"'s fields into the corresponding Form's (named DataEntry) field.

    My problem is that once this application is deployed ,there is no code in it to attach/detach new or old dataset to/from the Form,

    Newbie for ever,

     

     

    Saturday, February 3, 2007 5:44 PM

  • So are you using data binding? I'm not exactly sure how you've created your Form or what is on the Form. The following article might help if you're data binding at runtime:

    Roadmap for Windows Forms data binding

    Monday, February 5, 2007 4:12 PM
  • Hi Paul, (thanks for not jumping the ship just yet :-) ,

    XPSP2.
    VB 2005 Express

    Everything is coded (Data Entry Form, Access database, Tables) in VB 2005 Express.
    The Form is a Data Entry Form where the user can entry data into TextBoxes . Afterward some calculations are performed with the entered data, but basically that is it. This Form has also buttons triggering events to update, delete and add new row to the database,  plus some other ones to open other forms, etc.

    Actually what I am looking for is the standard Access function available in an "Access application". Purge, save and restore data.

    I am just guessing here - I am sure you know better - how this is done in "Access" . I can actually see on my computer as the database is created on D:\ AccessMDB\NewMDB.mdb (see code above), I can also see the increase of size of the *.mdb when table(s) are added. I am thinking out loud: can I grab this NewMDB and make a copy of it by the usual the file system operation, like copy save, rename? That would be = backup NewMDB.mdb. Later could I just replace it with an empty one (= purge NewMDB.mdb)? Restore would be placing back the old one into D:\AccessMDB\  ? Is it ?  Will this operation render the data into a jumble-bumble?

    Please keep with me for a little more. Your questioning helps me clear things up in my mind.

    Sincereley, 

    Monday, February 5, 2007 9:07 PM

  • There aren't really any standard functions for all if the operations that you describe, although most can be codes using various methods. You have a few options, and the choices involve handling the database as a complete file or at the object level. Working with the database at the object level you can use DAO, ADO (ADOX) and Jet SQL DDL. I tend to prefer Jet SQL DLL but there are some operations (such as creating a database) that require either the DAO or ADO/ADOX COM library.

    Working with the database at the file level implements standard file I/O operations and generally requires that the database be closed. In addition, file level operations on the database will affect all objects in the database as opposed to object level operations which affect only the objects specified in the commands.

    The following links should also help address some of the issues you've mentioned:

    Copy a database
    How To Compact Microsoft Access Database Through ADO

    Tuesday, February 6, 2007 2:36 PM
  • Hello again,

    This code compact a database.
    ------------------------------------------------------------------------
    Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CompactDB.Click
            Dim JRO As JRO.JetEngine
            JRO = New JRO.JetEngine
            JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\AccessDB\NewMDB.mdb", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\AccessDB\NewMDB2.mdb ;Jet OLEDB:Engine Type=5;")
            MessageBox.Show("Database was compacted")
    End Sub
    ------------------------------------------------------------------------
    The code in the previous post used ADOX to create the database - and tables. 

    I pasted the code from "Copy a database" into VB, ---> lots of squiggly lines. 

    I like your "Jet SQL DLL" tip regarding table creation. In the  proccess by ADOX  I ran into the difficulty and could not figure out how specify  DataType like adDecimal,  and adNumeric ?  If you had a code snippet showing how to do it, I would appreciate.

    Thanks for all,

    PS. There is an enormous interest (among us Newbies) to solve the backup/save, purge, restore mystery. Justly so. How could any database have any usefulness without these basic functions. I find it regrettable, that none of those who know better are interested to post the CODE on the net. None of the "How Do I" MS tutorials - neither the otherwise excellenty remarkable Tabor's series address this fundamental problem. We, hobbyist are left with SQL Express, which has none of these capabilites at our level and reach. (250MB installation + additional program(s) to do these operations). This very fact will render all our effort to deploy a small usable database application null and void.  The run for the deprecated Access database model just confirms this very same fact. (-steam released-)

    Tuesday, February 6, 2007 6:21 PM

  • Not sure what type of problem you were having with adNumeric and adDecimal under ADOX. I would need to know what sort of error you are encountering. Creating a numeric data type with adNumeric is rather straightforward. You probably should be using adDouble instead of adDecimal for a floating point Access data type:

    Set col = New ADOX.Column
    With col
        .Name = "Col1"
        .Type = adDouble
    End With
    tbl.Columns.Append col

    For Jet SQL there are several data types you can use to define floating point columns but I would stick with Double.

    Yes, Access has been more or less de-emphasized as a .NET database solution and Microsoft is pushing their SQL Server technologies. SQL Server 2005 Compact Edition is a relatively easy distribution, but currently lacks the friendly user interface tools of Access.

    Tuesday, February 6, 2007 7:20 PM
  • Thanks again,

    ......

    'col.Namespace = "Number"
            col.Name = "Number"
            col.Type = ADOX.DataTypeEnum.adInteger

    This is the problem I am encountering. Try replace adInteger with adNumeric or adDecimal in th above scenario. Table creation will not execute. Somewhere it is said an other parameter is needed for these two types - but it did not work out  for me (-it might be OK -but for  my lack of knowledge in programming certainly make it difficult for me to pin-point where the fault is)

    I did not say anything about Double problem. But  point well taken. I am still not clear about when one Type should be preferred over the other one in the case when both type doing the job well. (There are lots of information available about types on the net to study-not before I got this save....etc, thing solved). You mentioned Compact Edition - I looked at it too. It does not install on Express. I do not have Access - my application (preposterous to call it application) is tempted to  be built in VB Express - so there is little what I could say about that. But as the hype goes- I would prefer it over this ADOX/ADO/JRO mix. Definitively.

    Tuesday, February 6, 2007 10:14 PM

  • In your example "Number" is a Jet reserved word. You should use a different name.

    Keep in mind that ADO was created to work with several different types of databases and that data types amongst databases will differ. Determining the correct data type value to use when creating a column or defining a parameter for a SQL query or stored procedure will often require trial and error.

    Wednesday, February 7, 2007 1:10 PM
  • Thanks Paul - it was great, very helpful.

    Now I have to go, otherwise I will be late to manufacture consent, peaceful coexistence in a revelant data structure encapsulated with ideas wrapped in integrity refreshed
    by proper parameters following previously stored complex procedures inside disconnected Recordsets in a two-tier application.
    I will be in the coming days at Microsoft Knowledge Base where more info about this particular issue can be found. 

    Cheers,

     

    Wednesday, February 7, 2007 9:53 PM
  • I don't understand that why we canot create the mdb databases, theirs tables and fields using with the .NET components like oledb connection, datadapter, dataset? Why we have to use the SQL queries or old COM components like ADOX? One of them in the ways solving our problems but not completely! How can I make a Class with using ADOX or how can I create how a field that set the DefaultValue when no enter any value using with query language for Access mdb databases? Is there any way I didn't see yet? Microsoft gets new technologies and answering for new problems, but i've seen not completely. If "There isn't any way for do it" Then ((".NET is a new and strong language" = "Still using old technologies"= False) = True

    Thanks

    Tuesday, February 5, 2008 4:15 PM
  • If you need to create a new column and set the default value you can use Jet SQL. See the

    Wednesday, February 6, 2008 1:51 PM
  • This is an example to show how to create a new database from the ground up, create a new one (erasing old data), save it, restore it, compact it. The application (<euphemism) have an Entry Form, Table Forms (read-only) with resume TextBoxes (containing the sums of the individual columns) and a Database Form having create new, restore, and compact database buttons). Database data correction takes place in the Entry Form. Datarow is transferred into the Entry Form by clicking on the Table Form's row destined for correction.  

     

    Option Strict On

    Imports System.IO

     

    'Instructions:

    'Pre-Creation:

    'In Solution Explorer > Add Reference > COM :

    'Microsoft Jet and Replication Objects 2.6

    'Microsoft ADO Ext. 2.8 for DDL and Security

    'From Toolbox>FileDialog drag "OpenFileDialog" into the Form

     

    'Once the database was born:

    'In the Solution Explorer click on NewMDBDataSet1.xsd > click ID in Table1 > in Properties Window change Autoincrement to True

    'Do not forget: create a BackUps Folder before building the .exe in the Folder containing the source code. Watch also in the

    building process that the program will be placed to the proper place. (here for example: C:\Program Files\VSoft\Ledger\)

    The name of the Database created here is HOEXDB.mdb - of course you can give it any other name). The Back-ups go into

    the BackUps Folder ( C:\Program Files\VSoft\Ledger\BackUps\HOEXDB )

     

    Public Class AHomeSystemForm

    Public Shared Sub Restart()

    End Sub

     

    'BACK-UP THE DATABASE

    Private Sub BackUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUp.Click

    Dim timestamp, newstring, oldstring, timestampplusmdb As String

    Dim dlgboxBI As New DgDatabaseBackupinfo

     

    'Add a MonthDayYear_HoursMinutes Tag after NewMDB and moves it into BackUps Folder

    timestamp = Format(Now(), "_MMddyy_HHmm")

    oldstring = "C:\Program Files\VSoft\Ledger\HOEXDB.mdb"

    timestampplusmdb = timestamp + ".mdb"

    newstring = "C:\Program Files\VSoft\Ledger\BackUps\HOEXDB" & timestampplusmdb & ""

    Try

    File.Copy(oldstring, newstring)

    Catch ex As Exception

    System.Windows.Forms.MessageBox.Show(ex.Message)

    End Try

    'Show a DialogBox with the text:" A BackUp with today's timestamp was created in "C:\Program Files\Vsoft\Ledger\BackUps"

    If dlgboxBI.ShowDialog = Windows.Forms.DialogResult.OK Then

    End If

    End Sub

     

    'RESTORE THE DATABASE

    Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RestoreDB.Click

    Dim Filename As String

    Dim dlgboxRR As New DgDatabaseRestoreRestart

    Dim overwrite As Boolean

    overwrite = True

    Dim xi As String

    'Will open FileDialogBox. User have to find BackUps Folder in C:\Program Files\VSoft\Ledger\ and choose *.mdb database to restore

    With OpenFileDialog1

    .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"

    If .ShowDialog() = Windows.Forms.DialogResult.OK Then

    Filename = .FileName

    xi = "C:\Program Files\VSoft\Ledger\HOEXDB.mdb"

    Try

    File.Copy(Filename, xi, overwrite)

    Catch ex As System.Exception

    System.Windows.Forms.MessageBox.Show(ex.Message)

    End Try

    'If FileDialogBox's OK Button was clicked then

    'show DialogBox with "Database was successfully restored! Application will restart" text.

    If dlgboxRR.ShowDialog = Windows.Forms.DialogResult.OK Then

    Application.Restart()

    End If

    End If

    End With

    End Sub

     

    'CREAT A NEW DATABASE

    Private Sub CrNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrNewDB.Click

    Dim L As New DgDatabaseEraseWarning

    Dim Restart2 As New DgNewDatabaseRestart

    Dim Cat As New ADOX.Catalog

    Dim Cn As New ADODB.Connection

    Dim Tablename As New ADOX.Table

    'if there is a Database, it will be deleted

    If File.Exists("C:\Program Files\VSoft\Ledger\HOEXDB.mdb") Then

    If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then

    Exit Sub

    Else

    File.Delete("C:\Program Files\VSoft\Ledger\HOEXDB.mdb")

    End If

    End If

     

    'THIS IS THE MAKING OF A DATABASE

    Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\VSoft\Ledger\HOEXDB.mdb; Jet OLEDB:Engine Type=5")

    Dim col, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11,............................, col46, col47 As New ADOX.Column

    'Open the connection

    Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\VSoft\Ledger\HOEXDB.mdb; Jet OLEDB:Engine Type=5")

    'Open the Catalog

    Cat.ActiveConnection = Cn

    'Create the table (you can name it anyway you want)

    Tablename.Name = "Table2"

    'Taylor these columns according to your need - add so many as you need. Watch for the DataType!

    col.Name = "ID"

    col.Type = ADOX.DataTypeEnum.adInteger

    col1.Name = "Date"

    col1.Type = ADOX.DataTypeEnum.adDate

    col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    'Clothing

    col2.Name = "His"

    col2.Type = ADOX.DataTypeEnum.adSingle

    col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    col3.Name = "Her"

    col3.Type = ADOX.DataTypeEnum.adSingle

    col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    col4.Name = "CT"

    col4.Type = ADOX.DataTypeEnum.adSingle

    col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    'Education

    col5.Name = "Cour"

    col5.Type = ADOX.DataTypeEnum.adSingle

    col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    col6.Name = "CMat"

    col6.Type = ADOX.DataTypeEnum.adSingle

    col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    col7.Name = "Book"

    col7.Type = ADOX.DataTypeEnum.adSingle

    col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    col8.Name = "Libf"

    col8.Type = ADOX.DataTypeEnum.adSingle

    col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    col9.Name = "Nurs"

    col9.Type = ADOX.DataTypeEnum.adSingle

    col9.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    col10.Name = "MEdu"

    col10.Type = ADOX.DataTypeEnum.adSingle

    col10.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    col11.Name = "EDT"

    col11.Type = ADOX.DataTypeEnum.adSingle

    col11.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    ...............

    'these column were taken out for clarity (from 11->46) ------------ but you get the drift

    ................

    col47.Name = "Month"

    col47.Type = ADOX.DataTypeEnum.adSingle

    col47.Attributes = ADOX.ColumnAttributesEnum.adColNullable

    'Add Primary Key

    Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")

    'Append all the columns created above to the database

    Tablename.Columns.Append(col)

    Tablename.Columns.Append(col1)

    Tablename.Columns.Append(col2)

    Tablename.Columns.Append(col3)

    Tablename.Columns.Append(col4)

    Tablename.Columns.Append(col5)

    Tablename.Columns.Append(col6)

    Tablename.Columns.Append(col7)

    Tablename.Columns.Append(col8)

    Tablename.Columns.Append(col9)

    Tablename.Columns.Append(col10)

    Tablename.Columns.Append(col11)

    ...........................

    'these Colums.Append were taken out also (from 11->46)

    ...........................

    Tablename.Columns.Append(col46)

    Tablename.Columns.Append(col47)

    'Append the newly created table to the Tables Collection

    Cat.Tables.Append(Tablename)

    Tablename = Nothing

    Cat = Nothing

    Cn.Close()

    Cn = Nothing

     

    'RESTARTING THE DATABASE

    'Show DialogBox "NewDBRestart" with "New Empty Database was created! Application will restart" text.

    If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then

    Application.Restart()

    End If

    End Sub

     

    'COMPACT THE DATABASE

    Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CompactDB.Click

    Dim JRO As JRO.JetEngine

    JRO = New JRO.JetEngine

    'The first source is the original, the second is the compacted database under "OfficeComp" name.

    JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\VSoft\Ledger\HOEXDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\VSoft\Ledger\HOEXDBComp.mdb; JetOLEDB:Engine Type=5")

    'Here the original (not compacted database is deleted)

    File.Delete("C:\Program Files\VSoft\Ledger\HOEXDB.mdb")

    'Here the compacted database "OfficeComp" renamed to the original database name "Office".

    Rename("C:\Program Files\VSoft\Ledger\HOEXDBComp.mdb", "C:\Program Files\VSoft\Ledger\HOEXDB.mdb")

    'User notification

    MsgBox("The database was compacted successfully")

    End Sub

    'Close the Form

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Me.Close()

    End Sub

    End Class

     

    Cheers,

    Tuesday, April 1, 2008 11:48 PM