How to save records in ms access 2007

Question

0.00/5 (No votes)

See more:

C#

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.AddNew
  'rs("ID").Value = "1"
  rs("Type").Value = txttype.Value
  rs("Amount").Value = txtamt.Value
  
      rs.Update
  rs.Close
  
  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)"

rs.Update
rs.Close

MsgBox "Record Save Sucessfully"

Posted 28-Mar-16 19:11pm

Yogi ,Pune

Updated 29-Mar-16 8:12am

v2

Add a Solution

Comments

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RedDk · Answer 1 · 2016-03-29T08:12:00

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
            .AddNew
                  .Fields![ID] = rst2![ID]
                  .Fields![Type] = rst2![Type]
                  .Fields![Amount] = rst2![Amount]
            .Update
            .MoveNext
        rst2.MoveNext
        Loop
        
    End With
End Sub

Or not ... (some substitution required)

No SQL was used in this solution.