如何在Access VBA中的记录集中设置列值 [英] How to set column values in a record set in access vba

问题描述

下面给出的是工作代码.以前，我使用的.Name属性无效. 先前的代码:

Given below is the working code. Previously I was using the .Name property that didn't work. Previous code:

      For Each s In rs.Fields
            word = Replace(strArray(count), """", "")
            count = count + 1
        'the below line shows error
            s.Name = word
        Next

新的完整工作代码.它将打开一个对话框，供用户选择.csv文件，然后将所有数据从该csv文件导入到表中.

New Complete working code. It opens a dialog for user to select the .csv file and then imports all the data into the table from that csv file.

    strMsg = "Select the file from which you want to import data"
mypath = GetPath(strMsg, True)
mypath = mypath

Dim strFilename As String: strFilename = mypath
Dim strTextLine As String
Dim strArray() As String
Dim count As Integer

Dim regex As New RegExp
regex.IgnoreCase = True
regex.Global = True
'This pattern matches only commas outside quotes
'Pattern = ",(?=([^"]*"[^"]*")*(?![^"]*"))"
regex.Pattern = ",(?=([^""]*""[^""]*"")*(?![^""]*""))"

Dim iFile As Integer: iFile = FreeFile
Open strFilename For Input As #iFile
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
count = 0

Do Until EOF(1)
    Line Input #1, strTextLine
    count = 0

'regex.replaces will replace the commas outside quotes with <???> and then the
'Split function will split the result based on our replacement
    On Error GoTo ErrHandler
    strTextLine = regex.Replace(strTextLine, "<???>")
    strArray = Split(regex.Replace(strTextLine, "<???>"), "<???>")
    Set rs = db("AIRLINES").OpenRecordset
    Dim word As Variant
    With rs
        .AddNew
        For Each s In rs.Fields
            word = Replace(strArray(count), """", "")
            count = count + 1
        'the below line shows error
            s.Value = word
        Next
        .Update
        .Close
    End With
lpp:
    Loop

db.Close
Close #iFile
MsgBox ("Imported Successfully")
Exit Sub
ErrHandler:
   Resume lpp

推荐答案

不要使用Name属性.使用价值.

Don't use the Name property. Use Value.

如何填充数组?如果它的基本索引为0，则在设置字段值后增加Count.

How are you populating the array? If it has base index of 0, then increment Count after setting the field value.

这篇关于如何在Access VBA中的记录集中设置列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！