locked
Creating a dynamic data base RRS feed

  • Question

  • I'm not new to VB, but have not written code since 1997.  Using VB back then we could create dynamic data bases from within the program which could store data input.  Example is a simple address book.  I'm not seeing that capability now.  It seems like one can only access a pre-programmed data set now, such as Access, no capality of adding fields dynamically. 

    Also, the function to perform calculations to update data within the database seems to be lost as well.  Example:  A simple list of invemtory items which may experience market price increases.  You should be able to write code that would increase all prices 3% across the board, or even selected items in a set. 

    Please tell me that these things are still possible in VB.  And how?
    Sunday, September 7, 2008 5:28 PM

Answers

  • Elcobar said:

    I'm not new to VB, but have not written code since 1997.  Using VB back then we could create dynamic data bases from within the program which could store data input.  Example is a simple address book.  I'm not seeing that capability now.  It seems like one can only access a pre-programmed data set now, such as Access, no capality of adding fields dynamically. 


    Hi Elcobar,

    Welcome to MSDN forums!

    1.
    In VB.NET, you can create dynamically and programmatically MS Access databases and tables by using the Microsoft Jet OLE DB Provider and Microsoft ADO Ext. 2.x for DDL and Security (ADOX) with the COM Interop layer.

    Please try the following code sample:
    Firstly Add Reference COM component Microsoft ADO Ext. 2.7 for DDL and Security to your project.

    Imports ADOX  
    Imports System.Data.OleDb  
     
    Public Class Form1  
     
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click  
            Dim databaseName As String = "C:\AccessDB.mdb" 
            Dim tableName As String = "MyTable" 
     
            ' Part 1: Create Access Database file using ADOX  
            Dim cat As ADOX.Catalog = New ADOX.Catalog()  
            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & databaseName & ";Jet OLEDB:Engine Type=5")  
            MessageBox.Show("Database Created Successfully")  
            cat = Nothing 
     
            ' Part 2: Create one Table using OLEDB Provider   
            Dim con As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & databaseName)  
            con.Open()  
            'Get database schema  
            Dim dbSchema As DataTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {NothingNothing, tableName, "TABLE"})  
          con.Close()  
            ' If the table exists, the count = 1  
            If dbSchema.Rows.Count > 0 Then 
                ' do whatever you want to do if the table exists  
            Else 
                'do whatever you want to do if the table does not exist  
                ' e.g. create a table  
                Dim cmd As New OleDb.OleDbCommand("CREATE TABLE [" + tableName + "] ([Field1] TEXT(10), [Field2] TEXT(10))", con)  
                con.Open()  
                cmd.ExecuteNonQuery()  
                MessageBox.Show("Table Created Successfully")  
                con.Close()  
            End If 
     
        End Sub 
     
    End Class 

    Some tutorials:
    Create a Microsoft Access Database Using ADOX and Visual Basic .NET
    http://www.codeguru.com/vb/gen/vb_database/microsoftaccess/article.php/c5149/

    Create Access database table in VB.NET using OleDb data Provider.
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2822564&SiteID=1


    Elcobar said:

    Also, the function to perform calculations to update data within the database seems to be lost as well.  Example:  A simple list of invemtory items which may experience market price increases.  You should be able to write code that would increase all prices 3% across the board, or even selected items in a set. 

    2. In VB.NET, you can execute T-SQL statement to update data (in batch) within the database like this:

    Imports System.Data.OleDb  
     
    Public Class Form1  
     
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click  
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")  
            Dim cmd As OleDbCommand  
            cmd = New OleDbCommand("Update Table1 Set Price = Price * 1.03", con)  
            con.Open()  
            cmd.ExecuteNonQuery()  
            con.Close()  
        End Sub 
     
    End Class 
     


    Best regards,
    Martin Xie

    Thursday, September 11, 2008 7:03 AM