1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Creating a query through vb in access

Discussion in 'Business Applications' started by mercilesstraitor, Jul 19, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. mercilesstraitor

    mercilesstraitor Thread Starter

    Joined:
    Apr 30, 2007
    Messages:
    20
    At the moment I'm trying to automate the creation of queries, how do I get VB to create a new query in Access?

    Rescripting the SQL in an existing query using VB will not do as I need to create an unknown number of queries
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    I'll give you this to look at, it is something I am working on, so it is rough around the edges. The way I built the SQL string is not really efficient (multiple variables) you can do it with one.

    This code is made to let me query multiple datatables of the same structure, but named with different System prefixes.

    I set it up on a form so I could see the code

    I have to go to a meeting so I can't provide a working sample.


    Public Sub Lotinfo()

    Dim QueryName As String
    Dim NewTable As String
    Dim SysID As String


    QueryName = "qryAppend_LotMaster"
    ' combo to choose table
    NewTable = lnktblLotInfo_
    NewTable = "lnktblLotInfo_" & Forms!form1!cobSystem
    'SysID = Right(NewTable, 2)
    SysID = Forms!form1!cobSystem


    Dim qryDef As DAO.QueryDef
    Dim strSQL As String, strQueryName As String
    Dim strCurrent As String, strReplace As String
    Dim strSelect As String, strSelect2 As String, strFrom As String, strINSERT As String
    Dim strNewT As String

    strNewT = NewTable
    strQueryName = QueryName


    strINSERT = "INSERT INTO tblLotMaster ( Stock_Ctrl__Lot_Blind_Key, Client_Code, Product_Code, Original_Receipt_Quantity, Original_Receipt_Gross_Weight, Original_Receipt_Net_Weight, Original_Receipt_Date, Pack_Date, Gross_Unit_Weight, Net_Unit_Weight, Short_filing_number, [On_Hand_Indicator_(0_1)], Deletion_Flag, Billing_Lot_Blind_Key, Reporting_Lot_Blind_Key, Component_Rotation_String, Lot_Additional_Id, Activity_Counter, Stock_Control_Component_String, Original_Receipt_Number, Component_Label_1, Component_1_value, Component_Label_2, Component_2_value, Component_Label_3, Component_3_value, Component_Label_4, Component_4_value, Component_Label_5, Component_5_value, Component_Label_6, Component_6_value, Component_Label_7, Component_7_value, Component_Label_8, Component_8_value, Component_Label_9, Component_9_value, LotAscii, DateTimeStamp, Company )"
    strSelect = " SELECT " & strNewT & ".lots_mst01, " & strNewT & ".lots_mst02, " & strNewT & ".lots_mst03, " & strNewT & ".lots_mst04, " & strNewT & ".lots_mst05, " & strNewT & ".lots_mst06, " & strNewT & ".lots_mst07, " & strNewT & ".lots_mst08, " & strNewT & ".lots_mst09, " & strNewT & ".lots_mst10, " & strNewT & ".lots_mst11, " & strNewT & ".lots_mst12, " & strNewT & ".lots_mst13, " & strNewT & ".lots_mst14, " & strNewT & ".lots_mst15, " & strNewT & ".lots_mst16, " & strNewT & ".lots_mst17, " & strNewT & ".lots_mst18, " & strNewT & ".lots_mst19, " & strNewT & ".lots_mst20, " & strNewT & ".lots_mst21, " & strNewT & ".lots_mst22, " & strNewT & ".lots_mst23, " & strNewT & ".lots_mst24, " & strNewT & ".lots_mst25, " & strNewT & ".lots_mst26, " & strNewT & ".lots_mst27, " & strNewT & ".lots_mst28, " & strNewT & ".lots_mst29, " & strNewT & ".lots_mst30, " & strNewT & ".lots_mst31, " & strNewT & ".lots_mst32, " & strNewT & ".lots_mst33, " & strNewT & ".lots_mst34, " & strNewT & ".lots_mst35, "
    strSelect2 = strNewT & ".lots_mst36, " & strNewT & ".lots_mst37, " & strNewT & ".lots_mst38, funConvertMavesKey2([lots_mst11]) AS LotAscii, Now() AS DateTimeStamp, '" & SysID & "' as Comp"
    strFrom = " FROM " & strNewT & ", tblCompany;"


    ' displays SQL string on form for debugging
    [Forms]![form1]![Text3] = strINSERT & strSelect & strSelect2 & strFrom

    Set qryDef = CurrentDb.QueryDefs(strQueryName)




    qryDef.SQL = strINSERT & strSelect & strSelect2 & strFrom
    qryDef.Close

    DoCmd.OpenQuery QueryName, acNormal, acEdit


    End Sub
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    wow(y)
     
  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551

    thanks ODP :) I can't take full credit for it entirely becuase I had help getting started, but this code I am working with is for a reporting database I have that has multiple branch tables each branch being identical table structures but named with the prefix, the user would decide to update a specific branch and the code writes the sql based on the branch, which also also adds a branch field so I could do multiple branch updates where the code would rewrite the SQL to append to the master table.

    You can upload a copy of your database and explain what you want to do. I have to make up a sample of what I have because the current one is based on ODBC linked tables
     
  5. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    I cleaned it up so you can see a working sample, It is a work in progress, and the thing that is missing is a createobject statement, because this only works if the query name exists ( which was fine for me).
     

    Attached Files:

  6. mercilesstraitor

    mercilesstraitor Thread Starter

    Joined:
    Apr 30, 2007
    Messages:
    20
    Thankss what I needed was the createobject statement
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/597616

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice