Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

creating Append queries in VB

mikecoonmikecoon Member Posts: 61
Okay, I know how to create the Append query using Access.
Now my question is can i create the Append query using the Visual Basic language? Basically, I want to give the user the option of choosing which database he wants to append to. so I want him to choose the database, then when enter is click, I want the VB code to create the Append query using the database the user selected.
mikecoon


Comments

  • dokken2dokken2 Member Posts: 532
    : Okay, I know how to create the Append query using Access.
    : Now my question is can i create the Append query using the Visual Basic language? Basically, I want to give the user the option of choosing which database he wants to append to. so I want him to choose the database, then when enter is click, I want the VB code to create the Append query using the database the user selected.
    : mikecoon
    :
    :
    :
    yes, with DAO and the querydefs collection. my example uses a simple SELECT but you can create any kind of query including INSERT to append records-

    [code]
    'CREATE DYNAMIC SQL QUERY
    Private Sub cmdQuery_Click()
    Dim DB1 As DAO.Database
    Dim Qry As DAO.QueryDef
    Dim SQL As String

    On Error GoTo errQry

    'QUERY NAME
    Const qryName = "MyQuery"

    'SQL TO DO SOMETHING
    SQL = "SELECT * FROM tbl1;"
    'SQL = "SELECT * FROM tbl2;"

    Set DB1 = CurrentDb
    'CREATE QUERY
    Set Qry = DB1.CreateQueryDef(qryName, SQL)
    'OPEN QUERY
    DoCmd.OpenQuery qryName, acViewNormal

    xit:
    'DELETE QUERY
    DB1.QueryDefs.Delete qryName
    xit2:
    Set Qry = Nothing
    Set DB1 = Nothing
    Exit Sub

    errQry:
    If Err = 3011 Then Resume Next 'QUERY DOES NOT EXIST
    If Err = 3012 Then DB1.QueryDefs.Delete qryName: Resume 'DELETE EXISTING QUERY
    If Err = 3265 Then Exit Sub
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume xit
    End Sub
    [/code]
  • mikecoonmikecoon Member Posts: 61
    Well, what i had in mind is this. First I create the Append query in Access and have it append to another database by clicking on the another database option and then browsing for the database under file name.
    Now what I would like to do is change the file name using visual basic.
    Lets say my Append query is named Test. Is there a way to use visual basic to open the query "Test" and then change the file name of "Test" before running the query?

    mike coon
  • dokken2dokken2 Member Posts: 532
    : Well, what i had in mind is this. First I create the Append query in Access and have it append to another database by clicking on the another database option and then browsing for the database under file name.
    : Now what I would like to do is change the file name using visual basic.
    : Lets say my Append query is named Test. Is there a way to use visual basic to open the query "Test" and then change the file name of "Test" before running the query?
    :
    : mike coon
    :


    use the sql "IN" keyword to specify the external database-

    INSERT INTO [table1] IN 'E:External.mdb'
    SELECT tbl2.*
    FROM tbl2;

    its possible to modify an existing query but easier to delete it and create a new one with same name that refers to another database.

    one thing to remember, if you use access 2002 or newer and multiple users open the database, they will *not* be able to change any database objects [ie: delete/create a query] since the mdb is opened in "shared mode". they need a front-end on their pc that is linked to the data tables on the server, each user then has exclusive-mode to the mdb on their pc and can change objects in their own db. [only one-user or access97 or older and this is not an issue]
Sign In or Register to comment.