Skip to content

Instantly share code, notes, and snippets.

@zippy1981

zippy1981/Create-Mdb.ps1

Created August 23, 2011 21:45
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save zippy1981/1166670 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save zippy1981/1166670 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. zippy1981 revised this gist Aug 25, 2011. 1 changed file with 36 additions and 37 deletions.
    73 changes: 36 additions & 37 deletions Create-Mdb.ps1
    View file
    Original file line number Diff line number Diff line change
    @@ -13,32 +13,32 @@
    #TODO: Get The PInvoke calls to actually use these ENUMS.
    Add-Type -TypeDefinition @"
    namespace PInvoke {
    public enum ODBC_Constants {
    ODBC_ADD_DSN = 1,
    ODBC_CONFIG_DSN,
    ODBC_REMOVE_DSN,
    ODBC_ADD_SYS_DSN,
    ODBC_CONFIG_SYS_DSN,
    ODBC_REMOVE_SYS_DSN,
    ODBC_REMOVE_DEFAULT_DSN,
    };
    public enum ODBC_Constants {
    ODBC_ADD_DSN = 1,
    ODBC_CONFIG_DSN,
    ODBC_REMOVE_DSN,
    ODBC_ADD_SYS_DSN,
    ODBC_CONFIG_SYS_DSN,
    ODBC_REMOVE_SYS_DSN,
    ODBC_REMOVE_DEFAULT_DSN,
    };
    public enum SQL_RETURN_CODE {
    SQL_ERROR = -1,
    SQL_INVALID_HANDLE = -2,
    SQL_SUCCESS = 0,
    SQL_SUCCESS_WITH_INFO = 1,
    SQL_STILL_EXECUTING = 2,
    SQL_NEED_DATA = 99,
    SQL_NO_DATA = 100
    }
    public enum SQL_RETURN_CODE {
    SQL_ERROR = -1,
    SQL_INVALID_HANDLE = -2,
    SQL_SUCCESS = 0,
    SQL_SUCCESS_WITH_INFO = 1,
    SQL_STILL_EXECUTING = 2,
    SQL_NEED_DATA = 99,
    SQL_NO_DATA = 100
    }
    }
    "@;

    $signature = @'
    [DllImport("ODBCCP32.DLL",CharSet=CharSet.Unicode, SetLastError=true)]
    public static extern int SQLConfigDataSource
    (int hwndParent, int fRequest, string lpszDriver, string lpszAttributes);
    (int hwndParent, int fRequest, string lpszDriver, string lpszAttributes);
    [DllImport("odbccp32", CharSet=CharSet.Auto)]
    public static extern int SQLInstallerError(int iError, ref int pfErrorCode, StringBuilder lpszErrorMsg, int cbErrorMsgMax, ref int pcbErrorMsg);
    @@ -54,30 +54,29 @@ Function Create-MDB ([string] $fileName, [switch] $DeleteIfExists = $false) {
    }
    [string] $attrs = [string]::Format("CREATE_DB=`"{0}`" General`0", $fileName);
    # For 32 bit processes we use the older ODBCJT32.dll driver
    [string] $driver = 'Microsoft Access Driver (*.mdb)';
    # There is no 64 bit version of this driver so we use ACEODBC.dll
    # This requires the Microsoft Access Database Engine 2010 Redistributable
    # http://www.microsoft.com/download/en/details.aspx?id=13255
    if ([IntPtr]::Size -eq 8) {
    $driver = 'Microsoft Access Driver (*.mdb, *.accdb)';
    }
    # TODO: Interogate the registry (HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI) to search for which driver to use.
    # TODO: If no driver found use the 64 bit ones
    [string] $driver = 'Microsoft Access Driver (*.mdb)';
    # There is no 64 bit version of this driver so we use ACEODBC.dll
    # This requires the Microsoft Access Database Engine 2010 Redistributable
    # http://www.microsoft.com/download/en/details.aspx?id=13255
    if ([IntPtr]::Size -eq 8) {
    $driver = 'Microsoft Access Driver (*.mdb, *.accdb)';
    }
    # TODO: Interogate the registry (HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI) to search for which driver to use.
    # TODO: If no driver found use the 64 bit ones
    [int] $retCode = [PInvoke.Win32Utils]::SQLConfigDataSource(
    0, [PInvoke.ODBC_Constants]::ODBC_ADD_DSN,
    $driver, $attrs);
    if ($retCode -eq 0) {
    [int] $errorCode = 0 ;
    [int] $resizeErrorMesg = 0 ;
    $sbError = New-Object System.Text.StringBuilder(512);
    [int] $errorCode = 0 ;
    [int] $resizeErrorMesg = 0 ;
    $sbError = New-Object System.Text.StringBuilder(512);
    [PInvoke.Win32Utils]::SQLInstallerError(1, [ref] $errorCode, $sbError, $sbError.MaxCapacity, [ref] $resizeErrorMesg);
    if ($sbError.ToString() -eq 'Component not found in the registry') {
    $sbError = New-Object System.Text.StringBuilder(512);
    if ([intptr]::Size -eq 8) { $sbError.Append("Their appears to be no 64 bit MS Access Driver installed. Please install a 64 bit access driver or run this script from a 32 bit powershell instance"); }
    if ([intptr]::Size -eq 4) { $sbError.Append("Their appears to be no 32 bit MS Access Driver installed. Please install a 32 bit access driver or run this script from a 64 bit powershell instance"); }
    }

    throw New-Object ApplicationException([string]::Format("Cannot create file: {0}. Error: {1}", $fileName, $sbError));
    $sbError = New-Object System.Text.StringBuilder(512);
    if ([intptr]::Size -eq 8) { $sbError.Append("Their appears to be no 64 bit MS Access Driver installed. Please install a 64 bit access driver or run this script from a 32 bit powershell instance"); }
    if ([intptr]::Size -eq 4) { $sbError.Append("Their appears to be no 32 bit MS Access Driver installed. Please install a 32 bit access driver or run this script from a 64 bit powershell instance"); }
    }
    throw New-Object ApplicationException([string]::Format("Cannot create file: {0}. Error: {1}", $fileName, $sbError));
    }
    }

  2. zippy1981 revised this gist Aug 24, 2011. 1 changed file with 12 additions and 0 deletions.
    12 changes: 12 additions & 0 deletions Create-Mdb.ps1
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,15 @@
    # Copyright (c) 2011 Justin Dearing <[email protected]>
    #
    # Permission is hereby granted, free of charge, to any person obtaining a copy
    # of this software and associated documentation files (the "Software"), to deal
    # in the Software without restriction, including without limitation the rights
    # to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
    # copies of the Software, and to permit persons to whom the Software is
    # furnished to do so, subject to the following conditions:

    # The above copyright notice and this permission notice shall be included in
    # all copies or substantial portions of the Software.

    #TODO: Get The PInvoke calls to actually use these ENUMS.
    Add-Type -TypeDefinition @"
    namespace PInvoke {
  3. zippy1981 revised this gist Aug 24, 2011. 1 changed file with 14 additions and 4 deletions.
    18 changes: 14 additions & 4 deletions Create-Mdb.ps1
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    #TODO: Get The PInvoke calls to actually use these enums.
    #TODO: Get The PInvoke calls to actually use these ENUMS.
    Add-Type -TypeDefinition @"
    namespace PInvoke {
    public enum ODBC_Constants {
    @@ -35,15 +35,25 @@ public static extern int SQLInstallerError(int iError, ref int pfErrorCode, Stri
    Add-Type -MemberDefinition $signature -Name Win32Utils -Namespace PInvoke -Using PInvoke,System.Text;

    Function Create-MDB ([string] $fileName, [switch] $DeleteIfExists = $false) {
    # We need to pass the full path of the file to SQLConfigDataSource()
    # We need to pass the full path of the file to SQLConfigDataSource(). Relative paths will fail.
    $fileName = [System.IO.Path]::GetFullPath($fileName);
    if ($DeleteIfExists -and (Test-Path $fileName)) {
    Remove-Item $fileName;
    }
    [string] $attrs = [string]::Format("CREATE_DB=`"{0}`" General`0", $fileName);
    # For 32 bit processes we use the older ODBCJT32.dll driver
    [string] $driver = 'Microsoft Access Driver (*.mdb)';
    # There is no 64 bit version of this driver so we use ACEODBC.dll
    # This requires the Microsoft Access Database Engine 2010 Redistributable
    # http://www.microsoft.com/download/en/details.aspx?id=13255
    if ([IntPtr]::Size -eq 8) {
    $driver = 'Microsoft Access Driver (*.mdb, *.accdb)';
    }
    # TODO: Interogate the registry (HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI) to search for which driver to use.
    # TODO: If no driver found use the 64 bit ones
    [int] $retCode = [PInvoke.Win32Utils]::SQLConfigDataSource(
    0, [PInvoke.ODBC_Constants]::ODBC_ADD_DSN,
    "Microsoft Access Driver (*.mdb)", $attrs);
    $driver, $attrs);
    if ($retCode -eq 0) {
    [int] $errorCode = 0 ;
    [int] $resizeErrorMesg = 0 ;
    @@ -60,4 +70,4 @@ Function Create-MDB ([string] $fileName, [switch] $DeleteIfExists = $false) {
    }


    Create-Mdb "c:\foo.mdb" -DeleteIfExists
    Create-Mdb ".\foo.mdb" -DeleteIfExists
  4. zippy1981 revised this gist Aug 23, 2011. 1 changed file with 8 additions and 6 deletions.
    14 changes: 8 additions & 6 deletions Create-Mdb.ps1
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,5 @@
    Add-Type @"
    #TODO: Get The PInvoke calls to actually use these enums.
    Add-Type -TypeDefinition @"
    namespace PInvoke {
    public enum ODBC_Constants {
    ODBC_ADD_DSN = 1,
    @@ -34,20 +35,21 @@ public static extern int SQLInstallerError(int iError, ref int pfErrorCode, Stri
    Add-Type -MemberDefinition $signature -Name Win32Utils -Namespace PInvoke -Using PInvoke,System.Text;

    Function Create-MDB ([string] $fileName, [switch] $DeleteIfExists = $false) {
    [int] $retCode;
    # We need to pass the full path of the file to SQLConfigDataSource()
    $fileName = [System.IO.Path]::GetFullPath($fileName);
    if ($DeleteIfExists -and (Test-Path $fileName)) {
    Remove-Item $fileName;
    }
    [string] $attrs = [string]::Format("CREATE_DB=`"{0}`" General`0", $fileName);
    $retCode = [PInvoke.Win32Utils]::SQLConfigDataSource(
    [int] $retCode = [PInvoke.Win32Utils]::SQLConfigDataSource(
    0, [PInvoke.ODBC_Constants]::ODBC_ADD_DSN,
    "Microsoft Access Driver (*.mdb)", $attrs);
    if ($retCode -eq 0) {
    [int] $errorCode = 0 ;
    [int] $resizeErrorMesg = 0 ;
    $sbError = New-Object System.Text.StringBuilder(512);
    $sqlError = [PInvoke.Win32Utils]::SQLInstallerError(1, [ref] $errorCode, $sbError, $sbError.MaxCapacity, [ref] $resizeErrorMesg);
    if ($sbError.ToString() -eq 'Component not found in the registry') {
    [PInvoke.Win32Utils]::SQLInstallerError(1, [ref] $errorCode, $sbError, $sbError.MaxCapacity, [ref] $resizeErrorMesg);
    if ($sbError.ToString() -eq 'Component not found in the registry') {
    $sbError = New-Object System.Text.StringBuilder(512);
    if ([intptr]::Size -eq 8) { $sbError.Append("Their appears to be no 64 bit MS Access Driver installed. Please install a 64 bit access driver or run this script from a 32 bit powershell instance"); }
    if ([intptr]::Size -eq 4) { $sbError.Append("Their appears to be no 32 bit MS Access Driver installed. Please install a 32 bit access driver or run this script from a 64 bit powershell instance"); }
    @@ -58,4 +60,4 @@ Function Create-MDB ([string] $fileName, [switch] $DeleteIfExists = $false) {
    }


    Create-Mdb "c:\foo.mdb" -DeleteIfExists
    Create-Mdb "c:\foo.mdb" -DeleteIfExists
  5. zippy1981 revised this gist Aug 23, 2011. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions Create-Mdb.ps1
    View file
    Original file line number Diff line number Diff line change
    @@ -33,10 +33,10 @@ public static extern int SQLInstallerError(int iError, ref int pfErrorCode, Stri

    Add-Type -MemberDefinition $signature -Name Win32Utils -Namespace PInvoke -Using PInvoke,System.Text;

    Function Create-MDB ([string] $fileName, [bool] $deleteIfExists = $false) {
    Function Create-MDB ([string] $fileName, [switch] $DeleteIfExists = $false) {
    [int] $retCode;
    if ($deleteIfExists -and [File]::Exists($fileName)) {
    [File]::Delete($fileName);
    if ($DeleteIfExists -and (Test-Path $fileName)) {
    Remove-Item $fileName;
    }
    [string] $attrs = [string]::Format("CREATE_DB=`"{0}`" General`0", $fileName);
    $retCode = [PInvoke.Win32Utils]::SQLConfigDataSource(
    @@ -58,4 +58,4 @@ Function Create-MDB ([string] $fileName, [bool] $deleteIfExists = $false) {
    }


    Create-Mdb ".\foo.mdb"
    Create-Mdb "c:\foo.mdb" -DeleteIfExists
  6. zippy1981 created this gist Aug 23, 2011.
    61 changes: 61 additions & 0 deletions Create-Mdb.ps1
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,61 @@
    Add-Type @"
    namespace PInvoke {
    public enum ODBC_Constants {
    ODBC_ADD_DSN = 1,
    ODBC_CONFIG_DSN,
    ODBC_REMOVE_DSN,
    ODBC_ADD_SYS_DSN,
    ODBC_CONFIG_SYS_DSN,
    ODBC_REMOVE_SYS_DSN,
    ODBC_REMOVE_DEFAULT_DSN,
    };
    public enum SQL_RETURN_CODE {
    SQL_ERROR = -1,
    SQL_INVALID_HANDLE = -2,
    SQL_SUCCESS = 0,
    SQL_SUCCESS_WITH_INFO = 1,
    SQL_STILL_EXECUTING = 2,
    SQL_NEED_DATA = 99,
    SQL_NO_DATA = 100
    }
    }
    "@;

    $signature = @'
    [DllImport("ODBCCP32.DLL",CharSet=CharSet.Unicode, SetLastError=true)]
    public static extern int SQLConfigDataSource
    (int hwndParent, int fRequest, string lpszDriver, string lpszAttributes);
    [DllImport("odbccp32", CharSet=CharSet.Auto)]
    public static extern int SQLInstallerError(int iError, ref int pfErrorCode, StringBuilder lpszErrorMsg, int cbErrorMsgMax, ref int pcbErrorMsg);
    '@;

    Add-Type -MemberDefinition $signature -Name Win32Utils -Namespace PInvoke -Using PInvoke,System.Text;

    Function Create-MDB ([string] $fileName, [bool] $deleteIfExists = $false) {
    [int] $retCode;
    if ($deleteIfExists -and [File]::Exists($fileName)) {
    [File]::Delete($fileName);
    }
    [string] $attrs = [string]::Format("CREATE_DB=`"{0}`" General`0", $fileName);
    $retCode = [PInvoke.Win32Utils]::SQLConfigDataSource(
    0, [PInvoke.ODBC_Constants]::ODBC_ADD_DSN,
    "Microsoft Access Driver (*.mdb)", $attrs);
    if ($retCode -eq 0) {
    [int] $errorCode = 0 ;
    [int] $resizeErrorMesg = 0 ;
    $sbError = New-Object System.Text.StringBuilder(512);
    $sqlError = [PInvoke.Win32Utils]::SQLInstallerError(1, [ref] $errorCode, $sbError, $sbError.MaxCapacity, [ref] $resizeErrorMesg);
    if ($sbError.ToString() -eq 'Component not found in the registry') {
    $sbError = New-Object System.Text.StringBuilder(512);
    if ([intptr]::Size -eq 8) { $sbError.Append("Their appears to be no 64 bit MS Access Driver installed. Please install a 64 bit access driver or run this script from a 32 bit powershell instance"); }
    if ([intptr]::Size -eq 4) { $sbError.Append("Their appears to be no 32 bit MS Access Driver installed. Please install a 32 bit access driver or run this script from a 64 bit powershell instance"); }
    }

    throw New-Object ApplicationException([string]::Format("Cannot create file: {0}. Error: {1}", $fileName, $sbError));
    }
    }


    Create-Mdb ".\foo.mdb"