通过VB在Microsoft Access中插入多条记录

Question

我在Access中创建了数据库，并给出了数据类型“短文本”。

现在我试图通过Visual Basic提示记录到此表中。我到目前为止写了一个代码，但需要帮助来修复错误。我得到的编译错误：

Duplicate declaration in current scope

我不知道它是什么？

Option Compare Database 

Sub arrayData() 

Dim CustomerName() As Variant 
Dim num As Integer, dbs As Database, InsertRecord As String 
Dim CustomerID As Long, num1 As Long, CustomerName As String 

Set dbs = CurrentDb() 
CustomerID = 0 
For num1 = 0 To 49999 

CustomerID = CustomerID + 1 
CustomerName = Array("Peter", "Mary") 
CustomerAddress = Array("163 City Rd, SOUTHBANK", "65 Orange St, BENTLEIGH EAST") 
CustomerPhoneAddress = Array("0416874963", "0478937534") 
num = Int((200 - 0 + 1) * Rnd + 0) 
CustomerName = CustomerName(num) 
CustomerAddress = CustomerAddress(num) 
CustomerPhoneNo = CustomerPhoneNo(num) 
InsertRecord = "INSERT INTO Customers (CustomerID,CustomerName,CustomerAddress,CustomerPhoneNo) VALUES (" & "'" & CustomerID & "'" & "," & "'" & CustomerName & "'" & "," & "'" & CustomerAddress & "'" & "," & "'" & CustomerPhoneNo & "'" & ")" 

dbs.Execute InsertRecord 
Debug.Print CustomerID, CustomerName, CustomerAddress, CustomerPhoneNo 

Next 

End Sub 

在这种情况下我该怎么办？任何帮助，将不胜感激。

Answer 1

您使用的语言是“Visual Basic for Applications”（VBA）。这种语言是面向行的。它表示一条语句必须放在一行上，除非您使用的是一行连续字符（_），前面至少有一个空格。

Dim CustomerID As Long, num1 As Long, CustomerName As String, CustomerAddress _ 
    As String, CustomerPhoneNo As String 

不过，我会放置在单独的行Long和String变量和写：

Dim CustomerID As Long, num1 As Long 
Dim CustomerName As String, CustomerAddress As String, CustomerPhoneNo As String 

第二个语法错误是在InsertRecord = "INSERT INTO ...声明。这里同样的问题。它建议立即进行删除这个样子：

InsertRecord = "INSERT INTO CUSTOMERS (CustomerID,CustomerName," _ 
     & "CustomerAddress,CustomerPhoneNo) VALUES " _ 
     & "(" & "'" & CustomerID & "'" & "," & "'" & CustomerName & "'" & "," & "'" _ 
     & CustomerAddress & "'" & "," & "'" & CustomerPhoneNo & "'" & ")" 

---

此外，什么是- 0和+ 0好于Int((200 - 0 + 1) * Rnd + 0)。该表达式相当于Int(201 * Rnd)。

---

经过编辑，看起来您没有行延续。

错误是：您有2个变量名为CustomerName。变量名必须是唯一的：

Dim CustomerName() As Variant 
Dim CustomerName As String 

例如，命名阵列CustomerNames：

Dim CustomerNames() As Variant 

---

你应该缩进代码。它使阅读起来更容易。

Sub arrayData() 
    Dim CustomerName() As Variant 
    ... 
    CustomerID = 0 
    For num1 = 0 To 10 
     CustomerID = CustomerID + 1 
     ... 
     Debug.Print CustomerID, CustomerName, CustomerAddress, CustomerPhoneNo 
    Next 
End Sub