#Deleted (1 Viewer)

Zedster

Registered User.
Local time
Today, 20:17
Joined
Jul 2, 2019
Messages
168
I have written a procedure that imports a text file and enters each line into a table on a MS SQL Server Database.

Code:
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

AWF VIP
Local time
Today, 20:17
Joined
Jul 26, 2013
Messages
10,355
Did you already have the table open in access?
Does the table have a primary key defined in the link set up?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:17
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 20:17
Joined
Jul 2, 2019
Messages
168
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

Registered User.
Local time
Today, 20:17
Joined
Jul 2, 2019
Messages
168
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:

Capture.PNG


Whereas on SQL Server I get

Capture2.PNG
 
Last edited:

Minty

AWF VIP
Local time
Today, 20:17
Joined
Jul 26, 2013
Messages
10,355
Ah ha - a timestamp... Bet that's the issue.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:17
Joined
May 7, 2009
Messages
19,169
Using timestamp as unique record identifier has long been abandoned in favor of pk key.
 

Zedster

Registered User.
Local time
Today, 20:17
Joined
Jul 2, 2019
Messages
168
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

Registered User.
Local time
Today, 20:17
Joined
Jul 2, 2019
Messages
168
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

Registered User.
Local time
Today, 20:17
Joined
Jul 2, 2019
Messages
168
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

AWF VIP
Local time
Today, 20:17
Joined
Jul 26, 2013
Messages
10,355
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 typeRangeStorage
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
tinyint0 to 2551 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

Registered User.
Local time
Today, 20:17
Joined
Jul 2, 2019
Messages
168
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 typeRangeStorage
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
tinyint0 to 2551 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.
 

Users who are viewing this thread

Top Bottom