473,326 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Access from SharePoint; Type Conversion Failure with Dates

isben22
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
6 2773
JConsulting
603 Expert 512MB
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
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
603 Expert 512MB
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
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
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
603 Expert 512MB
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

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

Similar topics

17
by: chicha | last post by:
Hey people, I have to convert MS Access 2000 database into mysql database, the whole thing being part of this project I'm doing for one of my faculty classes. My professor somehow presumed I...
2
by: Giulio | last post by:
I make an import of a huge file txt table with headers at each page and date format as "20.12.2004" in order to use the data I first qo a query that exclude all line representing the headers...
52
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server...
1
by: Bob Alston | last post by:
Anyone know of any features of the new Access 2007 that will improve on the ability of Access to work in a web environment??? Bob
5
by: Anns via AccessMonster.com | last post by:
My establishment has about 20 ms access db's that will be converted over (see subject). When we pull all the BE's over to SQL and the FE's on Sharepoint (.net) surely we don't have to change...
1
by: Alan | last post by:
OK this is a weird one. I've got an import routine going whereby name and address data is pulled into a table from a csv file. I'm having strange results when importing postcode/zip data into...
1
by: getobject | last post by:
I am building an ASP.Net site and wish to use a SharePoint to hold the information. I am trying to add an entry to a list using Sharepoint webservices and keep getting a SOAP exception. I have know...
8
by: Smithers | last post by:
Are there any important differences between the following two ways to convert to a type?... where 'important differences' means something more profound than a simple syntax preference of the...
1
by: doublestack | last post by:
I have a html file with javascript and am loading information into it with a xml file stored within sharepoint. I am having problems getting the dates to appear correctly, specifically, start date...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.