Runtime - problem deleting a record

I have an .accdb front end that used to have an .accdb back end.  Now it has an SQl back end.  When I go to delete a record I get the following error message: "The Microsoft Office database engine stopped the process because you and another user are attempting to change the same data at the same time."

Well, this can't be.  The program is in development.  I AM the only user.

Also, when I create a new record I am getting a similar message asking if I want to copy my changes to the clip board or drop the changes.  The Save Record button is grayed out on that error message.

This is proplexing.
AviationAceSole-ProprietorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

clarkscottCommented:
Sounds like you have another form (or parent or child form) opened using the same records.
Make sure you're not using the record (or part of it in an inner join in a recordset) somewhere else 'active'.

Scott c
0
AviationAceSole-ProprietorAuthor Commented:
I think I may need to run a stored procedure on the SQL server to set permissions.  How would I code an SQL statement to run a stored procedure.  Docmd.RunSQL doesn't work for something like:
EXEC [YOURDB].[dbo].[DBA_LM_SetTableViewPermissions] 'TEST_VIEW'
0
AviationAceSole-ProprietorAuthor Commented:
I talked to the adminstration for the server.  He says the user I am using is connecting more than once.  I suppose that would cause the issue I am having.  Question: How would I disconnect a user (or session) from the SQL DB?  I have 11 tables from the SQL DB linked to my Access project.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

AviationAceSole-ProprietorAuthor Commented:
I made a new .accdb file.  (a blank one)  The only thing I put in it is one table this is linked to the SQL server.  I wanted to isolate this table.  I brought the table up in a datasheet view.  I got the same error message when I tried to add and update a new record.  Then I made a simple form that only had 4 of the fields from the table displayed.  Guess what.... no errors when I edited a new record.  Really.... what's going on here?  There must be something about the structure of this table that is offensive to the SQL server.
0
AviationAceSole-ProprietorAuthor Commented:
I started adding more fields to the form I mentioned in my previous post.  At some point (I forget how many fields) I got the same error as before.  It looks like there is going to be a limitation to how many fields that can be updated at one time.  Any insights out there?
0
AviationAceSole-ProprietorAuthor Commented:
The next thing I am trying is to assign a default value to each field in the record.  I believe that once a new record is saved , it can be updated with no problem later. (as long as all the fields have an initial value)
 
ALTER TABLE [header] ALTER COLUMN [control_no] SET DEFAULT '   '
This SQL statment has a problem.  I am trying to figure that out now.
0
AviationAceSole-ProprietorAuthor Commented:
OK.  It looks like setting default values for all the fields will be too problematic.  Fundamentaly, I don't get why the record locks up.  I will continue to investigate.
0
AviationAceSole-ProprietorAuthor Commented:
Problem solved.  I used the Microsoft SQL Server Migration Assistant for Access.  I'm not sure everyhting it did when it uploaded the tables to the SQL server, but it works!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.