如何检查记录使用的ID，那么如果记录存在更新，如果不增加新纪录 [英] How to check for record by using ID, then if record exists update if not add new record

问题描述

我已经创建一个Excel用户窗体来收集数据。我已经连接到访问转储数据。不过，我想每一个用户presses提交按钮的时间来更新访问。

基本上我需要Select语句来确定一个ID存在，那么，如果它不存在，我需要使用INSERT添加新行。我很新的与任何SQL所以任何帮助将是巨大的。

下面是code我现在，我需要它适应ADO。

 分更新（）
昏暗CNN作为ADODB.Connection
昏暗的myconn
昏暗首先作为ADODB.Recordset
昏暗STRSQL作为字符串

设置CNN =新ADODB.Connection
的myconn = ThisWorkbook.Path和放大器; Application.PathSeparator和放大器; TARGET_DB

与CNN
    .Provider =Microsoft.ACE.OLEDB.12.0
    。开的myconn

首先设置=新ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open来源：=泡沫，的ActiveConnection：= CNN，_
        的CursorType：= adOpenDynamic，锁定类型：= ADLOCKOPTIMISTIC，_
        选项​​：= adCmdTable





STRSQL =SELECT * FROM的泡沫，FoamID =＆放大器; txtMyID
设置RST = CurrentDb.OpenRecordset（STRSQL，dbOpenDynaset）

如果（rst.RecordCount = 0）然后
     DoCmd.RunSQLINSERT INTO泡沫（I​​D，型号，工作，环境管理计划重量，烤箱）VALUES＆放大器; _
          （与＆amp; txtID＆安培;，与＆amp; txtField1＆安培;'，'与＆amp; txtField2＆安培;'，'与＆amp; txtField3＆安培;'，'与＆amp; txtField4＆安培;'， '与＆amp;＆txtField5安培;'）;

结束如果

 关闭连接
    rst.Close
    cnn.Close
    第一个设置=什么
    设置CNN =没有


结束与

结束小组
 

解决方案

我修改你的code样品就足以得到它的工作在我的系统，并在Excel 2007中测试了这个版本

当我使用了 lngId 的值匹配现有记录的 ID ，该记录是在打开记录我可以更新其字段的值。

在 lngId 不匹配现有记录的 ID ，记录打开空[（BOF和.EOF）= TRUE 。在这种情况下，我添加了一个新的记录，并添加字段值了。

分更新（）     常量TARGET_DB的String =database1.mdb     昏暗CNN作为ADODB.Connection     昏暗的myconn作为字符串     昏暗首先作为ADODB.Recordset     昏暗STRSQL作为字符串     昏暗lngId只要     设置CNN =新ADODB.Connection     的myconn = ThisWorkbook.Path和放大器; Application.PathSeparator和放大器; TARGET_DB     与CNN         .Provider =Microsoft.ACE.OLEDB.12.0         。开的myconn     结束与     lngId = 4     STRSQL =SELECT * FROM tblFoo WHERE ID =＆放大器; lngId     首先设置=新ADODB.Recordset     随着RST         .CursorLocation = adUseServer         。开来源：= STRSQL，的ActiveConnection：= CNN，_                 的CursorType：= adOpenDynamic，锁定类型：= ADLOCKOPTIMISTIC，_                 选项​​：=了adCmdText         如果（.BOF而.EOF）然后             没有找到匹配;添加新记录             。添新             ！ID = lngId             ！some_text =Hello World的         其他             匹配记录找到;更新             ！some_text =Hello World的         结束如果         .Update         。关闭     结束与     第一个设置=什么     cnn.Close     设置CNN =没有 结束小组

I've create an excel userform to collect data. I have connected it to Access to dump the data. However I want to update Access every time a user presses the submit button.

Basically I need the Select statement to determine an id existence, then if it doesn't exists I need to use the INSERT to add the new row. I'm very new with any SQL so any help would be great.

Here is the code I have now, I need to adapt it to ADO.

Sub Update()
Dim cnn As ADODB.Connection
Dim MyConn
Dim rst As ADODB.Recordset
Dim StrSql As String

Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open MyConn

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:="Foam", ActiveConnection:=cnn, _
        CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
        Options:=adCmdTable





StrSql = "SELECT * FROM Foam WHERE FoamID = " & txtMyID
Set rst = CurrentDb.OpenRecordset(StrSql, dbOpenDynaset)

If (rst.RecordCount = 0) Then
     DoCmd.RunSQL "INSERT INTO Foam (ID, Part, Job, Emp, Weight, Oven) VALUES " & _
          "(" & txtID & ", '" & txtField1 & "', '" & txtField2 & "', '" & txtField3 & "', '" & txtField4 & "', '" & txtField5 & "' );"

End If

 ' Close the connection
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing


End With

End Sub

解决方案

I revised your code sample just enough to get it working on my system and tested this version in Excel 2007.

When I use a value for lngId which matches the id of an existing record, that record is opened in the recordset and I can update the values of its fields.

When lngId does not match the id of an existing record, the recordset opens empty [(.BOF And .EOF) = True]. In that situation, I add a new record and add the field values to it.

Sub Update()
    Const TARGET_DB As String = "database1.mdb"
    Dim cnn As ADODB.Connection
    Dim MyConn As String
    Dim rst As ADODB.Recordset
    Dim StrSql As String
    Dim lngId As Long

    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open MyConn
    End With

    lngId = 4
    StrSql = "SELECT * FROM tblFoo WHERE id = " & lngId
    Set rst = New ADODB.Recordset
    With rst
        .CursorLocation = adUseServer
        .Open Source:=StrSql, ActiveConnection:=cnn, _
                CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
                Options:=adCmdText
        If (.BOF And .EOF) Then
            ' no match found; add new record
            .AddNew
            !ID = lngId
            !some_text = "Hello World"
        Else
            ' matching record found; update it
            !some_text = "Hello World"
        End If
        .Update
        .Close
    End With

    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

这篇关于如何检查记录使用的ID，那么如果记录存在更新，如果不增加新纪录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！