?
Solved

MS Access hanging up on linked table field...

Posted on 2009-03-31
4
Medium Priority
?
718 Views
Last Modified: 2013-11-27
I'm using an mdb MS Access database with linked SQL 2000 tables and views. For some reason, one of my data entry forms hangs up when a certain field becomes active (in this case the field is named ScheduledDate). It only hangs up on this field, the other fields work okay. I tried relinking the backend data tables/views, even deleting the links and re-linking, and still the same problem. The field type is datetime and it allows nulls. There are other datetime fields in the same table that don't hang up, though.

I went into the linked table itself through Access, and it hung up when I cursored over this ScheduledDate field. When I go into the table via the backend itself (through SQL Enterprise Manager), no hang ups in the view and it appears to be okay (the table link is to an SQL backend "view").

Anyways, to make my long story short - have any of you had problems like this where MS Access would hang up over a single linked table field?
0
Comment
Question by:Bill543
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 31

Accepted Solution

by:
hnasr earned 375 total points
ID: 24040055
Prblem happens with one DateTime Field.
Problem happens even with access only linked table.

Copy a new table, structure only, and link it to access.
Select few records at a time from old table, paste into new table and check.
Access used to stop working using ODBC  with Oracle tables taht have wrong Date fields format.  The data was imported to oracle table from a txt file.
0
 
LVL 14

Assisted Solution

by:pteranodon72
pteranodon72 earned 375 total points
ID: 24040279
Can you alter the view in SQL Server? Try adding a timestamp field (even if you don't use it). Delete the link in Access and then recreate the link.  See if that works better.

HTH,

pT
0
 

Author Comment

by:Bill543
ID: 24042269
Thanks for your inputs. I tried importing the backend table into a new table and re-linking, but same problem. For now I went ahead and disabled that field in the MS Access forms and queries. I'll check into changing the formatting of the field. I'm using a SQL Server version 2000 backend, so I think there's only one date field format (datetime).

-Bill
0
 

Author Closing Comment

by:Bill543
ID: 31565057
Thanks for your comments! I fixed the hang up problem by changing the date field type in the SQL 2000 table from "datetime" to "smalldatetime."
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question