By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,935 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Access from SharePoint; Type Conversion Failure with Dates

isben22
P: 13
I am a beginning developer using ACCESS 03 and Share Point Portal 2003.

I have a list of events with start and stop dates in SharePoint that I want to pass to and Access Data Base.

Ultimatly I want to create an itinerary program that has an event and many subEvents as in a relational database. But I have not been able to find information to show me how to turn a SPS List into a relational table.

I have now linked to the SPS List, and then use a update macro to add the SPS List data to Access table so that I can make it relational.

When I do this, Access returns a "Type Conversion Failure" trying to copy the dates. The date fields are the only one that have these problems.

Can someone direct me to infomrmation that will either:

a. Help me to create a one to many relationships in SP Portal Srervices from SPS lists?
-or-
b. Lead me to a solution where by I can import data from a list regularly and append it to a table to be used on a rolling basis.

Any information or leads would be greatly apprciated.
May 6 '07 #1
Share this Question
Share on Google+
6 Replies


JConsulting
Expert 100+
P: 603
I am a beginning developer using ACCESS 03 and Share Point Portal 2003.

I have a list of events with start and stop dates in SharePoint that I want to pass to and Access Data Base.

Ultimatly I want to create an itinerary program that has an event and many subEvents as in a relational database. But I have not been able to find information to show me how to turn a SPS List into a relational table.

I have now linked to the SPS List, and then use a update macro to add the SPS List data to Access table so that I can make it relational.

When I do this, Access returns a "Type Conversion Failure" trying to copy the dates. The date fields are the only one that have these problems.

Can someone direct me to infomrmation that will either:

a. Help me to create a one to many relationships in SP Portal Srervices from SPS lists?
-or-
b. Lead me to a solution where by I can import data from a list regularly and append it to a table to be used on a rolling basis.

Any information or leads would be greatly apprciated.

Can you post the SQL for the update query that you're running?
May 6 '07 #2

isben22
P: 13
Can you post the SQL for the update query that you're running?
Absolutely!

INSERT INTO Mission ( ID, Edit, MissionName, MissionDateFrom, MissionDateTo, Notes, MissionPlanner, ItineraryID ) IN 'Z:\Operations File\DB\DailyCards_be.mdb' SELECT Missions.ID, Missions.Edit, Missions.MissionName, Mission.MissionDateFrom, Missions.MissionDateTo, Missions.Notes, Missions.MissionPlanner,Missions.ItineraryID FROM Missions;

This SQL is computer generated.

I have a link from my SPS List into the access DB (this gets the data there) then I use an update query to take the data (I use and SPS site to start the data chain) from this list to be used by access to develop an itinerary.

It will not transfer the dates from the SPS to the new table.

I also do not know if I can even make a SPS list relational. I so not find much literature on using SPS with Access specifically.

Thank you for looking at this for me.
May 7 '07 #3

JConsulting
Expert 100+
P: 603
Absolutely!

INSERT INTO Mission ( ID, Edit, MissionName, MissionDateFrom, MissionDateTo, Notes, MissionPlanner, ItineraryID ) IN 'Z:\Operations File\DB\DailyCards_be.mdb' SELECT Missions.ID, Missions.Edit, Missions.MissionName, Mission.MissionDateFrom, Missions.MissionDateTo, Missions.Notes, Missions.MissionPlanner,Missions.ItineraryID FROM Missions;

This SQL is computer generated.

I have a link from my SPS List into the access DB (this gets the data there) then I use an update query to take the data (I use and SPS site to start the data chain) from this list to be used by access to develop an itinerary.

It will not transfer the dates from the SPS to the new table.

I also do not know if I can even make a SPS list relational. I so not find much literature on using SPS with Access specifically.

Thank you for looking at this for me.

Can you give this a go?

INSERT INTO Mission
( ID, Edit, MissionName, MissionDateFrom, MissionDateTo, Notes, MissionPlanner, ItineraryID )
SELECT Missions.ID, Missions.Edit, Missions.MissionName, format(Mission.MissionDateFrom,"mm/dd/yyyy"), format(Missions.MissionDateTo,"mm/dd/yyyy"), Missions.Notes, Missions.MissionPlanner,Missions.ItineraryID FROM Missions;

I'm not sure how the "IN 'Z:\Operations File\DB\DailyCards_be.mdb" plays into your insert statement, but it's not valid syntax.

Let me know if the format works. Also..if you have a time with the date, we can modify the format for that.
J
May 7 '07 #4

isben22
P: 13
Can you give this a go?

INSERT INTO Mission
( ID, Edit, MissionName, MissionDateFrom, MissionDateTo, Notes, MissionPlanner, ItineraryID )
SELECT Missions.ID, Missions.Edit, Missions.MissionName, format(Mission.MissionDateFrom,"mm/dd/yyyy"), format(Missions.MissionDateTo,"mm/dd/yyyy"), Missions.Notes, Missions.MissionPlanner,Missions.ItineraryID FROM Missions;

I'm not sure how the "IN 'Z:\Operations File\DB\DailyCards_be.mdb" plays into your insert statement, but it's not valid syntax.

Let me know if the format works. Also..if you have a time with the date, we can modify the format for that.
J
Thanks a Million! I will try it first thing tomorrow! The 'Z:\Operations File\DB\DailyCards_be.mdb" is the location of the back end. I have a split database. However, if this works on a "uni-base" then I may be able to work the back end...

thank you so much:
May 7 '07 #5

isben22
P: 13
Thanks a Million! I will try it first thing tomorrow! The 'Z:\Operations File\DB\DailyCards_be.mdb" is the location of the back end. I have a split database. However, if this works on a "uni-base" then I may be able to work the back end...

thank you so much:

Well it worked, only not the way I had intended. I have learned a little more about the SQL I have been trying to avoid ;)

I am back at the drawing board. I am trying to use a Share Point List as a master table (Linked) and use Access Tables as subTables. For some reason you cannot make a one to many join to a SharePoints2 List which is why I was importing data to an access table. I will have to do more research in SPS2.

Thank you for all the help. I have copied the information you have sent and have found ways to utilize it in the future.

Again, thank you.
M
May 8 '07 #6

JConsulting
Expert 100+
P: 603
Well it worked, only not the way I had intended. I have learned a little more about the SQL I have been trying to avoid ;)

I am back at the drawing board. I am trying to use a Share Point List as a master table (Linked) and use Access Tables as subTables. For some reason you cannot make a one to many join to a SharePoints2 List which is why I was importing data to an access table. I will have to do more research in SPS2.

Thank you for all the help. I have copied the information you have sent and have found ways to utilize it in the future.

Again, thank you.
M
M,
Revelations aren't always for the good. Let us know if we can help in other ways. There's always 10 different ways to do things.
J
May 8 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.