?
Solved

MS Access hanging up on linked table field...

Posted on 2009-03-31
4
Medium Priority
?
723 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 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

807 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