October 11, 2007 at 11:06 pm
I'm running Access 2007 through ODBC on Windows Server 2003 to a SQL Server 2000 back-end. I have setup linked tables for all the tables in my very simple database. Each table has a single split form (with "GUI" and spreadsheet views of the data) created to update it. Essentially I'm using Access as the front-end app for my database.
When I enter a new record in Access I click Access' "New" button, enter the records, then click Access' "Save" button (under the Records section on the Home tab of the ribbon). AS soon as I hit save, all fields in the form display "#Deleted". When I click "Refresh All" the record appears in full in the spreadsheet view of my form. What's happening?
October 11, 2007 at 11:25 pm
OK, I think it has something to do with the INT IDENTITY key I've placed on my SQL tables. For those tables whose primary key is an integer determined by SQL Server, the #Deleted values show. I have some tables which have composite keys, i.e. two foreign keys linked to form a primary key. When I create new records through this form, I don't get the #Deleted issue.
So I'm still stuck. Any ideas?
October 23, 2007 at 9:44 am
I think I'd try forcing a refresh on the sub forms through the After Update event. I'm not a hard-core Access programmer, so that's just a semi-educated guess.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 23, 2007 at 9:55 am
I have had that problem if the database schema has changed since the creation of the ODBC. You may try recreating the ODBC.
October 23, 2007 at 10:01 am
If your schema changed, you shouldn't have to recreate your ODBC connection, just refresh the links through Linked Table Manager.
The ODBC connection is just a path to the data, it doesn't store the data layout internally since it can connect to multiple tables or views.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 24, 2007 at 7:43 am
A similar problem happens using Access and MySQL. The fix in that situation is to add a date/time field with a timestamp value to the table being referenced. However, you may also verify the ODBC driver you are using and make sure it's up to date.
TJ
October 24, 2007 at 10:05 pm
In Access 97 and 2K there was a similar problem when Access had a link table and it could not figure out what was the Primary Key from SQL Server. So when a new record was created, Access had no way to validate the new record, therefore the #Deleted# messages.
The workaround was to specify the field(s) of the Primary Key at the time you link the table for the first time. Once Access knows that,everything should work.
Try it and let us know if this fixed the problem
Cheers,
Cesar
October 25, 2007 at 2:30 am
I had something similar in Access 2003. My solution was to add one of the following lines of code to my button:
Forms![myformname].refresh
or
Forms![myformname].requery
November 1, 2007 at 12:50 am
Hi All,
Thanks for your suggestions. I ended up putting a Requery command in the AfterInsert event. I didn't reference it to the form name (which caused all sorts of grief; I just couldn't get it right), but it works nonetheless.
Sam
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply