Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Location
    Thailand
    Posts
    12

    Unanswered:

    First:- Pradon in my english.
    Hi I am a thai man. I am working and learning to program in "Visual basic 6" and ms-Access. I am absolute new for dbforums and trying to learn how to use and contract with other. Advice me please

  2. #2
    Join Date
    Feb 2004
    Posts
    90
    You are going to have to explain more as to what you need and what you are asking.

    Maybe you may want to use a translation service to do this. Or find a thai speaking database forum.

    8 replies, but none showing....I'm assuming these have been deleted/moved due to flaming? People smelling a troll?

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ahhhh ... That would explain it... I wonder if there is a SPECIFIC QUESTION floating around here somewhere ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Feb 2004
    Posts
    90
    I'm not with you M Owen?

    Except that the poster hasn't asked a question at all. But if english isn't his/hers first language then it must be difficult to ask a question.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Goldy
    I'm not with you M Owen?

    Except that the poster hasn't asked a question at all. But if english isn't his/hers first language then it must be difficult to ask a question.
    Rhetorical Goldy ... Strictly rhetorical ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Aug 2006
    Location
    Thailand
    Posts
    12
    Oh! I am sorry for my first post in dbforums and thank's for all comment. I was search for "delete query" or "delete appended query" and found this keyword in head topic without any description but massage from webmaster for introduce myself

    This is my ploblem
    I can delete tables in an ms-access2000 database using ADOX.Table: for parth of my code

    For Each objTable In cat.Tables
    If objTable.Name = strTableName Then
    cat.Tables.Delete strTableName
    Exit For
    End If
    Next
    cat.Tables.Refresh
    It work well but when I want to delete query by use ADOX.View and cat.views the query still exists and my function return blank err'message box. what's wrong? Has other way to delete query? Has easier way?
    Thank's for all help

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I quickly adjusted some old ADOX code and it worked fine:

    Code:
    Sub ReadView()
        
        Dim v As ADOX.View
        Dim d As ADOX.Catalog
        Dim c As ADODB.Connection
        Dim i As Integer
        
        Set d = New ADOX.Catalog
       
    'Just a function that returns an ADO connection
        Set c = OpenAdoMSAccessConn("C:\MyBE.mdb")
        
        Set d.ActiveConnection = c
        
        For Each v In d.Views
                   
            Debug.Print v.Name
            
            If v.Name = "Query1" Then
                
                d.Views.Delete v.Name
                
            End If
            
        Next v
        
        Set d.ActiveConnection = Nothing
        
        c.Close
        Set c = Nothing
        Set v = Nothing
        Set d = Nothing
        
    End Sub
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I think simple selects can be found in the views collection, but parameterized queries, union queries, action queries etc, you need the procedures collection for.

    Here's some ADO samples that might be usable, seing you're using ADO/ADOX
    Code:
    Function DeleteTableOrQuery(v_strObjectName) As Boolean
        
        Dim cn                  As ADODB.Connection
        Dim rs                  As ADODB.Recordset
        Dim fDeleted            As Boolean
      
        Set cn = CurrentProject.Connection
        Set rs = cn.OpenSchema(adSchemaViews, _
            Array(Empty, Empty, v_strObjectName))
        If (Not rs.EOF) Then
            cn.Execute "DROP VIEW [" & v_strObjectName & _
                "]", , adExecuteNoRecords + adCmdText
                fDeleted = True
        End If
        If (Not fDeleted) Then
            Set rs = cn.OpenSchema(adSchemaProcedures, _
                Array(Empty, Empty, v_strObjectName))
            If (Not rs.EOF) Then
                cn.Execute "DROP PROC [" & v_strObjectName & _
                    "]", , adExecuteNoRecords + adCmdText
                fDeleted = True
            End If
        End If
        If (Not fDeleted) Then
            Set rs = cn.OpenSchema(adSchemaTables, _
                Array(Empty, Empty, v_strObjectName))
            If (Not rs.EOF) Then
                cn.Execute "DROP TABLE [" & v_strObjectName & _
                    "]", , adExecuteNoRecords + adCmdText
                fDeleted = True
            End If
        End If    
    
        DeleteTableOrQuery = fDeleted
        
    End Function
    I think, hovewer, that will need some errorhandling, if check constraints are used on a table, or the table participates in a relationship.

    So, with Jet 4.0, you don't need ADOX, you can execute DDL on an ADO connection, and I think

    <yourcon>.execute "DROP PROC <queryname>" should work on any queries - even those created with CREATE VIEW DDL syntax.

    So a couple of brute force versions, could be

    dim cn as adodb.connection
    set cn = currentproject.connection
    on error resume next
    cn.execute "DROP PROC qryMyQuery", , adcmdtext+adexecutenorecords
    if err.number <> 0 then
    ' didn't exist
    err.clear
    end if

    or DAO just
    on error resume next
    currentdb.querydefs.delete "qryMyQuery"
    if err.number <> 0 then
    ' didn't exist
    err.clear
    end if
    Roy-Vidar

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by RoyVidar
    <yourcon>.execute "DROP PROC <queryname>" should work on any queries - even those created with CREATE VIEW DDL syntax.
    Ah - interesting - my first thought was DDL but didn't know the syntax for Access queries. PROC huh.

    EDIT - BTW - I wouldn't describe that as brute force. Simple and efficient IMO.
    Last edited by pootle flump; 08-09-06 at 07:34.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I think you can even use "DROP TABLE <whatever object name>"

    I don't know how correct it is, but I use "brute force" to describe throwing an exception in stead of looping a collection.
    Roy-Vidar

  11. #11
    Join Date
    Aug 2006
    Location
    Thailand
    Posts
    12

    Thank's for all reply.

    Thank's for all reply. Ok. My ploblem is solved (in many way) and that's increase my knowlage.
    1. My action query can't be found in views collection : It's my mistake
    2. Try to use DAO again by checking dao version then I can use dao with ms-access2000
    (Use dao3.6 by upgrade my vb6 with sp6)
    3.New function for me: "DROP PROC ..."
    Again. Thank's very much.
    Last edited by polngpole; 08-09-06 at 13:25.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •