Error after change BE from Access to SQL

[Vensia]

Hello,

I have code like below :

(With back end Access)

Dim rst As DAO.Recordset
Dim strSQL As String
Dim intLoop As Integer

strSQL = "SELECT * FROM tblSalaryMain WHERE EmpID = '" & strEmpID & "'
ORDER BY SalaryMonth"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
For intLoop = 1 To 12
With rst
.FindFirst "SalaryMonth = " & intLoop
If .NoMatch Then
.AddNew


(With back end SQLServer)

Dim rst As DAO.Recordset
Dim strSQL As String
Dim intLoop As Integer

strSQL = "SELECT * FROM tblSalaryMain WHERE EmpID = '" & strEmpID & "'
ORDER BY SalaryMonth"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
For intLoop = 1 To 12
With rst
.FindFirst "SalaryMonth = " & intLoop
If .NoMatch Then
.AddNew

In table tblSalaryMain, I have an Identity field called SalaryID.
In Access, there is no error message.
But in SQLServer, I get error message "No current record".
Please help. Thanks.

Vensia

 

[Sylvain Lafontaine]

Probably a missing primary key for the table tblSalaryMain on SQL-Server.
Setting an identity field doesn't automatically set the column as a primary
key for the table.

 

[Rick Brandt]

Hello,

I have code like below :

(With back end Access)

Dim rst As DAO.Recordset
Dim strSQL As String
Dim intLoop As Integer

strSQL = "SELECT * FROM tblSalaryMain WHERE EmpID = '" & strEmpID &
"' ORDER BY SalaryMonth"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
For intLoop = 1 To 12
With rst
.FindFirst "SalaryMonth = " & intLoop
If .NoMatch Then
.AddNew


(With back end SQLServer)

Dim rst As DAO.Recordset
Dim strSQL As String
Dim intLoop As Integer

strSQL = "SELECT * FROM tblSalaryMain WHERE EmpID = '" & strEmpID &
"' ORDER BY SalaryMonth"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset,
dbSeeChanges) For intLoop = 1 To 12
With rst
.FindFirst "SalaryMonth = " & intLoop
If .NoMatch Then
.AddNew

In table tblSalaryMain, I have an Identity field called SalaryID.
In Access, there is no error message.
But in SQLServer, I get error message "No current record".
Please help. Thanks.

Vensia

Is EmpID a Text field? I see no reason for there to be a difference. I use DAO
against ODBC links to SQL Server all the time and I have never encountered a
need to do anything any different than I do with regular Access tables.

 

[Vensia]

Is EmpID a Text field? I see no reason for there to be a difference. I use DAO
against ODBC links to SQL Server all the time and I have never encountered a
need to do anything any different than I do with regular Access tables.

Rick,

EmpID is a text field.
Primary Key of tblSalaryMain is EmpID + PeriodYear + PeriodMonth
I only add "dbSeeChanges" in line of code
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset,dbSeeChanges)

If I don't add "dbSeeChanges", I get error message like this :
Run-time error '3622'
You must use the dbSeeChanges option with OpenRecordset when accessing a SQL
Server table thas has an IDENTITY column

The complete error message is :
Run-time error 3021
No current record

The error stops at line :
..FindFirst "SalaryMonth = " & intLoop

Thanks.

Vensia

 

[Vensia]

There is a primary key which consists of EmpID + PeriodYear + PeriodMonth
Thanks.

 

[Vensia]

Is EmpID a Text field? I see no reason for there to be a difference. I use DAO
against ODBC links to SQL Server all the time and I have never encountered a
need to do anything any different than I do with regular Access tables.

Hello Rick,

I have created a simple table to trace the problem and found this error
actually happens in SQL Server.
I repost my code :

Dim rst As DAO.Recordset
Dim strSQL As String
Dim intLoop As Integer

strSQL = "SELECT * FROM tblSalaryMain WHERE EmpID = '" & strEmpID & "' ORDER
BY SalaryMonth"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, bSeeChanges)
For intLoop = 1 To 12
With rst
.FindFirst "SalaryMonth = " & intLoop
If .NoMatch Then
.AddNew

The above code returns error if there is no record in rst.

But if I don't have IDENTITY column in tblSalaryMain and the code like this
:

Dim rst As DAO.Recordset
Dim strSQL As String
Dim intLoop As Integer

strSQL = "SELECT * FROM tblSalaryMain WHERE EmpID = '" & strEmpID & "' ORDER
BY SalaryMonth"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) ' without using
dbSeeChanges
for intLoop = 1 To 12
With rst
.FindFirst "SalaryMonth = " & intLoop
If .NoMatch Then
.AddNew

Then there is no any error message although there is no record in rst
So how do I solve this problem if I have a table which has IDENTITY column ?

Thanks.

 

[Sylvain Lafontaine]

This is where your problem lies: you have a composite key and Access is
probably unable to know how to populate it correctly on the SQL-Server while
making sure that the new record is associated with the main table.

For the subtable, you should create an independant primary key with its own
identity column and EmpId should only be used as a foreign key. Also, if
you add a unique index on the three fields EmpID + PeriodYear + PeriodMonth,
make sure that the name of this index is alphabetically greater than the
name of the index used for the primary key; as Access use the first unique
index in alphabetical order to determine which one is the index used as the
primary key.

There have been many posts in the past about problems with Access,
SQL-Server and composite primary keys; you're not the first one.