locked
powershell/access/excel RRS feed

  • Question

  • Good evening,

    I have one detail of a new automation process that I need to verify:

    Using Powershell to import a large Access database into Excel via OLEDB ... do I just drop the excel part of the connection string (Extended Properties='Excel', etc) and point the path to the access database

    I'm trying to script a workaround for an autonumber error (maxlocksperfile) that I always get. So I'm trying to automate importing the access table into an excel table then importing it back into access. 

    Does that make sense?

    Thank you for you assistance. The information in this forum has helped me dramatically advance my career.

    Tuesday, August 22, 2017 12:36 AM

Answers

  • Here is the basic connection string for Access:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\scripts\database1.accdb;Persist Security Info=False

    And yes it is basically the same with no "Extended Proeprties".


    \_(ツ)_/



    • Edited by jrv Tuesday, August 22, 2017 1:06 AM
    • Marked as answer by Mikepellas Tuesday, August 22, 2017 1:59 AM
    Tuesday, August 22, 2017 12:48 AM
  • Change your connection to table lock.  Don't use record locks on large tables.


    \_(ツ)_/

    • Marked as answer by Mikepellas Tuesday, August 22, 2017 1:59 AM
    Tuesday, August 22, 2017 1:07 AM

All replies

  • Here is the basic connection string for Access:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\scripts\database1.accdb;Persist Security Info=False

    And yes it is basically the same with no "Extended Proeprties".


    \_(ツ)_/



    • Edited by jrv Tuesday, August 22, 2017 1:06 AM
    • Marked as answer by Mikepellas Tuesday, August 22, 2017 1:59 AM
    Tuesday, August 22, 2017 12:48 AM
  • Change your connection to table lock.  Don't use record locks on large tables.


    \_(ツ)_/

    • Marked as answer by Mikepellas Tuesday, August 22, 2017 1:59 AM
    Tuesday, August 22, 2017 1:07 AM