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?

Albert D. Kallal · Accepted Answer · 2014-08-04 05:53:56Z

3

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)

answered Aug 4, 2014 at 5:53

Albert D. Kallal

50.1k3 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SalkinD Over a year ago

This exactly what I was searching for, thanks. There occurs a new problem now, but at least I get an error Message now over Errors(0): Access does not auto-increment the primary key, so I get an 'id cant be NULL' error. Is it possible to fix this?

Albert D. Kallal Over a year ago

It is SQL server that will increment the autonumber. This means that the ID (primary key column) in sql server should be of type “int”. And the column should be set as PK, and also set the Is Identity as yes, and the Identity increment to 1. This will result in a column that Access sees as autonumber. The MAJOR difference is that with SQL server you do not see the autonumber UNTIL the record is saved as opposed with regular Access tables the autonumber appears as soon as you start typing in the form. So all tables need a PK.

HansUp · Accepted Answer · 2014-08-03 13:22:10Z

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.

edited Aug 3, 2014 at 13:22

answered Aug 3, 2014 at 13:16

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Collectives™ on Stack Overflow

Inserting into SQL Server using MS Access

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related