Create Query in Access2000 DB using ADO

[Nick A.]

To use the code in this post, you must add the following two References in your VB6 Project:

- Microsoft ActiveX Data Objects x.x Library (ADODB)
- Microsoft ADO Ext. x.x for DDL and Security (ADOX)

Function AccessQueryDrop(PConnection As ADODB.Connection, ByVal PQueryName As String) As Boolean
  Dim objCatalog As ADOX.Catalog
  
  On Error GoTo ErrorHandler
  
  Set objCatalog = New ADOX.Catalog
  objCatalog.ActiveConnection = PConnection
  
  objCatalog.Views.Delete PQueryName
  
  Set objCatalog = Nothing
  
  AccessQueryDrop = True
  Exit Function
  
ErrorHandler:
  MsgBox Error()
  AccessQueryDrop = False
End Function

Function AccessQueryAdd(PConnection As ADODB.Connection, ByVal PQueryName As String, ByVal PQuerySQLStat As String) As Boolean
  Dim objCatalog As ADOX.Catalog
  Dim objCommand As ADODB.Command
  
  On Error GoTo ErrorHandler
  
  Set objCatalog = New ADOX.Catalog
  objCatalog.ActiveConnection = PConnection
   
  Set objCommand = New ADODB.Command
  objCommand.CommandType = adCmdText
  objCommand.CommandText = PQuerySQLStat
  
  objCatalog.Views.Append PQueryName, objCommand
  
  Set objCommand = Nothing
  Set objCatalog = Nothing
  
  AccessQueryAdd = True
  Exit Function
  
ErrorHandler:
  MsgBox Error()
  AccessQueryAdd = False
End Function

Function AccessQueryUpdate(PConnection As ADODB.Connection, ByVal PQueryName As String, ByVal PQuerySQLStat As String) As Boolean
  Dim objCatalog As ADOX.Catalog
  Dim objCommand As ADODB.Command
  
  On Error GoTo ErrorHandler
  
  Set objCatalog = New ADOX.Catalog
  objCatalog.ActiveConnection = PConnection
   
  Set objCommand = objCatalog.Views(PQueryName).Command
  objCommand.CommandType = adCmdText
  objCommand.CommandText = PQuerySQLStat
  
  Set objCatalog.Views(PQueryName).Command = objCommand
  
  Set objCommand = Nothing
  Set objCatalog = Nothing
  
  AccessQueryUpdate = True
  Exit Function
  
ErrorHandler:
  MsgBox Error()
  AccessQueryUpdate = False
End Function