none
Task Scheduling PowerShell Script To Create Access Database RRS feed

  • Question

  • I have this script that I have stolen and modified from other answers here at TechNet:

    # Creates new blank Access database.
    Function Create-DataBase($Db){
     $application = New-Object -ComObject Access.Application
     $application.NewCurrentDataBase($Db,10)
     $application.CloseCurrentDataBase()
     $application.Quit()
    }
    Function Invoke-ADOCommand($Db, $Command){
     $connection = New-Object -ComObject ADODB.Connection
     $connection.Open("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=$Db")
     $connection.Execute($command)
     $connection.Close()
    }
    $Db = "C:\ScheduledCodeRun\CollatedData\Database.mdb"
    $table = "Table1"
    $Fields = "col1 Text, col2 Text, col3 Text"
    $command = "Create Table $table '($fields')"
    If(Test-Path $Db){
        Write-Host 'DB already exists' -fore green
    }else{
        Create-DataBase $db
        Invoke-ADOCommand $Db 'Create Table Table1(col1 Text, col2 Text, col3 Text)'
    }
    When I run the script in PowerShell ISE, it creates the new Access Database fine. However, when I run it in PowerShell command line, it doesn't.

    Can someone please tell me where I'm going wrong?









    Wednesday, August 13, 2014 4:55 AM

Answers

  • SQL statements require a ; to signal the end of the statement

    Invoke-ADOCommand $Db 'Create Table Table1(col1 Text, col2 Text, col3 Text)';

    No they don't.  That is only true in compound statements and in Oracle.

    Access works just fine without the ;.

    I just ran it again and it still works:

    Windows PowerShell
    Copyright (C) 2013 Microsoft Corporation. All rights reserved.
    
    PS C:\scripts> # Creates new blank Access database.
    PS C:\scripts> Function Create-DataBase($db){
    >>     $application = New-Object -ComObject Access.Application
    >>     $application.NewCurrentDataBase($db,10)
    >>     $application.CloseCurrentDataBase()
    >>     $application.Quit()
    >> }
    >> Function Invoke-ADOCommand($Db, $Command){
    >>     $connection = New-Object -ComObject ADODB.Connection
    >>     $connection.Open("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=$Db")
    >>     $connection.Execute($command)
    >>     $connection.Close()
    >> }
    >>
    PS C:\scripts> $dbname='C:\temp3\testdb2.mdb'
    PS C:\scripts>
    PS C:\scripts> If(Test-Path $dbname){
    >>     Write-Host 'DB already exists' -fore green
    >> }else{
    >>     Create-DataBase $dbname
    >>     Invoke-ADOCommand $dbname 'Create Table Table1(col1 Text, col2 Text, col3 Text)'
    >> }
    >>
    
    
    Properties       : System.__ComObject
    AbsolutePosition :
    ActiveConnection : System.__ComObject
    BOF              :
    Bookmark         :
    CacheSize        : 1
    CursorType       : 0
    EOF              :
    Fields           : System.__ComObject
    LockType         : 1
    MaxRecords       : 0
    RecordCount      :
    Source           : Create Table Table1(col1 Text, col2 Text, col3 Text)
    AbsolutePage     :
    EditMode         :
    Filter           : 0
    PageCount        :
    PageSize         : 10
    Sort             :
    Status           :
    State            : 0
    CursorLocation   : 2
    MarshalOptions   : 0
    DataSource       : System.__ComObject
    ActiveCommand    :
    StayInSync       : True
    DataMember       :
    Index            :
    
    PS C:\scripts>& $dbname
    

    The only issue may be with the installation of Office or with missing patches.  There is nothing wrong with the script.

    I have used PowerShell and Access since the first beta of both.  Semi-colons are not needed.


    ¯\_(ツ)_/¯


    • Marked as answer by Scott N Wilson Thursday, August 14, 2014 1:10 AM
    • Edited by jrv Thursday, August 14, 2014 1:22 AM
    Wednesday, August 13, 2014 9:40 PM

All replies

  • It works fine for me.  What is your error?

    ¯\_(ツ)_/¯

    Wednesday, August 13, 2014 9:42 AM
  • It works fine for me.  What is your error?

    ¯\_(ツ)_/¯


    When I copy the script into PowerShell, it runs down to the line:

        Invoke-ADOCommand $Db 'Create Table Trable1(col1 Text, col2 Text, col3 Text)'

    And then just sits there. If I press 'Enter', the script continues and the required output happens. I'm not even sure what to ask in a Google question to look up what is going wrong?!
    Wednesday, August 13, 2014 9:11 PM
  • SQL statements require a ; to signal the end of the statement

    Invoke-ADOCommand $Db 'Create Table Table1(col1 Text, col2 Text, col3 Text)';

    Wednesday, August 13, 2014 9:29 PM
  • SQL statements require a ; to signal the end of the statement

    Invoke-ADOCommand $Db 'Create Table Table1(col1 Text, col2 Text, col3 Text)';


    That hasn't worked either, sorry. If it helps, I need to press 'Enter' twice for it to complete the code? The first 'Enter' goes to a '>>' line, then the 2nd 'Enter' press executes the code to completion.
    Wednesday, August 13, 2014 9:35 PM
  • SQL statements require a ; to signal the end of the statement

    Invoke-ADOCommand $Db 'Create Table Table1(col1 Text, col2 Text, col3 Text)';

    No they don't.  That is only true in compound statements and in Oracle.

    Access works just fine without the ;.

    I just ran it again and it still works:

    Windows PowerShell
    Copyright (C) 2013 Microsoft Corporation. All rights reserved.
    
    PS C:\scripts> # Creates new blank Access database.
    PS C:\scripts> Function Create-DataBase($db){
    >>     $application = New-Object -ComObject Access.Application
    >>     $application.NewCurrentDataBase($db,10)
    >>     $application.CloseCurrentDataBase()
    >>     $application.Quit()
    >> }
    >> Function Invoke-ADOCommand($Db, $Command){
    >>     $connection = New-Object -ComObject ADODB.Connection
    >>     $connection.Open("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=$Db")
    >>     $connection.Execute($command)
    >>     $connection.Close()
    >> }
    >>
    PS C:\scripts> $dbname='C:\temp3\testdb2.mdb'
    PS C:\scripts>
    PS C:\scripts> If(Test-Path $dbname){
    >>     Write-Host 'DB already exists' -fore green
    >> }else{
    >>     Create-DataBase $dbname
    >>     Invoke-ADOCommand $dbname 'Create Table Table1(col1 Text, col2 Text, col3 Text)'
    >> }
    >>
    
    
    Properties       : System.__ComObject
    AbsolutePosition :
    ActiveConnection : System.__ComObject
    BOF              :
    Bookmark         :
    CacheSize        : 1
    CursorType       : 0
    EOF              :
    Fields           : System.__ComObject
    LockType         : 1
    MaxRecords       : 0
    RecordCount      :
    Source           : Create Table Table1(col1 Text, col2 Text, col3 Text)
    AbsolutePage     :
    EditMode         :
    Filter           : 0
    PageCount        :
    PageSize         : 10
    Sort             :
    Status           :
    State            : 0
    CursorLocation   : 2
    MarshalOptions   : 0
    DataSource       : System.__ComObject
    ActiveCommand    :
    StayInSync       : True
    DataMember       :
    Index            :
    
    PS C:\scripts>& $dbname
    

    The only issue may be with the installation of Office or with missing patches.  There is nothing wrong with the script.

    I have used PowerShell and Access since the first beta of both.  Semi-colons are not needed.


    ¯\_(ツ)_/¯


    • Marked as answer by Scott N Wilson Thursday, August 14, 2014 1:10 AM
    • Edited by jrv Thursday, August 14, 2014 1:22 AM
    Wednesday, August 13, 2014 9:40 PM
  • I was just going to edit it. The semi-colon should be inside the quote, it's part of the sql statement

    Invoke-ADOCommand $Db 'Create Table Table1(col1 Text, col2 Text, col3 Text);'

    Wednesday, August 13, 2014 9:40 PM
  • I was just going to edit it. The semi-colon should be inside the quote, it's part of the sql statement

    Invoke-ADOCommand $Db 'Create Table Table1(col1 Text, col2 Text, col3 Text);'

    Semi colons do not count in Access or ADO.

    The issue is crap characters in the script like "smart-quotes.

    Try using this version: http://1drv.ms/1rsyK9h

    It is tested and works on WIn7 and Win 8


    ¯\_(ツ)_/¯

    Wednesday, August 13, 2014 9:44 PM
  • Access SQL: basic concepts, vocabulary, and syntax

    ...

    Basic SQL clauses: SELECT, FROM, and WHERE
    A SQL statement takes the general form:

    SELECT field_1
    FROM table_1
    WHERE criterion_1
    ;
     NOTES 

    Access ignores line breaks in a SQL statement. However, consider using a line for each clause to help improve the readability of your SQL statements for yourself and others.

    Every SELECT statement ends with a semi-colon (;). The semi-colon can appear at the end of the last clause or on a line by itself at the end of the SQL statement.

    http://office.microsoft.com/en-ca/access-help/access-sql-basic-concepts-vocabulary-and-syntax-HA010256402.aspx


    Wednesday, August 13, 2014 10:12 PM
  • In Access Query editor this is true.  It I snot true when using OLEDB SQL.  MS Sql syntax in OLEDB and SQLServer does bot require a semicolon on a single SQL statement.

    We are using OLEDB and NOT MSAccess.  We can use MS relaxed syntax.


    ¯\_(ツ)_/¯

    Wednesday, August 13, 2014 11:44 PM
  • Brian - think of it this way.  If there was s syntax error the OLEDB command would have thrown an exception.

    Try this and note that only the last one causes an exception.

    Function Invoke-ADOCommand($Db, $Command){
        $connection = New-Object -ComObject ADODB.Connection
        $connection.Open("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=$Db")
        $connection.Execute($command)
        $connection.Close()
    }
    $dbname='C:\temp3\testdb.mdb'
    Invoke-ADOCommand $dbname 'Create Table Table1(col1 Text, col2 Text, col3 Text)'
    Invoke-ADOCommand $dbname 'Create Table Table1(col1 Text, col2 Text, col3 Text);'
    Invoke-ADOCommand $dbname 'Create Table Table1(col1 Text, col2 Text, col3 Text;'
    
    
    


    ¯\_(ツ)_/¯

    Wednesday, August 13, 2014 11:52 PM
  • It works fine for me.  What is your error?


    ¯\_(ツ)_/¯


    When I copy the script into PowerShell, it runs down to the line:

        Invoke-ADOCommand $Db 'Create Table Trable1(col1 Text, col2 Text, col3 Text)'

    And then just sits there. If I press 'Enter', the script continues and the required output happens. I'm not even sure what to ask in a Google question to look up what is going wrong?!

    Do you still have press enter for it to continue? 

    Thursday, August 14, 2014 4:07 AM
  • It works fine for me.  What is your error?


    ¯\_(ツ)_/¯


    When I copy the script into PowerShell, it runs down to the line:

        Invoke-ADOCommand $Db 'Create Table Trable1(col1 Text, col2 Text, col3 Text)'

    And then just sits there. If I press 'Enter', the script continues and the required output happens. I'm not even sure what to ask in a Google question to look up what is going wrong?!

    Do you still have press enter for it to continue? 


    I do. I think from further research that it is most likely an Access 2010 patch that hasn't been installed in our companies fleet. So, rather than pull teeth trying to get it approved and installed - I decided to make a blank database with the table and columns setup, and get my script to make a copy of that database to write to each run. It then renames the title of the database to include the current date.

    # Copies new blank database to useable folder.
    Copy-Item "C:\ScheduledCodeRun\BlankDatabase\Database.mdb" "C:\ScheduledCodeRun\CollatedData\"
    
    # Renames database to include todays date.
    $CurrentDate = Get-Date -format "yyyy.MM.dd"
    Rename-Item "C:\ScheduledCodeRun\CollatedData\Database.mdb" -NewName "Database.$CurrentDate.mdb"



    Thanks for your assistance anyway.
    Friday, August 15, 2014 3:25 AM
  • As I noted - nothing to do with semi-colons.

    I recommend first re-installing Office and running WIndows Update then try again.  Don't forget to download and install the latest version of the ACE drivers for Access and Office. 


    ¯\_(ツ)_/¯

    Friday, August 15, 2014 4:13 AM