Solved

MS Access hanging up on linked table field...

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

830 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