0

I am new to this forum.

I have the following environment: an Access frontend client that is connected to a database on SQL Server (backend). I would like to use an Access form to enter data that is associated with a specific ID number (in database Table). Ideally the ID will automatically increment when an INSERT is made to the Table. The vba code (based on the SQL query) I wrote is the following:

Option Compare Database
Option Explicit

Private Sub List0_Click()

Dim HelloWORLD As String
Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb

CurrentDb.Execute "INSERT INTO Table1 (TESTING_1, TESTING_2) VALUES (" & 9 & "," & HelloWORLD & ")"
End Sub

The code is compiling but it is not appending the Table1 like it is supposed to do. Please Help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3405572
  • 117
  • 1
  • 2
  • 9
  • Why dont u use AUTO_INCREMENT. For more http://stackoverflow.com/questions/5669198/how-do-i-auto-increment-a-column-in-my-table – Nitin Varpe Mar 11 '14 at 10:40
  • Additionally `HelloWORLD ` as a string would need to be enclosed in single quotes. – Alex K. Mar 11 '14 at 11:24

2 Answers2

0

You can use sequences. You can creates sequence MySequence1 and use it:

CurrentDb.Execute "INSERT INTO Table1 (TESTING_1, TESTING_2) VALUES (NEXT VALUE FOR MyDemoSequence," & HelloWORLD & ")"

Paweł P
  • 33
  • 3
  • Thank you for your answer. I am now trying to create a sequence using SQL server queries but I was only able to find the TransactSQL syntax to create it. The command with SQL querry does not recognize the term SEQUENCE although I have dbowner rights. I would really appreciate your views on this. Thanks again. – user3405572 Mar 12 '14 at 23:09
0

Can you make the ID field an IDENTITY field within SQL Server? This will automatically increment whenever a new row is inserted. Example from Microsoft documentation:

CREATE TABLE new_employees
(
 id_num int IDENTITY(1,1),
 fname varchar (20),
 minit char(1),
 lname varchar(30)
)

The first number is the seed, and the second is the increment, so IDENTITY(1,1) means start at '1', and increase by '1' each time.

You can retrieve the ID that was created using SELECT SCOPE_IDENTITY(); following the insert if necessary.

Alan
  • 2,962
  • 2
  • 15
  • 18