asked on Insert query not working with a linked sharepoint table with a multi-value field
access 2010 Insert query..
sharepoint linked table.
I have a sharepoint table that has fields that are Mutli-valued.
The value being stored is only one value.
Example:
Field name = Global
The choices are "Yes" or "No" only one value gets stored.
I'm trying to insert data from this sharepoint table into another access table.
It wont allow me to because the sharepoint table contains Fields that are mutli-valued datatypes ?
any way around this ??
sharepoint linked table.
I have a sharepoint table that has fields that are Mutli-valued.
The value being stored is only one value.
Example:
Field name = Global
The choices are "Yes" or "No" only one value gets stored.
I'm trying to insert data from this sharepoint table into another access table.
It wont allow me to because the sharepoint table contains Fields that are mutli-valued datatypes ?
any way around this ??
Microsoft Access
Last Comment
Fordraiders
Why is this a multi-value field if only a single value can be stored? Multi-value fields are used (but only by non-pros) to store multiple values. For example: FavoriteColor: Turquoise, Pink, Yellow if a person can't decide on just one.
ASKER
inherited this stuff...Pat. Cant give you a good reason.
ASKER
combo box fields and value list fields
I can't give you details because you'd have to put a gun to my head to make me use a multi-value field so I don't have practical experience. What I do know though is that the underlying relationship is a true 1-many and because of that, there is special SQL syntax that MS had to create for the sole purpose of working with MV fields and that is what you have to do to get values from a MV field. I don't have any links I can post but try Google and look for Multi-value field SQL. Here's one link. I didn't read it closely enough to know if it solves your problem.
https://support.office.com/en-us/article/Using-multivalued-fields-in-queries-6f64f92d-659f-411c-9503-b6624e1e323a
https://support.office.com/en-us/article/Using-multivalued-fields-in-queries-6f64f92d-659f-411c-9503-b6624e1e323a
ASKER
could i create a sql recordset in vba and then insert the data that way...or same issues?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Pat,
Weird but worked so far...
To Test
Created a new table "Table1" with the same data types but no "Lookups" or value lists.
Used this code to try and insert data on the "Combo box" , "Select statement" and "value list" fields from the sharepoint Linked table and this code below was inserting all the records so far...
Dim db As DAO.Database, RecCount As Long
'Get the total number of records in your import table to compare later
RecCount = DCount("*", "Dashboard")
RecCount = RecCount
Set db = CurrentDb
db.Execute "INSERT INTO TABLE1 ([Deal Trigger], [Deal Review Type], [Director Name]) " & _
"SELECT [Deal Trigger], [Deal Review Type], [Director Name] " & _
"FROM [Dashboard]", dbFailOnError
Weird but worked so far...
To Test
Created a new table "Table1" with the same data types but no "Lookups" or value lists.
Used this code to try and insert data on the "Combo box" , "Select statement" and "value list" fields from the sharepoint Linked table and this code below was inserting all the records so far...
Dim db As DAO.Database, RecCount As Long
'Get the total number of records in your import table to compare later
RecCount = DCount("*", "Dashboard")
RecCount = RecCount
Set db = CurrentDb
db.Execute "INSERT INTO TABLE1 ([Deal Trigger], [Deal Review Type], [Director Name]) " & _
"SELECT [Deal Trigger], [Deal Review Type], [Director Name] " & _
"FROM [Dashboard]", dbFailOnError
Why is it weird that the query works fine without the MVF?
ASKER
Well, i just tried all the fields and it failed...so gotta back track and find the field that is not working.
ASKER
Thanks