Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access ==> SQL Server 2

Status
Not open for further replies.
LonnieJohnson

LonnieJohnson

Programmer
Apr 16, 2001
2,628
US
I have an application built in Access that creates new records in an SQL Server table. The SQL table is linked via a DSN.

My problem is this. For no good reason I can see, My record appears to get deleted before the record is saved. The access fields all say DELETED. But if I look at the table, my record is there. This is annoying to the user. It also, stops my code from running because other things need to occur when the record gets saved.

The quirky part is that it dosen't happen all the time. I can keep adding the same data in each field and it will save the record two, three, four times with no problems. Then out of the blue WHAM!!! All the fields will say DELETED and the code will stop.

Help.

prodevmg@yahoo.com
ProDev, MS Access Applications B-)
May God bless you in the year 2002.
 
Sort by date Sort by votes
Hey man I have the same problem. Here we run an SQL server 2000 and use the SQL drivers off the SQL Server cd for our clients to connect to the server. We have all the main tables linked in Access to the SQL server through ODBC. Sometimes all the records come up as deleted in forms and reports in Access and I don't know why. If you find out anything please e-mail me back. Thanks a lot!!
 
Upvote 0 Downvote
Some things to check.

1) Does the SQL table have a unique index, primary key, or timestamp column? If not, add one of these features. These errors can occur if Access cannot uniquely identify a row in a SQL table.

2) If the unique index or primary key does exist, do any of the columns have null values?

3) Does the SQL table contain an insert trigger that modifies the key values (or unique index values)?

4) Does the SQL table contain a ROWGUID? If so see the following article.

ACC2000: Records Display #DELETED When You Use Access 2000 to Link to SQL Server 7.0 Table with ROWGUID

5) Do one or more key columns contain FLOAT or REAL data? I'm not sure if this can cause the problem you see but it seems likely because FLOAT and REAL are approximate values that Access may not find after SQL stores the record. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
 
Upvote 0 Downvote
Yes all of our tables have primary keys.
Yes some of our columns do have null values, but none of our primary keys are null.
No we no dont have triggers that even so much as look at our primary keys.
No we are not using ROWGUID, and I think the problem that relates most to our problem is highlighted by this microsoft article Our primary keys do not have real or float numbers, we used the numeric data type in SQL and have only whole numbers in that primary key
 
Upvote 0 Downvote
It's too bad Microsoft doesn't provide a solution in the KB article. That article is about 20 months old. Have you updated to a later version of MDAC to see if that solves the problem? Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
 
Upvote 0 Downvote
Thanks guys for the replies and input. I have a primary key which is a record ID. I had it set as an identity field. Again my situation did not happen everytime. Just sometime. I am using A97. When I add a record via my Access form to the linked SQL 2000 table, it may take ok, sometimes it didn't and show the #DELETED in all fields. The record would get posted but my code would stop.

After I picked my code and set up apart, I could only deduce that it was my RecordID field. I removed the Identity property. I automatically increment it within my code.

I know this is probably not the right solution, but I am under pressure to get it up. Thus, the work around.

Does anyone else have any input?
prodevmg@yahoo.com
ProDev, MS Access Applications B-)
May God bless you in the year 2002.
 
Upvote 0 Downvote
Yeah we blew away our original primary key and just made a new one in a new column that incremented properly, now it works. RRRRAAARRRRRRRRR @%*%&#*@#@!$ SQL and Access
 
Upvote 0 Downvote
To show SQL Server data on an Access Form or Report it is not necessary to Link the SQL Server tables in Access. Instead setup an ADO connection string and format the query or table and use this as the recordsource for the Form or Report. The ODBC driver does not need to be used at all, which would avoid the error you are experiencing.
 
Upvote 0 Downvote
Thanks cmmrfrds,

I like this approach. It is cleaner than the work around I was trying.

I need some help creating the recordsource for the form. I have the connection and it works.

I have this is a public module...


Public adoCN As ADODB.Connection
Public Const ADOConnect As String = "Provider=sqloledb.1;Integrated " _
& "Security=SSPI;Persist Security Info=False;Initial Catalog=CLIENT_KS;Data Source=KVCSQL"

Public Sub ADO_CONN()
Set adoCN = New ADODB.Connection
adoCN.Open (ADOConnect)
End Sub


The above public sub will get run in the opening Autoexec

I have this the folloiwing in the form that needs the recordsource...


Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "testlink", adoCN


How do I get it to be the recordsource?

Me.RecordSource = rs.??????


Thanks again prodevmg@yahoo.com
ProDev, MS Access Applications B-)
May God bless you in the year 2002.
 
Upvote 0 Downvote
This example is from an Access 2000 mdb file, I am having trouble with my PC that has Access 97 so I can't verify right now.

'-- OnOpen Event
Dim yy As Access.Form
Set yy = Forms!frm_IDTable.Form
'--
Set yy.Recordset = rs
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
341
Volvere
V
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
322
pjw001
pjw001
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
223
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
199
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
239
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top