Inserting into SQL Server using MS Access

Question

I know this will be a common problem, nevertheless I didn't find any solution.

I migrated my access database into SQL Server Express. SELECT and UPDATE work well, actually better that with the Access engine. But I can't get standard insertions running...

This does not work:

Set rc = CurrentDb.openRecordset("SELECT * FROM DBLog WHERE false")
rc.AddNew
rc.update  '-->ERROR
rc.close

This does not work either:

DoCmd.RunSQL("INSERT INTO [test2].dbo.DBLog (type) VALUES (6)")

This does work: sending the above SQL with pass through. So its not the SQL Servers problem.

I have set IDENTITY and PRIMARY in the Database. Access also knows the primary. In design view although there is no "Autonumber", and this may be the problem. But how to resolve that?

Answer 1

Since you have a linked table named DBLog, I would execute an INSERT statement, similar to the one which worked in SQL Server itself, against that linked table.

Dim db As DAO.Database
Dim strInsert As String
strInsert = "INSERT INTO DBLog ([type]) VALUES (6)"
Set db = CurrentDb
db.Execute strInsert, dbFailonerror

I enclosed the field name type in square brackets because it is a reserved word.

Answer 2

As a general rule, after migrating to SQL server for the back end, then a typical and common open reocrdset of

Set rst = CurrentDb.OpenRecordset("Contacts")

Needs to become

Set rst = CurrentDb.OpenRecordset("Contacts", dbOpenDynaset, dbSeeChanges)

So, in your case:

Set rc = CurrentDb.openRecordset("SELECT * FROM DBLog WHERE false" dbOpenDynaset, dbSeeChanges)