Access 2003 and SQL Server 2005 and DAO recordset

  • Thread starter Thread starter stefania nj
  • Start date Start date
S

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
 
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
Click to expand...
Set rst = CurrentDb.OpenRecordset(sql, dbSeeChanges)
Click to expand...

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
 
Back
Top