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.
6 2773
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?
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.
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
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:
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
| |