Office中国论坛/Access中国论坛

标题: [讨论]用ADO如何建立临时查询 [打印本页]

作者: 阿丰 时间: 2004-11-9 02:58
标题: [讨论]用ADO如何建立临时查询
我在DAO中可以用以下代码建立临时查询：

Dim qdf As DAO.QueryDef, strSQL As String

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

DoCmd.OpenQuery qdf.name, , acReadOnly

请问用ADO如何实现？

作者: skylark 时间: 2004-11-9 04:05
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

作者: 阿丰 时间: 2004-11-9 22:17
问题已解决，谢谢楼上的兄弟。还有一个新问题，在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 时间: 2004-11-10 01:10
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=sa

WD=;" & _

"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=sa

WD=;" & _

"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]

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)