Create Query in Access2000 DB using ADO

[Nick A.]

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.

[HairyMonkeyMan]

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).

' 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

[Nick A.]

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.

[Shuja Ali]

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

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

[Nick A.]

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

@$^%^^$&#^&#%


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.

[Shuja Ali]

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....

[Nick A.]

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...

[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