Powershell and MS-ACCESS (2013)

This topic contains 3 replies, has 2 voices, and was last updated by  Ramon Tan 3 weeks, 2 days ago.

  • Author
    Posts
  • #103268

    Ramon Tan
    Participant

    I am trying to convert an old Powershell script:

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")
    objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source = 'C:\Scripts\Test.mdb'"
    Set objRecordSet = objConnection.OpenSchema(adSchemaTables)
    Do Until objRecordset.EOF
    Wscript.Echo "Table name: " & objRecordset.Fields.Item("TABLE_NAME")
    Wscript.Echo "Table type: " & objRecordset.Fields.Item("TABLE_TYPE")
    Wscript.Echo
    objRecordset.MoveNext
    Loop
    My dilemma is not so much the syntax of the 'Set', or the 'Do Until ...', or the 'Wscript.Echo' statements. Rather, I am stucked with the object class references: these are references to ADO (ActiveX Data Objects).
    Would be grateful for any tips or hints to the right source(s) for updates.
    Many thanks.

  • #103276

    Rob Simmers
    Participant
    • #103280

      Ramon Tan
      Participant

      Many thanks Rob, much appreciated.

    • #103327

      Ramon Tan
      Participant

      Hello Rob,
      Following the website references you provided, I have tested the statements (I am running PS 5.1) —
      1 – $objConnection = New-Object -comobject ADODB.Connection
      2 – $objRecordset = New-Object -comobject ADODB.Recordset
      3 – $objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\XXC\Documents\MyAccessDB.mdb")

      I keep getting the error message: " Provider cannot be found. It may not be properly installed. "
      I ran each statement individually and #1 and #2 always run fine.
      It is always at #3 that I get the error. I must be missing something behind the scenes?

      I have ACCESS 2007 installed and running fine on my Win 7 (64-bit) PC for many years now.
      It was installed as part of Office 2007 Pro, which includes Word,Excel,PPoint,Outlook,Access & 1 or 2 other products which I don't use (Publisher). Would be grateful for any advice or suggestions.

You must be logged in to reply to this topic.