Click here to Skip to main content
15,879,613 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am using Ms access 2007.I have create database in access & also design FORM in it.

I am new in Access. I want to know how to SAVE records in access. I have tried following code to save records, it's working:

Dim rs As DAO.Recordset
  Set db = CurrentDb
  Set rs = db.OpenRecordset("Pattern")
  'rs("ID").Value = "1"
  rs("Type").Value = txttype.Value
  rs("Amount").Value = txtamt.Value
  MsgBox "Record Save Sucessfully"

But I want to write query to save records. I tried for many times but access give me syntax error, as i new in access i don't know how to write query in msaccess code

Please give me some examples to save data in access tables using query in code

What I have tried:

Con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Test1.accdb;Persist Security Info=False;"

rs = "Insert into Pattern(ID,Type,Amount) VALUES (123,'Shirt',5000)"


MsgBox "Record Save Sucessfully"
Updated 29-Mar-16 8:12am
ZurdoDev 29-Mar-16 7:52am    
We can't help you fix your INSERT sql unless you post it. Click Improve question and add that in.
CHill60 29-Mar-16 8:58am    
It would help if you told us what the syntax error was as well.
If ID is an autonumber then you should not include it in your sql i.e.
"Insert into Pattern(Type, Amount) values ('Shirt',5000)"
and it would not be rs = .... it would be db.Execute("Insert into Pattern(Type, Amount) values ('Shirt',5000)")

1 solution

Here's a solution I use to make a quick "copy" of a datatable. I'm leaving some of the comments so you might be able to pick-up on the logic. Note that I've changed the access type from your requirement (DAO) to ADOX (ADODB). Writing this into a module in the VBA console might throw up a warning so go to Object Browser library and find my replacement.
Sub RollTableOff() 'At this stage this code updates the data table with the first input query....
    Dim cnn1 As ADODB.Connection
    Dim cat1 As New ADOX.Catalog
    Dim rst1 As New ADODB.Recordset
    Dim rst2 As New ADODB.Recordset
    'Set context for populating new table (pattern02).
    'Empty values from pattern02 before running.
    Set cnn1 = CurrentProject.Connection
    Set cat1.ActiveConnection = cnn1
    Set rst1.ActiveConnection = cnn1
    'Open recordsets based on new and orignal tables.
     rst1.Open "Pattern", , adOpenKeyset, adLockOptimistic, adCmdTable
    'Loop through recordsets to copy from original to new table.
    With rst1
         Do Until rst2.EOF
                  .Fields![ID] = rst2![ID]
                  .Fields![Type] = rst2![Type]
                  .Fields![Amount] = rst2![Amount]
    End With
End Sub

Or not ... (some substitution required)

No SQL was used in this solution.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900