Access 2003 and SQL Server 2005 and DAO recordset

[stefania nj]

Hi,
I have migrated my access tables to SQL Server 2005.
I linked the tables to use with my front-end via ODBC.
I am having serious issues with DAO recordsets

I previously worked with MySQL linked tables and did not experience
this issue.

the test code is the following
Public Function testRec()
Dim rst As DAO.Recordset
Dim sql As String

sql = "SELECT * FROM TBL_ADM_COMPANIES;"
Set rst = CurrentDb.OpenRecordset(sql)
Debug.Print rst.GetRows
rst.Close
End Function

I get run time error 3622
"You must use the dbSeeChanges option with OpenRecordset when
accessing a SQL Server table that has an IDENTITY column"

I added the dbSeeChanges option and run the following code
Public Function testRec()
Dim rst As DAO.Recordset
Dim sql As String

sql = "SELECT * FROM TBL_ADM_COMPANIES;"
Set rst = CurrentDb.OpenRecordset(sql, dbSeeChanges)
Debug.Print rst.GetRows
rst.Close
End Function

I get the following run time error
3001
invalid argument

this code runs correctly
Public Function testRecADO()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String

Set cnn = CurrentProject.Connection
sql = "SELECT * FROM TBL_ADM_COMPANIES"
rst.Open sql, cnn, adOpenStatic

Debug.Print rst.GetString
rst.Close
End Function

But I did not expect to having to change all DAO to ADODB connection I
did not have to do that with MySQL.
Please help.

Thank you in advance,

Stefania

 

[John W. Vinson]

Hi,
I have migrated my access tables to SQL Server 2005.
I linked the tables to use with my front-end via ODBC.
I am having serious issues with DAO recordsets

Set rst = CurrentDb.OpenRecordset(sql, dbSeeChanges)

Try

Set rst = CurrentDb.OpenRecordset sql, dbOpenDynaset, dbSeeChanges

There are multiple arguments to the OpenRecordset method. The order in which
they are provided makes a difference!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com