Adding query

[kimtp]

Is it possible to add a query to a Access mdb programatically in VB6? I have copied a db to a new db, placed all the fields in the new that are needed. Now I would like to add a query.

Thanx.

Kim

 

[tsdragon]

I must be misunderstanding what you want. A query is used to read/write database tables. It's not something you would ADD to a database (unless you mean a STORED query?).


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[earthandfire]

I've tested this in VB.NET and (hopefully corectly) converted it to VB5/6 - important changes from my original I've shown in bold

The name of the database:

Private Const axs2000db = "C:\Data\Testing\Access2000\db1.mdb"


The routine to add a couple of queries:

    Dim eng As DAO.DBEngine
    Dim wksp As DAO.Workspace
    Dim db As DAO.Database

    [b]SET[/b] eng = New DAO.DBEngine
    [b]SET[/b] wksp = eng.Workspaces(0)
    [b]SET[/b] db = wksp.OpenDatabase(axs2000db)

    Dim qd As DAO.QueryDef

    [b]SET[/b] qd = New DAO.QueryDef
    qd.Name = "Query1"
    qd.SQL = "SELECT * FROM Table1"
    db.QueryDefs.Append(qd)
    [b]SET[/b] qd = Nothing

    [b]SET[/b] qd = New DAO.QueryDef
    qd.Name = "Query2"
    qd.SQL = "SELECT Field1, Field2 FROM Table1 WHERE Field2 = 10"
    db.QueryDefs.Append(qd)
    [b]SET[/b] qd = Nothing

    db.Close()

    [b]SET[/b] db = Nothing
    [b]SET[/b] wksp = Nothing
    [b]SET[/b] eng = Nothing

You will need to add a reference to the appropriate DAO library

Hope this helps, and that I've converted it correctly. It should at least point you in the right direction.

By the way, Table1 has two fields:
Field1 Text 50
Field2 Number LongInteger

 

[kimtp]

Thanx earthandfire. I set this up and get a 'Operation not support for this type of object'.

Thanx.

Kim

 

[earthandfire]

???

Where?

 

[kimtp]

whooooops. Forgot to add:
'Operation not support for this type of object'
db.QueryDefs.Append (qd)

Sorry

 

[PCLewis]

There is another way that uses ADOX rather than DAO which I thought you may wish to see.

I am sure you can make use of the following code, which was written for use within Access.

This code is fairly generic and I use it to create or alter a query in Access.

Public Sub CreateOrUpdateQuery(sQueryName As String, sSql As String, Optional bPassThrough As Boolean = False)

  Dim cat As ADOX.Catalog
  Dim cmd As ADODB.Command

  Set cat = New ADOX.Catalog

  Dim lType As Long
  
  lType = adPermObjProcedure
  cat.ActiveConnection = CurrentProject.Connection

  On Error Resume Next
  ' assume the query is a procedure since most complex
  ' queries are classed procedures
  Set cmd = cat.Procedures(sQueryName).Command
  
  ' If it's not found in procedures, check if the query is a View
  If cmd Is Nothing Then
    Set cmd = cat.Views(sQueryName).Command
    If Not (cmd Is Nothing) Then lType = adPermObjView
  End If
  
  On Error GoTo 0
  
  ' if it's still not found then create it as a procedure
  If cmd Is Nothing Then
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cat.ActiveConnection
    ' we append to procedures collection but it might end up in the
    ' views collection ;)
    cat.Procedures.Append sQueryName, cmd
  End If
  
  cmd.CommandText = sSql
  If bPassThrough Then
    cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
    cmd.Properties("Jet OLEDB:Pass Through Query Connect String") = gsPassThruConnectString
  End If
  
  Select Case lType
  Case adPermObjView
    Set cat.Views(sQueryName).Command = cmd
  Case adPermObjProcedure
    Set cat.Procedures(sQueryName).Command = cmd
  End Select

  Set cat = Nothing
  Set cmd = Nothing

End Sub

 

[earthandfire]

Since I don't currently have VB6 installed, I can't check it; but this is the complete VB.NET code that works - it requires a reference to a DAO object library - maybe I converted something incorrectly for VB6:

  Private Const axs2000db As String = "C:\Data\Testing\Access2000\db1.mdb"
  Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

    Dim eng As DAO.DBEngine
    Dim wksp As DAO.Workspace
    Dim db As DAO.Database

    eng = New DAO.DBEngine
    wksp = eng.Workspaces(0)
    db = wksp.OpenDatabase(axs2000db)

    Dim qd As DAO.QueryDef

    qd = New DAO.QueryDef
    qd.Name = "Query1"
    qd.SQL = "SELECT * FROM Table1"
    db.QueryDefs.Append(qd)
    qd = Nothing

    qd = New DAO.QueryDef
    qd.Name = "Query2"
    qd.SQL = "SELECT Field1, Field2 FROM Table1 WHERE Field2 = 10"
    db.QueryDefs.Append(qd)
    qd = Nothing

    db.Close()
    db = Nothing
    wksp = Nothing
    eng = Nothing

  End Sub

Hope this helps.

 

[kimtp]

thanx for the help. Been kinda jammed. Will try your suggestions soon and let you know the results.