Results 1 to 6 of 6

Thread: create Access Database with Code

  1. #1

    Thread Starter
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    create Access Database with Code

    this question has come up a few times....
    .....can I use an Access Database on a system where Access is not Installed.....

    well the answer is yes

    it will work only if the .mdb Jet 4.0 is good enough for the Job, the .mdb
    will work, if you choose the .accdb Version Access will have to be Installed

    here two ways to create an .mdb Database with Code.

    1) with Powershell- this will create a .mdb or .accdb
    just open Powershell and add this Script, change to the Path and Access version you need
    see Image
    Name:  powershell.jpg
Views: 1679
Size:  29.3 KB

    the Powershell script
    Code:
    $pathToAccess= New-Item -Path 'E:\TestAccess' -ItemType Directory #Set DB Location
    
    #which Database Format ?
    #To create database with Access 2002-2003 file format
    New-AccessDatabase -name test03.mdb $pathToAccess #<- use this Version, Access does not have to be Installed
    
    #To create database with Access 2007 file format
    #New-AccessDatabase -name test07.mdb -path E:\Berichte -acc7
    
    # for this Access Version accdb <- Access has to be Installed
    #To create database with Access 2007 accdb file format 
    #New-AccessDatabase -name atest07.accdb -path E:\Berichte
    
    ########### don't change anything inside this Block ####################################################################
    function New-AccessDatabase {                                                                                          #
    param (                                                                                                                #
        [string]$name,                                                                                                     #
        [string]$pathToAccess,                                                                                             #
        [switch]$acc7                                                                                                      #
    )                                                                                                                      #
        if (!(Test-Path $pathToAccess)){Throw "Invaild Folder"}                                                            #
        $file = Join-Path -Path $pathToAccess -ChildPath $name                                                             #
        if (Test-Path $file){Throw "File Already Exists"}                                                                  #
        $cat = New-Object -ComObject ‘ADOX.Catalog’                                                                        #
        if ($acc7) {$cat.Create("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$file")}                                   #
        else {                                                                                                             #
            if ($name -match ".accdb") {$cat.Create("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$file")}               #
            else {$cat.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$file")}                                      #
        }                                                                                                                  #
        $cat.ActiveConnection.Close()                                                                                      #
    }                                                                                                                      #
    function Open-AccessDatabase {                                                                                         #
    param (                                                                                                                #
        [string]$name,                                                                                                     #
        [string]$pathToAccess                                                                                              #
    )                                                                                                                      #
        $file = Join-Path -Path $pathToAccess -ChildPath $name                                                             #
        if (!(Test-Path $file)){Throw "File Does Not Exists"}                                                              #
        $connection = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$file") #
        $connection.Open()                                                                                                 #
        $connection                                                                                                        #
    }                                                                                                                      #
    function Close-AccessDatabase {                                                                                        #
    param (                                                                                                                #
        [System.Data.OleDb.OleDbConnection]$connection                                                                     #
    )                                                                                                                      #
        $connection.Close()                                                                                                #
    }                                                                                                                      #
    function New-AccessTable {                                                                                             #
    ## assumes database is open                                                                                            #
    param (                                                                                                                #
        [string]$sql,                                                                                                      #
        [System.Data.OleDb.OleDbConnection]$connection                                                                     #
    )                                                                                                                      #
        $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)                                                #
        $cmd.ExecuteNonQuery()                                                                                             #
    }                                                                                                                      #
    ########################################################################################################################
    
    # open the created Database
    $db = Open-AccessDatabase -name test03.mdb $pathToAccess # <---- Database in Folder
    
    
    #create your Table tbl_Employee
    $sql=@"
    Create Table tbl_Employee
    (EM_ID AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY,
    EM_Firstname varChar(50),
    EM_Lastname varChar(50) CONSTRAINT EM_Lastname UNIQUE)
    "@
    New-AccessTable -sql $sql -connection $db #<----------- add the Table to Database
    
    #create your Table tbl_Employee
    $sql=@"
    Create Table tbl_Contacts
    (CO_ID AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY,
    FK_ID INTEGER,
    CO_Firstname varChar(50),
    CO_Lastname varChar(50) CONSTRAINT CO_Lastname UNIQUE)
    "@
    New-AccessTable -sql $sql -connection $db #<----------- add the Table to Database
    
    $sql=@"
    ALTER TABLE tbl_Contacts
    ADD FOREIGN KEY (FK_ID) REFERENCES tbl_Employee(EM_ID)
    "@
    New-AccessTable -sql $sql -connection $db #<----------- add Relation to Table
    
    #add some Employees
    $sql=@"
    Insert Into tbl_Employee (EM_Firstname,EM_Lastname)Values('ChrisE','Test')
    "@
    New-AccessTable -sql $sql -connection $db #<----------- add to Table
    $sql=@"
    Insert Into tbl_Employee (EM_Firstname,EM_Lastname)Values('John','Doe')
    "@
    New-AccessTable -sql $sql -connection $db #<----------- add to Table
    
    #add some Contacts
    $sql=@"
    Insert Into tbl_Contacts (CO_Firstname,CO_Lastname,FK_ID)Values('Mary','Poppins',1)
    "@
    New-AccessTable -sql $sql -connection $db #<----------- add to Table
    $sql=@"
    Insert Into tbl_Contacts (CO_Firstname,CO_Lastname,FK_ID)Values('Joe','Dipp',2)
    "@
    New-AccessTable -sql $sql -connection $db #<----------- add to Table
    
    ########### don't change anything here #################################################################################
    Close-AccessDatabase -connection $db # always this command when your done !!                                           #
    ########################################################################################################################

    with .Net
    create a new Project, and add/Set Ref.

    Code:
    'set references to MS ADO Ext. 2.x for DDL and Security 
    'select WindowApplication1 
    '-Properties
    '--Compile
    '---Advanced Complie Options
    'and choose instead of AnyCPU the option x86
    
    Imports System.Data.OleDb
    
    Public Class Form1
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'Create the Database and the Tables
            CreateDatabase("E:\Contacts.mdb", , 5, )
            MakeTables()
        End Sub
    
        Public Sub MakeTables()
            Dim sSQL As String
    
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\Contacts.mdb")
    
            'here you create your Table in the Database
            con.Open()
            'create your Table tbl_Employee
            sSQL = " Create Table tbl_Employee"
            sSQL &= "( [EM_ID] AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY"
            sSQL &= ", [EM_Firstname] varChar(50)"
            sSQL &= ", [EM_Lastname] varChar(50) CONSTRAINT EM_Lastname UNIQUE"
            sSQL &= ")"
            ExecuteSQL(con, sSQL)
    
            sSQL = " Create Table tbl_Contacts"
            sSQL &= "( [CO_ID] AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY"
            sSQL &= ", [FK_ID] Int NOT NULL"
            sSQL &= ", [CO_Firstname] varChar(50)"
            sSQL &= ", [CO_Lastname] varChar(50) CONSTRAINT CO_Lastname UNIQUE"
            sSQL &= ")"
            ExecuteSQL(con, sSQL)
    
    
            sSQL = "ALTER TABLE tbl_Contacts "
            sSQL &= " ADD FOREIGN KEY (FK_ID) REFERENCES tbl_Employee(EM_ID)"
            ExecuteSQL(con, sSQL)
    
            sSQL = "Insert Into tbl_Employee (EM_Firstname,EM_Lastname)Values('ChrisE','Test')"
            ExecuteSQL(con, sSQL)
    
            sSQL = "Insert Into tbl_Employee (EM_Firstname,EM_Lastname)Values('John','Doe')"
            ExecuteSQL(con, sSQL)
    
    
            sSQL = "Insert Into tbl_Contacts (CO_Firstname,CO_Lastname,FK_ID)Values('Mary','Poppins',1)"
            ExecuteSQL(con, sSQL)
    
            sSQL = "Insert Into tbl_Contacts (CO_Firstname,CO_Lastname,FK_ID)Values('Joe','Dipp',2)"
            ExecuteSQL(con, sSQL)
    
    
            con.Close()
            con = Nothing
        End Sub
    
    
        Public Function CreateDatabase(ByVal MDBName As String, _
                                         Optional ByVal Password As String = Nothing, _
                                         Optional ByVal EngineType As Int32 = 5, _
                                         Optional ByVal ErrMessage As String = Nothing) As Boolean
    
            Dim Sw As New System.IO.StringWriter
    
            Sw.Write("Provider=Microsoft.Jet.OLEDB.4.0;")
            Sw.Write("Jet OLEDB:Engine Type = {0};", EngineType.ToString)
            Sw.WriteLine("Data Source={0}", MDBName)
            If Not Password = Nothing Then
                Sw.Write(";Jet OLEDB:Database Password={0}", Password)
            End If
    
            Try
                Dim Cat As New ADOX.Catalog
                Cat.Create(Sw.ToString)
                Dim Msg As String = "Fehler bei Freigabe ADOX.Cat"
                If Not ReleaseComObject(Cat.ActiveConnection) Then
                    ErrMessage = Msg
                    Return False
                ElseIf Not ReleaseComObject(Cat) Then
                    ErrMessage = Msg
                    Return False
                End If
                Return True
    
            Catch ex As Exception
                ErrMessage = ex.Message
                Return False
            End Try
    
            Return True
        End Function
        Private Function ReleaseComObject(ByVal objCom As Object) As Boolean
            Dim Result As Integer = 0
            For i As Integer = 0 To 9
                Result = Runtime.InteropServices.Marshal.FinalReleaseComObject(objCom)
                If Result = 0 Then
                    Return True
                End If
                System.Threading.Thread.Sleep(0)
                Application.DoEvents()
            Next
            Return False
        End Function
    
        Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                         ByVal sSQL As String, _
                                         Optional ByRef ErrMessage As String = Nothing, _
                                         Optional ByVal TransAction As  _
                                         OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    End Class
    HTH

    upps. I wanted to put this in the CodeBank
    perhaps a ADMIN can move this for me
    thanks
    Last edited by ChrisE; Mar 28th, 2020 at 07:02 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  2. #2
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: create Access Database with Code

    You can also use DAO if you want to add reference to some COM objects

  3. #3

    Thread Starter
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: create Access Database with Code

    Quote Originally Posted by kpmc View Post
    You can also use DAO if you want to add reference to some COM objects
    yep
    and probably the easiest way in creating .mdb's

    just thought .Net Users dont know DAO
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: create Access Database with Code

    Quote Originally Posted by ChrisE View Post
    yep
    and probably the easiest way in creating .mdb's

    just thought .Net Users dont know DAO
    Don't be making generalizations ... depends on their age... not all of us are one-trick ponies. Some of us know two tricks.
    Personally I think when it comes to DDL, DAO is the best/easiest to work with while ADO is better with DML... At least that's been my experience.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: create Access Database with Code

    I think with ADO you still need the Jet driver, not that that is a problem.

  6. #6

    Thread Starter
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: create Access Database with Code

    Quote Originally Posted by techgnome View Post
    Don't be making generalizations ... depends on their age... not all of us are one-trick ponies. Some of us know two tricks.
    Personally I think when it comes to DDL, DAO is the best/easiest to work with while ADO is better with DML... At least that's been my experience.

    -tg
    I'm pretty sure you know >5 Trick's any more
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width