Using Powershell to Update Access DB field that allows multi

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 4 months ago.

  • Author
    Posts
  • #5961

    by graybin at 2012-11-21 05:11:46

    I am trying to use PowerShell to update an Access DB that has a field that allows multiple values. I have code to update a field that only allows single values, but when I try to use that to update this other field, it fails with the following error:
    $objRecordSet.Fields.Item("member").Value = "Some Test"
    Exception setting "Value": "Cannot perform this operation."
    At line:1 char:37
    + $objRecordSet.Fields.Item("member"). < <<< Value = "Some Test"
    + CategoryInfo : InvalidOperation: (Smile | :) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

    If I try to get the data in a field that allows multiple values, it always appears as if there is no data. As shown below:

    $objRecordSet.Fields.Item("member")

    Properties : System.__ComObject
    ActualSize : 4
    Attributes : 230
    DefinedSize : 536870910
    Name : member
    Type : 203
    Value :
    Precision : 255
    NumericScale : 255
    OriginalValue :
    UnderlyingValue :
    DataFormat :
    Status : 0

    $objRecordSet.Fields.Item("member").Value
    Returns nothing

    $objRecordSet.Fields.Item("member").Value | gm
    TypeName: System.String

    Name MemberType Definition
    ---- ---------- ----------
    Clone Method System.Object Clone()
    CompareTo Method int CompareTo(System.Object value), int CompareTo(string strB)
    Contains Method bool Contains(string value)
    CopyTo Method System.Void CopyTo(int sourceIndex, char[] destination, int destinationIndex,...
    EndsWith Method bool EndsWith(string value), bool EndsWith(string value, System.StringCompari...
    Equals Method bool Equals(System.Object obj), bool Equals(string value), bool Equals(string...
    GetEnumerator Method System.CharEnumerator GetEnumerator()
    GetHashCode Method int GetHashCode()
    GetType Method type GetType()
    GetTypeCode Method System.TypeCode GetTypeCode()
    IndexOf Method int IndexOf(char value), int IndexOf(char value, int startIndex), int IndexOf...
    IndexOfAny Method int IndexOfAny(char[] anyOf), int IndexOfAny(char[] anyOf, int startIndex), i...
    Insert Method string Insert(int startIndex, string value)
    IsNormalized Method bool IsNormalized(), bool IsNormalized(System.Text.NormalizationForm normaliz...
    LastIndexOf Method int LastIndexOf(char value), int LastIndexOf(char value, int startIndex), int...
    LastIndexOfAny Method int LastIndexOfAny(char[] anyOf), int LastIndexOfAny(char[] anyOf, int startI...
    Normalize Method string Normalize(), string Normalize(System.Text.NormalizationForm normalizat...
    PadLeft Method string PadLeft(int totalWidth), string PadLeft(int totalWidth, char paddingChar)
    PadRight Method string PadRight(int totalWidth), string PadRight(int totalWidth, char padding...
    Remove Method string Remove(int startIndex, int count), string Remove(int startIndex)
    Replace Method string Replace(char oldChar, char newChar), string Replace(string oldValue, s...
    Split Method string[] Split(Params char[] separator), string[] Split(char[] separator, int...
    StartsWith Method bool StartsWith(string value), bool StartsWith(string value, System.StringCom...
    Substring Method string Substring(int startIndex), string Substring(int startIndex, int length)
    ToCharArray Method char[] ToCharArray(), char[] ToCharArray(int startIndex, int length)
    ToLower Method string ToLower(), string ToLower(System.Globalization.CultureInfo culture)
    ToLowerInvariant Method string ToLowerInvariant()
    ToString Method string ToString(), string ToString(System.IFormatProvider provider)
    ToUpper Method string ToUpper(), string ToUpper(System.Globalization.CultureInfo culture)
    ToUpperInvariant Method string ToUpperInvariant()
    Trim Method string Trim(Params char[] trimChars), string Trim()
    TrimEnd Method string TrimEnd(Params char[] trimChars)
    TrimStart Method string TrimStart(Params char[] trimChars)
    Chars ParameterizedProperty char Chars(int index) {get;}
    Length Property System.Int32 Length {get;}

    Is there a way to fix this, or do I just have to wait until PowerShell gets more powerful in regards to Access?

    by DonJ at 2012-11-21 13:51:54

    Well... PowerShell isn't going to get more powerful in regards to Access, as currently PowerShell has zero Access capabilities built in . You're either using .NET Framework classes or the very-old ADO COM interfaces, which PowerShell can touch, but they're really external technologies. And, if you're using ADO (the old COM stuff), it gets further adapted by .NET Interop, so there's plenty of room for something to go wonky.

    I wasn't even aware that Access fields *could* do multiple values. Not familiar enough with Access, I guess.

    I'd say the problem is in whatever interface you're using to touch Access (ADO? ADO.NET?) not understanding multi-valued fields. Can't say whether or not that'll ever get better... Microsoft's developer division definitely has a love-hate-hate-meh relationship with Access, so I wouldn't hold my breath for too long.

    I hate to just pass the buck, but it might be worth posting at StackOverflow.com. Again, I don't think you've got a PowerShell issue per se – it's whatever PowerShell is using under the hood, which means it'd be the same answer for C# or VB.

You must be logged in to reply to this topic.