1 2 Previous Next 

Access: How to create an Accdb databasefile with columns


With thanks to Paul Clement we were able to create this tip how to create from scratch a Accdb database file in code. Be aware there has to be set a reference (extensions) to the Access Interop
'Set a reference to Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Access.Dao
Module Exercise
    Sub Main()
        Try
            Dim AccessDatabaseEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine
            Dim AccessDatabase As Microsoft.Office.Interop.Access.Dao.Database
            AccessDatabase = AccessDatabaseEngine.CreateDatabase("C:\Test\NewDatabase.accdb"LanguageConstants.dbLangGeneral, DatabaseTypeEnum.dbVersion120)
            AccessDatabase.Close()
        Catch ex As Exception
            Console.Write(ex.Message)
            Console.ReadLine()
        End Try
       
       
        Using conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "  Data Source=C:\Test\NewDatabase.accdb;Persist Security Info=False;")
            Using cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
              "AutoId int identity ," & _
              "Id int NOT NULL," & _
              "Name NVarchar(50)," & _
                "BirthDate datetime," & _
               "IdCountry int," & _
                  "CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
                conn.Open()
                Try
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    Console.Write(ex.Message)
                    Console.ReadKey()
                End Try
            End Using
        End Using
    End Sub
End Module



Convert MS Access DataBase to another version format (also to 2007) in Visual Basic Code


Often is asked in the forums how to handle an MS Access 2003 file and an MS Access 2007 file.

There is a very simple way to convert those. Be aware it is possible that methods from one are not available in the other and are therefore then not always converted.

Option Strict On
'Set a reference to Microsoft.Office.Interop.Access
Imports Microsoft.Office.Interop.Access
Module Module1
    Sub Main()
        Dim theAccessObject As New Application
        theAccessObject.ConvertAccessProject("C:\Test1\nwind.mdb""C:\Test1\nwindAcc.accdb"AcFileFormat.acFileFormatAccess2007)
    End Sub
End Module



VS.Net Open database in 64 bit OS


If you try and open an access database on a 64bit os you will get the following error. System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. To prevent this open My Project -> compile -> advanced compiler options and set the target cpu to x86.

Otherwise get the new redistributable

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en



    Dim ds As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn As OleDbConnection
        Dim strConn As String
        Dim da As OleDbDataAdapter

        strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
        strConn &= "Data Source = c:\Northwind.mdb;"


        conn = New OleDbConnection(strConn)
        ds = New DataSet
        da = New OleDbDataAdapter("Select * from Products", conn)
        Try
            da.Fill(ds, "Products")
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
        DataGridView1.DataSource = ds.Tables("Products")
    End Sub



Access (OleDB): List Database Tables


Use the oledb connections GetOleDbSchemaTable method to list the tables in an access database.
Module Module1
    Sub Main()
        Dim strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
        strConn &= "Data Source = C:\Test1\Nwind.mdb;"
        Dim dtTableNames As DataTable
        Using conn As New OleDb.OleDbConnection(strConn)
            conn.Open()
            dtTableNames = conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, _
                New Object() {NothingNothingNothing"TABLE"})
        End Using
        Dim dr As DataRow
        For Each dr In dtTableNames.Rows
            Console.WriteLine(dr.Item("TABLE_NAME"))
        Next
        Console.ReadLine()
    End Sub
End Module



Access (OleDb): List Database Queries


Use the oledb connections GetOleDbSchemaTable method to list the queries in an access database.
      Imports System.Data.OleDb

Module Module1

    Sub Main()
        Dim strConn As String
        Dim conn As OleDbConnection

        strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
        strConn &= "Data Source = C:\Northwind.mdb;"

        conn = New OleDbConnection(strConn)

        conn.Open()

        Dim dtTableNames As DataTable

        dtTableNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, _
            Nothing)

        conn.Close()

        Dim dr As DataRow

        For Each dr In dtTableNames.Rows
            Trace.WriteLine(dr.Item("PROCEDURE_NAME"))
        Next
    End Sub

End Module



1 2 Previous Next