#Deleted

[Zedster]

I have written a procedure that imports a text file and enters each line into a table on a MS SQL Server Database.

10    On Error GoTo err_ReadInFile

          Dim strTextLine As String
20        Dim intFile As Integer: intFile = FreeFile
          Dim db As Database
          Dim rs As Recordset
30        Set db = CurrentDb
40        Set rs = db.OpenRecordset(strTable)
          
50        Open strFilename For Input As #intFile
60        Do Until EOF(1)
70            Line Input #1, strTextLine
80            Debug.Print strTextLine
90            With rs
100               .AddNew
110               !IF_TextLine = strTextLine
120               !IF_Category = strCategory
130               .Update
140           End With
150       Loop
160       Close #intFile
170       rs.Close
          
180       Set rs = Nothing
190       Set db = Nothing

The table is linked to my access database.

I have imported the file, and it runs without error. But when I open up the table in Access it shows 54 lines with each column containing the "#Deleted". When I open up the table on the SQL server all 54 lines are populated with the correct text.

Can anyone explain why the data appears fine on the SQL server but will not display in Access

 

[Minty]

Did you already have the table open in access?
Does the table have a primary key defined in the link set up?

 

[arnelgp]

add Option Explicit to your code and you will see your Error.
also note that on opening a linked table (msssql), you need
to add dbSeeChanges to the openrecordset method:

Set rs = db.OpenRecordset(strTable, dbOpenDynaset, dbSeeChanges)

 

[Zedster]

Did you already have the table open in access?
Does the table have a primary key defined in the link set up?

No to first question
Yes to second primary key is configured in the table on SQL server

 

[Zedster]

add Option Explicit to your code and you will see your Error.
also note that on opening a linked table (msssql), you need
to add dbSeeChanges to the openrecordset method:

Set rs = db.OpenRecordset(strTable, dbOpenDynaset, dbSeeChanges)

The table has Option Explicit stated and compiles fine.
I added dbOpenDynaset, dbSeeChanges as recommended, it made no difference:

Whereas on SQL Server I get

 

[Minty]

Ah ha - a timestamp... Bet that's the issue.

 

[arnelgp]

Using timestamp as unique record identifier has long been abandoned in favor of pk key.

 

[Zedster]

Using timestamp as unique record identifier has long been abandoned in favor of pk key.

It isn't the unique record identifier. The unique record identifier and primary key is IF_ID is.

 

[Zedster]

Ah ha - a timestamp... Bet that's the issue.

Why is that? I went on a training course for using Ms Access with MS SQL around 15 years ago and was told that the SQL table must have a timestamp else it would not perform reliably. In the last 15 years I must have created scores of databases (I use databases for almost everything). In every table I have created I have always added a timestamp field, which I never use, I just followed the training. This the first time I have come across this issue.

 

[Zedster]

Solved!

I had a need to import fresh data daily with each file containing potentially hundreds of rows. So in my infinite wisdom I decided that rather than use integer as the datatype for IF_ID. I would use BIGINT (this first time I have ever done such a thing). It appears BIGINT exceeds the range that MS Access can support. I changed BIGINT back to big integer and all works.

 

[Minty]

BIGINT support has only recently been introduced to Access, and you would need an absolutely massive number of records to exceed a normal SQL Integer range!

Data type	Range	Storage
bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	8 Bytes
int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	4 Bytes
smallint	-2^15 (-32,768) to 2^15-1 (32,767)	2 Bytes
tinyint	0 to 255	1 Byte

The TimeStamp thing was originally a requirement if you didn't have a primary key and were using the old ODBC Drivers. These days it's surplus to requirements, I haven't used one for years.

 

[Zedster]

BIGINT support has only recently been introduced to Access, and you would need an absolutely massive number of records to exceed a normal SQL Integer range!

Data type	Range	Storage
bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	8 Bytes
int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	4 Bytes
smallint	-2^15 (-32,768) to 2^15-1 (32,767)	2 Bytes
tinyint	0 to 255	1 Byte

The TimeStamp thing was originally a requirement if you didn't have a primary key and were using the old ODBC Drivers. These days it's surplus to requirements, I haven't used one for years.

Thanks for that, its been a long time since I looked into data types and it looks like bigint wont be required in my lifetime. It is always good to make mistakes, I won't forget this in a while, although I could have done without 3 hours of experimenting at getting nowhere this afternoon tbh. Thanks for the input on timestamp too I will try not including it from now and see what happens, from what you suggest it will be nothing.