#Deleted records in linked sql2k database

#Deleted records in linked sql2k database

Post by Darin Anderso » Tue, 10 Sep 2002 18:15:48



Hello,  I have a sql2k table that has valid data in it.  I have linked to it with Access XP but when I open the table to view I get #Deleted in all of the fields.  I have done a search in TechNet and found an article that says I am running an older version of Jet and it instructs me to install the latest version (6) of jet.  After doing this I am still getting the deleted records.  I have tried installing Access and the update to Jet on a Win2K server and get the same error there.  Anybody have any  ideas before I spend $200 to call MS?

Thanks

Darin

 
 
 

#Deleted records in linked sql2k database

Post by Brian Mora » Tue, 10 Sep 2002 21:05:42


I don't have the KB handy, but I belive this will happen if the PK of the SQL table is a float datatype. Float is a non-exact number and SQL and ODBC basically translate the number differently. The numbers don't match so Access think the record has been deleted...  change the data type to a precise value such as a decimal datatype and see if the problem goes away...

--

Brian Moran
SQL Server MVP
SQL Server Magazine Columnist


  Hello,  I have a sql2k table that has valid data in it.  I have linked to it with Access XP but when I open the table to view I get #Deleted in all of the fields.  I have done a search in TechNet and found an article that says I am running an older version of Jet and it instructs me to install the latest version (6) of jet.  After doing this I am still getting the deleted records.  I have tried installing Access and the update to Jet on a Win2K server and get the same error there.  Anybody have any  ideas before I spend $200 to call MS?

  Thanks

  Darin

 
 
 

1. Deleted records when text file is linked as table (#deleted)

I am trying to attach a text file to an existing access database using the
link method.  I am finding all records with quotes (") in them show up as
deleted when the link is created.  I would like to define the text qualifier
as {none}.  I have search but find no examples of how to do this.
    Below is the schema.ini file and a sample of the code with a record set
example.
    Any help in resolving this would be appreciated.  I have tried to escape
and double the quotes but nothing seems to stop the deleting.

Dan McGivney
Knapp Enterprises


Schema:
[update.txt]
colnameheader=true
format=delimited(|)
CharacterSet=ANSI

connect example
Public Sub Add_Table(Tname, FName)

    ' Check to see if table exist
        For Each td In dbsJet.TableDefs
             If td.Name = Tname Then
                dbsJet.TableDefs.Delete Tname
             End If
        Next
    ' ad table
    Set td = dbsJet.CreateTableDef _
        (Tname)
     td.Connect = _
        "TEXT;DATABASE=C:\Inetpub\wwwroot\text_data"
      td.SourceTableName = FName
    dbsJet.TableDefs.Append td
End Sub

Record set
show_idTitle     Show_Type     Genre1     Year     Actor     Dur
GGLA06     Golfing the Great Lakes     Sport     Golf               30
#Deleted     #Deleted     #Deleted     #Deleted     #Deleted     #Deleted
GHSI01     Gung Ho! Succeeding In A Changing World     Special     Business
90

2. New Database Features in VB 6

3. BUG: sql2k/sp1 linked server to sql2k dirty commit

4. B-U-I-L-D Y-O-U-R O-W-N C-A-B-L-E B-O-X.......................... 4914

5. Deleting records from a recordset without deleting from the database

6. A classic one...

7. SQL2K and Linked Server to Federated DB2 Database

8. Error 3167 Record Deleted when there is no deleted record

9. Failure to delete records on a linked table

10. Delete a record from master and linked table

11. error deleting records in linked server

12. Deleting records and those that link to them from other tables