如何在ms access 2007中保存记录 [英] How to save records in ms access 2007

问题描述

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

我的尝试：



Con =Provider = Microsoft.ACE.OLEDB.12.0;数据源= D：\ Test1.accdb;持久安全信息=假;



rs =插入模式（ID，类型，金额）值（123 ，'衬衫'，5000）



rs.Update

rs.Close



MsgBox记录保存成功

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"

推荐答案

这是我用来快速复制数据表的解决方案。我将留下一些评论，以便您可以了解逻辑。请注意，我已将访问类型从您的要求（DAO）更改为ADOX（ADODB）。将此写入VBA控制台中的模块可能会发出警告，因此请转到对象浏览器库并找到我的替代品。

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

或者不......（需要一些替换）



此解决方案中未使用SQL。

Or not ... (some substitution required)

No SQL was used in this solution.

这篇关于如何在ms access 2007中保存记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！