Solved

MS Access hanging up on linked table field...

Posted on 2009-03-31
4
695 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

948 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

22 Experts available now in Live!

Get 1:1 Help Now