Solved

MS Access hanging up on linked table field...

Posted on 2009-03-31
4
693 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
  • 2
4 Comments
 
LVL 30

Accepted Solution

by:
hnasr earned 125 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 125 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now