Missing data in first record in MS Access (front end) and using SQL Server (back end)

Question

I have a database that I just converted the back end to SQL Server using SSMA. I left the front end in MS Access. I only converted the tables and not the queries. It already had some data in it and that moved over just fine.

All was going well until just recently. On opening the database and loading the main form Event Interest it started having problems with the first record of the subform, called Names. The first field in the first record has data sometimes and not others. This is a text field. When data is in the field it puts in random numbers. I believe they may be related to SQL somehow. When there is no data/missing you can select the field and hit the backspace button and the data will appear minus the one character you just errased. I have no idea what is going on.

Any help you can supply I would greatly appreciate it. Thank you in advance.

I am new to SQL Server and I have used older versions of MS Access for a few years.

Answer 1

I am not certain what the problem might be, but these are some considerations that come to mind:

1. try deleting and recreating your linked tables. Perhaps an update to the table structure (or view, if you're linked to a view) has invalidated some of the metadata stored in the table link in your front end.

2. does your table have a primary key? If not, you really need one. There really is no such thing as a properly-designed data table in a relational database that is PK-less.

3. does your table have a timestamp? If not, add one, as this helps Access keep track of whether or not the data has changed on the server.

However, let me add that none of these issues manifest themselves exactly with the symptoms you've described, so they may not help.