-
Jan 30th, 2019, 01:42 PM
#1
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
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.
-
Jan 30th, 2019, 02:10 PM
#2
Re: create Access Database with Code
You can also use DAO if you want to add reference to some COM objects
-
Jan 30th, 2019, 02:17 PM
#3
Re: create Access Database with Code
Originally Posted by kpmc
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.
-
Jan 30th, 2019, 02:29 PM
#4
Re: create Access Database with Code
Originally Posted by ChrisE
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
-
Jan 30th, 2019, 02:33 PM
#5
Re: create Access Database with Code
I think with ADO you still need the Jet driver, not that that is a problem.
-
Jan 30th, 2019, 02:59 PM
#6
Re: create Access Database with Code
Originally Posted by techgnome
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|