Error after change BE from Access to SQL

V

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
 
S

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.
 
R

Rick Brandt

Vensia said:
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
Click to expand...

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.
 
V

Vensia

Rick Brandt said:
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.
Click to expand...


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
 
V

Vensia

Rick Brandt said:
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.
Click to expand...


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.
 
R

Rick Brandt

Vensia said:
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 ?
Click to expand...

ANY time you open a record set you should first test to see if it contains any
records.

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

If rst.RecordCount > 0 Then
for intLoop = 1 To 12
With rst
.FindFirst "SalaryMonth = " & intLoop
If .NoMatch Then
.AddNew
....

I'm surpised that didn't cause an error with your Access table.
 
S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Findfirst recordset to search memo field type 11
Access 2003 and SQL Server 2005 and DAO recordset 1
Syntax error in sql string to update a table from a form using co 2
Listbox execute SQL error 2
Populate Combo Boxes using Form_Current Options 3
Pls help, my VBA does not work 3
create new record (addnew?) 3
Switchboard form no longer fills 1

Top