在MS Access中使用多个DAO记录集

Question

第一个帖子，所以要尽可能温和！：）

我正在Access中创建一个新的数据库来更新我们的电子商务软件（也是基于Access的）。

我们收到来自供应商的3个提要，所有CSV文件的格式略有不同。我已经使用链接表成功地导入了提要，并且可以（我认为）根据需要通过程序刷新文件中的数据。

我创建了一个"CurrentProducts"表，其中包括目前我们网站上的所有产品。

我想依次从CurrentProducts表中获取每个产品代码，在每个供应商提要中查找它，根据我们的购买价格计算我们的销售价格，找出哪个供应商提供的价格最便宜，然后相应地更新CurrentProducts表格。

我以前经常在Excel中使用VBA进行宏操作，但我从未真正接触过Access中的DAO记录集，所以我承认我真的不知道自己在做什么！

到目前为止，我已经得到了下面的代码。CurrentProducts表中约有17900条记录，"Ingram"表中近51000条记录，ScanSource表中近15000条记录，Varlink表中约3000条记录。

我已经让代码运行了5-10分钟，虽然代码看起来确实有效，但运行速度非常慢。我只能假设，必须有一种比我目前所做的更快/更容易的方法来访问记录集中的数据。

那么，交给你们，我是应该放弃这一切，重新开始，还是可以从这里开始调整？

谢谢。

Private Sub Command0_Click()
Dim var As DAO.Recordset
Dim ing As DAO.Recordset
Dim scan As DAO.Recordset
Dim curr As DAO.Recordset
Dim filtvar As DAO.Recordset
Dim filtscan As DAO.Recordset
Dim filting As DAO.Recordset
Dim db As Database
Dim varSQL As String, ingSQL As String, scanSQL As String, currSQL As String
Dim prodcode As String
Dim varPrice As Double, ingPrice As Double, scanPrice As Double, currPrice As Double
DoCmd.Hourglass True
Set db = CurrentDb
currSQL = "select ProductCode, Price from CurrentProducts"
varSQL = "select ProductCode, (Price*1.25) as CalcPrice from Varlink"
ingSQL = "select ProductCode, (Price*1.25) as CalcPrice from Ingram"
scanSQL = "select ProductCode, (Price*1.25) as CalcPrice from ScanSource"
Set curr = db.OpenRecordset(currSQL)
Set var = db.OpenRecordset(varSQL)
Set ing = db.OpenRecordset(ingSQL)
Set scan = db.OpenRecordset(scanSQL)
curr.MoveLast 'Needed to get the accurate number of records
'Show the progress bar
SysCmd acSysCmdInitMeter, "Working...", curr.RecordCount
curr.MoveFirst
Do While Not curr.EOF
prodcode = curr!ProductCode
var.Filter = "[ProductCode] = " & "'" & prodcode & "'"
Set filtvar = var.OpenRecordset
ing.Filter = "[ProductCode] = " & "'" & prodcode & "'"
Set filting = ing.OpenRecordset
scan.Filter = "[ProductCode] = " & "'" & prodcode & "'"
Set filtscan = scan.OpenRecordset
usevarprice = 0
useingprice = 0
usescanprice = 0
If filtvar.EOF And filtvar.BOF Then
Else
    varPrice = filtvar!CalcPrice
    varPrice = Round(varPrice, 0)
    usevarprice = 1
End If
If filting.EOF And filting.BOF Then
Else
    ingPrice = filting!CalcPrice
    ingPrice = Round(ingPrice, 0)
    useingprice = 1
End If
If filtscan.EOF And filtscan.BOF Then
Else
    scanPrice = filtscan!CalcPrice
    scanPrice = Round(scanPrice, 0)
    usescanprice = 1
End If
If usevarprice = 1 And useingprice = 1 And usescanprice = 1 Then
    If varPrice < ingPrice And varPrice < scanPrice Then
        newPrice = varPrice
    ElseIf ingPrice < varPrice And ingPrice < scanPrice Then
        newPrice = ingPrice
    Else
        newPrice = scanPrice
    End If
ElseIf usevarprice = 1 And useingprice = 1 And usescanprice = 0 Then
    If varPrice < ingPrice Then
        newPrice = varPrice
    Else
        newPrice = ingPrice
    End If
ElseIf usevarprice = 1 And useingprice = 0 And usescanprice = 1 Then
    If varPrice < scanPrice Then
        newPrice = varPrice
    Else
        newPrice = scanPrice
    End If
ElseIf usevarprice = 0 And useingprice = 1 And usescanprice = 1 Then
    If scanPrice < ingPrice Then
        newPrice = scanPrice
    Else
        newPrice = ingPrice
    End If
Else
    If usevarprice = 1 Then
        newPrice = varPrice
    ElseIf useingprice = 1 Then
        newPrice = ingPrice
    ElseIf usescanprice = 1 Then
        newPrice = scanPrice
    End If
End If
curr.Edit
curr!Price = newPrice
curr.Update
curr.MoveNext
n = n + 1
'Update the progress bar
SysCmd acSysCmdUpdateMeter, n
'Keep the application responding (optional)
DoEvents
Loop
curr.Close: Set curr = Nothing
var.Close: Set var = Nothing
ing.Close: Set ing = Nothing
scann.Close: Set scan = Nothing
'Remove the progress bar
SysCmd acSysCmdRemoveMeter
'Show the normal cursor again
DoCmd.Hourglass False
End Sub

Answer 1

考虑这个查询，使用它只需剪切并粘贴到查询设计窗口的SQL视图中。

SELECT c.productcode,
       c.price,
       s.supfile,
       s.calcprice
FROM   currentproducts c
       LEFT JOIN (SELECT "varlink"        AS supfile,
                         productcode,
                         ( price * 1.25 ) AS CalcPrice
                  FROM   varlink
                  UNION ALL
                  SELECT "ingram"         AS supfile,
                         productcode,
                         ( price * 1.25 ) AS CalcPrice
                  FROM   ingram
                  UNION ALL
                  SELECT "scansource"     AS supfile,
                         productcode,
                         ( price * 1.25 ) AS CalcPrice
                  FROM   scansource) AS s
              ON c.productcode = s.productcode 

如果查询显示您感兴趣的数据，您只需要从并集表中获得每个产品代码的最小值。这在SQL中并不太难。

例如，可以将联合查询另存为查询，然后操作数据。

SELECT u.supfile,
       u.productcode,
       u.calcprice
FROM   MyUnion u
WHERE  (( ( u.calcprice ) IN (SELECT TOP 1 calcprice
                                 FROM   MyUnion b
                                 WHERE  b.productcode = u.productcode
                                 ORDER  BY calcprice) )); 

此查询也可以作为派生表包含，与上面的查询相同，因此您可以将各种低价与当前价格进行比较，但当两个供应商的价格相同时，此查询会重复。

决策过程可能不像更新价格最低的地方那么简单，但是，如果没有其他事情，使用sql和vba将大大减少处理时间和记录集的数量。

Answer 2

我最终使用了一个临时表（temp_prod），并将来自所有3个供应商的所有产品转储到该表中。然后我使用了这个查询：

SELECT a.productcode, a.buyprice, a.listprice, a.source FROM temp_prod AS a WHERE a.buyprice in (Select top 1 buyprice from temp_prod b where a.productcode = b.productcode order by buyprice)

打开包含每个产品代码的最便宜价格的记录集。然后我循环浏览记录集，用新的销售价格（根据购买价格计算）更新产品出口表，瞧。非常感谢你对雷莫的帮助！