[讨论]用ADO如何建立临时查询

阿丰

我在DAO中可以用以下代码建立临时查询：

Dim qdf As DAO.QueryDef, strSQL As String

Set qdf = CurrentDb.CreateQueryDef("临时查询", strSQL)

DoCmd.OpenQuery qdf.name, , acReadOnly

请问用ADO如何实现？

skylark

To 阿丰:'ADOX

Sub ADOCreateQuery()    Dim cat     As New ADOX.Catalog

    Dim cmd     As New ADODB.Command

   

    ' Open the catalog

    cat.ActiveConnection = "rovider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\nwind.mdb;"    ' Create the query

    cmd.CommandText = "Select * FROM Categories"

    cat.Views.Append "AllCategories", cmd

   

    Set cat = Nothing

   

End Sub

阿丰

问题已解决，谢谢楼上的兄弟。还有一个新问题，在DAO中可用下列代码删除临时查询文件：  CurrentDb.QueryDefs.Refresh

  For Each qdf In CurrentDb.QueryDefs

      If qdf.name = "临时查询" Then

         CurrentDb.QueryDefs.Delete qdf.name

         Exit For

      End If

  Next qdf在ADO中我用以下代码，老是报出错， Dim cat  As New ADOX.Catalog

Set cat.ActiveConnection = CurrentProject.Connection

cat.Views.Refresh

cat.Views.Delete "临时查询"

Set cat.ActiveConnection = Nothing

Set cat = Nothing

可有好办法？

Benjamin_luk

Set qdf = CurrentDb.CreateQueryDef("", strSQL)还以为你是真正用的是临时查询呢，如果真正的临时查询是不用删除的了。看看下面的例子： Sub ClientServerX2() Dim dbsCurrent As Database

Dim qdfBestSellers As QueryDef

Dim qdfBonusEarners As QueryDef

Dim rstTopSeller As Recordset

Dim rstBonusRecipients As Recordset

Dim strAuthorList As String ' Open a database from which QueryDef objects can be

' created.

Set dbsCurrent = OpenDatabase("DB1.mdb") ' Create a temporary QueryDef object to retrieve

' data from a Microsoft SQL Server database.

Set qdfBestSellers = dbsCurrent.CreateQueryDef("")With qdfBestSellers

  .Connect = "ODBC;DATABASE=pubs;UID=saWD=;" & _

    "DSN=Publishers"

  .SQL = "SELECT title, title_id FROM titles " & _

   "ORDER BY ytd_sales DESC"

  Set rstTopSeller = .OpenRecordset()

  rstTopSeller.MoveFirst

End With ' Create a temporary QueryDef to retrieve data from

' a Microsoft SQL Server database based on the results from

' the first query.

Set qdfBonusEarners = dbsCurrent.CreateQueryDef("")With qdfBonusEarners

  .Connect = "ODBC;DATABASE=pubs;UID=saWD=;" & _

   "DSN=Publishers"

  .SQL = "SELECT * FROM titleauthor " & _

   "WHERE title_id = '" & _

   rstTopSeller!title_id & "'"

  Set rstBonusRecipients = .OpenRecordset()

End With ' Build the output string.

With rstBonusRecipients

  Do While Not .EOF

   strAuthorList = strAuthorList & "    " & _

    !au_id & ":  $" & (10 * !royaltyper) & vbCr

   .MoveNextLoop

End With ' Display results.

MsgBox "lease send a check to the following " & _

  "authors in the amounts shown:" & vbCr & _

  strAuthorList & "for outstanding sales of " & _

  rstTopSeller!Title & "." rstTopSeller.Close

dbsCurrent.CloseEnd Sub[em05]