Create Query in Access2000 DB using ADO

Hi everybody.

I have an Access2000 DB and i'm using ADO to access it through ODBC. I wish to drop and recreate an existing query or even better, alter its SQL statement if possible. Can anyone provide some examples of how to do this?

Please have in mind that the database at some point may be converted to SQLServer so since i'm using ODBC, i wish to do it using generic code.

TIA.

You can execute sql statements on your database. With sql, you can alter/create tables/queries.

To execute a statement, you can use your connection object (called dbConn in this example).

Code:

---

' The following would create a table.
dbConn.Execute "CREATE TABLE `dbName`.`TableName` (
  `Primary` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `Field1` VARCHAR(45) NOT NULL,
  PRIMARY KEY(`Primary`)
)
TYPE = InnoDB;"

' You can also populate a recordset from an sql statement in this way...
' (you need a recordset object to populate - dbRec)
Set dbRec = dbConn.Execute("SELECT * FROM tblMyTable")

' This will create a query (tempory in recordset only)
Set dbRec = dbConn.Execute("SELECT * FROM MyTable " & _
    "INNER JOIN MyOtherTable " & _
    "ON MyTable.Key = MyOtherTable.ForeignKey " & _
    "WHERE MyOtherTable.Key = '" & SomeValue & "'")

---

SQL is a vast subject. If there is something specific you are looking for, try doing a google search.

HTH

Quote:

---

Originally Posted by HairyMonkeyMan

You can execute sql statements on your database. With sql, you can alter/create tables/queries.

---

Thank you for your answer. Unfortunately, your examples although they are very nice, they did not help very much. I wish to create a query in an access DB like it was done in DAO (via QueryDefs collection). Your suggestion to "google" lead me to this site.

http://cma.zdnet.com/guides/qa_acces...2002_0169.html

Unfortunatelly, this didn't work either :(. CreateQueryADO sub did not work - i got the message "object or provider is not capable of performing requested operation" - i guess it's an access 2000 issue. I also could not use the suggested "CurrentDb" object because it did not state what reference you must add to the project in order to use it :(.

Anyway, I'm continuing the search and if i find anything, i'll post it here for future reference.

I got it.. I got it....

Here is how you create a query from a VB code in an Access File...

Add references to the following before executing this code....
1. Microsoft ActiveX Data Objects 2.8 Library
2. Microsoft ADO Ext. 2.8 for DDL and Security..

You can do so by going to the Project Menu and then select References. In the new dialog select the above mentioned options... and then copy the following code

Code:

---

Option Explicit
 'Declare a Connection Object
 Dim cnDB As ADODB.Connection
 'Declare a Catalog object
 Dim cQuery As ADOX.Catalog
 
 Sub Main()
        Set cnDB = New ADODB.Connection
                 
        'Open a Connection to the database (Change the C:\DB1.MDB to your own MDB File
        With cnDB
                .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\DB1.MDB"
                .Open
        End With
         
         
        Set cQuery = New ADOX.Catalog
         
         
        'Sets the active connection of the catalog object to the connection that is opened
        cQuery.ActiveConnection = cnDB
         
        'Create a command object
        Dim cmdNewQuery As ADODB.Command
        Set cmdNewQuery = New ADODB.Command
         
        'Write the query which you want to be created in Access Database
        cmdNewQuery.CommandText = "Select * from TABLE1"
         
        'This will add the above created query to the access file....
        cQuery.Views.Append "QueryFromVB", cmdNewQuery
         
        'Close the connection
        cnDB.Close
               
        Set cnDB = Nothing
        Set cQuery = Nothing
        Set cmdNewQuery = Nothing
       
 End Sub

---

This works with Access 2002..

Hope it helps you...

--Shuja

Quote:

---

Originally Posted by vb_the_best

I got it.. I got it....

---

@$^%^^$&#^&#% :mad: :mad: :mad: :mad: :mad:


I had found this code in several pages but it didn't work. Now i realize why. It doesnt' work through ODBC! That's why i got the "object or provider is not capable of performing requested operation" message.

Anyway, i discarded ODBC and it works perfectly. :)

Many thanks to both of you. :wave:

Sorry I didn't try it iusing ODBC.. But it worked in ADO.. anyway...

if it helped u.. thatz great then

and hey thanks for the rating....

Ok, after a lot of search, i finally found an almost perfect way to do the following from VB6 in an Access 2000 database:

- Delete a query using ADO (exclusivelly).
- Create a new query using ADO (exclusivelly) which unfortunatelly will NOT be visible from within Access 2000 interface.
- "Create" a new query using ADO (exclusivelly) which may be visible from within Access 2000 interface, depending on how you do the whole process.
- Alter a query's SQL statement using ADO (exclusivelly).

I'm describing the process below. I'm also posting the code i found, in my next post in this thread. Hopefully, my research will save someone's time...

Deleting the query is a rather easy process, without any workarounds. When creating a new query from within VB using ADO though, it is not visible in Access 2000. If you wish to bypass that, you can do the following:
- Create a dummy query from within Access 2000.
- Use the AccessQueryUpdate sub in my next post to modify the query's statement from within VB. Altering the query's statement does not render it invisible, only creating a new one does.

I hope this solution helps some people...

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)

Code:

---

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

---