Access Front End/SQL Backend

[oldozziesubby]

I've been looking through all the previous posts and it seems that I am doing something wrong. I have two databases, one front end that had all the forms, reports, modules etc. and a backend that had all the tables. I converted the backend tables to MS SQL 2000 and linked them to the front end by ODBC no problem. I am using DAO for a lot of updating. and a lot of drop down boxes. I have no problem adding new records to the various tables, however as soon as I start to try to modify a record I get a write conflict error and I'm the only one using it. What is interesting is that the first button, the 'Save Record' is greyed out and I cant use it. I'm pulling my hair out, because I can't figure out what I'm doing wrong or what I'm missing when I create the tables in SQL. So, I hope there is someone who has had the same issue (there are no problems, jut unresolved issues) and can point me in the right direction. Thank you

 

[boblarson]

Did you make sure that in the SQL tables that there is a primary key set for each table and that when you linked them in that you answered it's question about which field was the primary key?

 

[oldozziesubby]

Did you make sure that in the SQL tables that there is a primary key set for each table and that when you linked them in that you answered it's question about which field was the primary key?

Hi Bob, Thank you, yes I did, I made sure all the prime keys were in order and that all the id keys were created and relevant. But it still seemed to make no difference. I was wondering if it has to to with the set up of the relationships etc, but mabe I'm wrong

Peter

 

[boblarson]

It may be that because you have to set up the relationships in your backend and not in your front end and since you are dealing with SQL server, if you try to set relationships in your front end, especially if you try to use Referential Integrity, access may be choking there.

Also, just to note, in an MDB, you can't use action queries or parameter queries for certain actions when using SQL server. You need to change to Stored Procedures and then run those from Access.

 

[oldozziesubby]

It may be that because you have to set up the relationships in your backend and not in your front end and since you are dealing with SQL server, if you try to set relationships in your front end, especially if you try to use Referential Integrity, access may be choking there.

Also, just to note, in an MDB, you can't use action queries or parameter queries for certain actions when using SQL server. You need to change to Stored Procedures and then run those from Access.

Thanks Bob, Maybe that's the problem. I have a combo box which selects data from a table in sql to add to the record but when I try to move to the next record, that's when I get the problem.

Peter

 

[yoritomo]

Hi, I'm having a very similar error.

I can create new records, but cant modify existing ones. It throws up a write error and again I'm the only person in it (its on my desktop and is still in the design phase)

I have already created 3 databases that run off this SQl server and only ever got this error before on one of them (but very sporadically), and generally shutting it down and reopenning worked. One of the databases even works off the same schema so I know thats not the issue, also my account is a full administrator on the sql server.

I do have a few combo boxes setup on the form, but nothing any fancier than I have previously used, and infact if I go directly into the table and try to enter data in it still throws up the error, but its fine if I enter data using the sql management studio

The form itself is directly from the table, no queries

And if it makes any differance at all, when I create a new record the ID number it auto generates is the the previous ID + the amount of times i have got the write conflict error since I last created the previous id

 

[yoritomo]

Ah Ha!
I found the answer (unfortunatly not in a packet of smarties, although I shall go eat some incase they give me a better one) http://www.access-programmers.co.uk/forums/showthread.php?t=115886

 

[boblarson]

Thanks for posting back with that.

 

[oldozziesubby]

Ah Ha!
I found the answer (unfortunatly not in a packet of smarties, although I shall go eat some incase they give me a better one) http://www.access-programmers.co.uk/forums/showthread.php?t=115886

Thanks Yoritomo, I will try that

 

[oldozziesubby]

Hi and thanks for your help. I think it has finally been sorted out.
I saw another reply by pdx_man about adding a field of datatype DateStamp.
I added it and it seems to solved my problem so again, thanks everyone