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:by DonJ at 2012-11-21 13:51:54$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 | gmTypeName: 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?
Well… PowerShell isn’t going to get more powerful in regards to Access, as currently PowerShell has zero Access capabilities built in <grin>. 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.