将记录从直通查询追加到本地表 [英] Append Records From Passthrough Query to Local Table

问题描述

我有一个Access数据库，并且正在使用传递查询来从AS400表返回记录.连接字符串和直通查询工作正常，但是现在我试图将p-t查询的结果填充到db中的本地表中，并且我的代码正在超时.这是我第一次尝试使用ADO，因此我以我不是100％知道自己在做什么！"来拒绝我的代码.您能否看一下，看看是否有明显的地方我做错了?任何方向将不胜感激.预先谢谢你.

I have an Access Database and I'm using a pass through query to return records from an AS400 table. The connection string and pass through query work fine, but now I'm trying to populate the results of the p-t query into a local table within the db and my code is timing out. This is my first attempt at ADO so I'm disclaiming my code with "I'm not 100% sure what I'm doing!". Could you look at this and see if there is something obvious that I'm doing wrong? Any direction would be appreciated. Thank you in advance.

    Sub mod_ADODBConnect()
      Const NewTableName = "MyNewTable"
      Dim cn As ADODB.Connection
      Dim rs As ADODB.Recordset
      Dim db As DAO.Database
      Dim sSQL1 As String
      Dim sSQL2 As String

      sSQL1 = "SELECT ITMNUM, ITMDS, ITPKDS, MJCMCD, SBCMCD, STATUS, PRITIN, OGEXDT         from PDBLLIB007.BLPMST07"
      sSQL2 = "INSERT INTO ' & NewTableName & ' SELECT [" & sSQL1 & "].* from [" & sSQL1 & "]"

      Set cn = New ADODB.Connection
      cn.Open "Driver={Client Access ODBC Driver (32-bit)};" & _
        "System=DC007; Uid=XXXXX; Pwd=XXXXXX; MgDSN=0; ConnType=2;" & _
        "BlockSize=512; MaxFieldLen=2048; LazyClose=1; Prefetch=1; QueryTimeOut=0;     Translate=1"

     Set rs = New ADODB.Recordset
     rs.Open sSQL1, cn, adOpenDynamic, adLockOptimistic

     Do While Not rs.EOF
        rs.MoveNext
     Loop

   Set db = CurrentDb
   db.Execute ("sSQL2")

   rs.Close
   cn.Close

   Set rs = Nothing
   Set cn = Nothing
   Set db = Nothing
   End Sub

推荐答案

您有一个传递查询，该查询可以正常工作并返回所需的行.现在，您要将这些行存储在新的本地(Jet/ACE)表中.在我看来，一种更简单的方法是在新的"制作表"查询中使用传递作为数据源.

You have a pass-through query which works fine and returns the rows you want. Now you want to store those rows in a new local (Jet/ACE) table. Seems to me a simpler approach would be to use the pass-through as the data source in a new "make table" query.

SELECT * INTO MyNewTable FROM YourPassThruQuery;

糟糕，您似乎打算将这些行附加到现有表中.

Oops, looks like you meant to append those rows to an existing table.

INSERT INTO MyNewTable
SELECT * FROM YourPassThruQuery;

如果表结构不匹配，则可以为两个表使用字段列表.

If the table structures don't match, you can use field lists for both tables.

INSERT INTO MyNewTable (fld1, fld2)
SELECT first_field, second_field FROM YourPassThruQuery;

这篇关于将记录从直通查询追加到本地表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！