Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2007
    Posts
    29

    Unanswered: Sharepoint date Issues

    I exported several data tables to a Sharepoint Server. I then deleted that local table and replaced it with a linked version of that table.

    I am using mostly ADOBD connections and everything seems to work fine except the dates. I have tried every setting I can find and cannot stop various run time errors from occurring. Anyone have any ideas?

    Secondly is there a command which is the equivalent of running the linked table manager? I do not know how the table linking normally works but it does not "update" as often as I need it to.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How are you handling NULL date values?

    As for table linking - see the access code bank at the top of this topic
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2007
    Posts
    29
    I was handling NULL dates buy simply providing a specific date #7/1/2007# to be specific. In the end I ended up turning every date field into a text field. Rather sloppy way to do it but that was the only thing that I could make it work.

    On somewhat of a side note, I have several queries that run amazingly slow now thanks to this whole process. It looks like the Sharepoint connection treats every record like a separate connection or something. I have a few queries that take several minutes to run and I have only a few thousand total records.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by sattlerjm
    I ended up turning every date field into a text field
    NO, NO, NO, NO, NO!!

    Put it back and solve the issue properly. You're opening up a whole new can of worms by doing that!

    As for the speed issues; how are you accessing these records... ADO, DAO, or using queries?
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes - I FULLY agree with George! DON'T change your date fields to a text field!! You may not realize it now but when you try to run a query based on a specific date or date range, you're screwed (and need to find more work-arounds which can lead to more work-arounds, etc...etc...). Not to mention users entering non-date data into that field.
    Last edited by pkstormy; 01-15-08 at 17:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jul 2007
    Posts
    29
    I know that it is absolutely wrong to set the date to a text field and I would never tell anyone to do the same. Problem is I cannot find the correct solution and honestly I have no support other than online to turn to. If anyone has any idea on this I would love to hear it, I have searched high and low, given the amount of time I have to give this project.

    I am using mostly ADO and Queries. I have rewritten some of the queries to be more efficient and that has helped some. I am going to assume that alot of the slowdown has to do with the Sharepoint connection.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding refreshing the linked tables, I posted some code in the MSAccess code bank which creates/refreshes linked tables for an ODBC connection on linked SQL Server tables. I'm not sure if this might help but you may want to look at it. I believe there's also a post somewhere in this forum on how to issue a simple command to refresh linked tables. I'm not sure where it is but I'll see if I can find it.

    Regarding the date errors you're getting - have you checked to make sure all the values are legitimate date values. I've sometimes seen a date value get into a date field type with a value of 12/03 instead of 12/01/03. This caused some issues. Also remember that date fields are queried using the # (ex: Select * from MyTable where MyDateField = # & SomeDate & "#").
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jul 2007
    Posts
    29
    The dates worked flawlessly before when the same tables were local. Everything now results in the same error. That I am trying to put too much data into a field that is too small.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I once had an error similar to this when I used a Progress ODBC driver to connect to a Progress backend database. I updated with a new ODBC driver and things seemed to work better. I'm not sure if this is related to your problem but I'm "guessing" that it might have something to do with the kind of driver you're using to connect to the backend db.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •