dcsimg
Results 1 to 19 of 19

Thread: how can i in visual basic console track date that is being inserted in table using co

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2019
    Posts
    24

    how can i in visual basic console track date that is being inserted in table using co

    how can i in visual basic console track date that is being inserted in table using compiler?
    how to set up Debug.Print to watch what date take from what tables?
    give me instracrion

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,605

    Re: how can i in visual basic console track date that is being inserted in table usin

    https://docs.microsoft.com/en-us/off...p/print-method

    Just place some debug.print statements in your code
    In case when working with queries you can assign the query first to string, then debug.print the string, then the execute statement.
    The output will be printed in the "Immediate" Window

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jun 2019
    Posts
    24

    Re: how can i in visual basic console track date that is being inserted in table usin

    how to make output from this lines below using Debug.Print?
    we need values Итого.Вес in immdiate windows for ever cicle of the program.

    Do While Not .EOF
    SysCmd acSysCmdSetStatus, "Обработка таблицы: " & !nm_tabl
    CurrentDb.QueryDefs("Вес").SQL = "UPDATE " & !nm_tabl & " INNER JOIN Итого ON (" & !nm_tabl & ".Поле1 = Итого.Артикул) AND (" & !nm_tabl & ".Поле2 = Итого.Фирма) SET Итого.Вес = [" & !nm_tabl & "].[Поле3]"
    CurrentDb.Execute "Вес"
    Last edited by daveramsey; Aug 12th, 2019 at 09:28 AM.

  4. #4
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,605

    Re: how can i in visual basic console track date that is being inserted in table usin

    Is this Access VBA?
    Beside this, you can’t see what the intermediate results of the SQL command.
    You can only print the sql statement on the screen.
    When the query is executing you can not display the progress of records processed

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jun 2019
    Posts
    24

    Re: how can i in visual basic console track date that is being inserted in table usin

    but can i find what values and where taken and inserted in what table?
    can i use this method

    Dim MyVar
    MyVar = "Come see me in the Immediate pane."
    Debug.Print MyVar

    for example i want to know what values are entered using this code


    Code:
    Private Sub Вес_Click()
    Dim rs As ADODB.Recordset, Фирма1 As String
    Фирма1 = Nz(DLookup("Фирма1", "Итого"))
    Set rs = New ADODB.Recordset
    rs.Open "select nm_tabl From tb_tabl Where vkl = 3 order By nm_tabl", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTableDirect
    With rs
    Do While Not .EOF
    SysCmd acSysCmdSetStatus, "Обработка таблицы: " & !nm_tabl
    CurrentDb.QueryDefs("Вес").SQL = "UPDATE " & !nm_tabl & " INNER JOIN Итого ON (" & !nm_tabl & ".Поле1 = Итого.Артикул) AND (" & !nm_tabl & ".Поле2 = Итого.Фирма) SET Итого.Вес = [" & !nm_tabl & "].[Поле3]"
    CurrentDb.Execute "Вес"
    .MoveNext
    Loop
    If Фирма1 <> "" Then
    .MoveFirst
    Do While Not .EOF
    SysCmd acSysCmdSetStatus, "Обработка таблицы: " & !nm_tabl
    CurrentDb.QueryDefs("Вес").SQL = "UPDATE " & !nm_tabl & " INNER JOIN Итого ON (" & !nm_tabl & ".Поле1 = Итого.Артикул) AND (" & !nm_tabl & ".Поле2 = Итого.Фирма1) SET Итого.Вес = [" & !nm_tabl & "].[Поле3]"
    CurrentDb.Execute "Вес"
    .MoveNext
    Loop
    End If
    End With
    rs.Close
    MsgBox ("Вес сформирован")
    
    End Sub

  6. #6
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,605

    Re: how can i in visual basic console track date that is being inserted in table usin

    Before running the update query you could examine the results of the first select statement.
    Code:
    Set rs = New ADODB.Recordset
    rs.Open "select nm_tabl From tb_tabl Where vkl = 3 order By nm_tabl", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTableDirect
    
    With rs
      .MoveFirst
      Do Until .EOF
        ' You have selected a single field
        Debug.Print rs.Fields(0) 
        .MoveNext
      Loop
    End With

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jun 2019
    Posts
    24

    Re: how can i in visual basic console track date that is being inserted in table usin

    this query gave the list of tables but how do we get values that where selected by the query?

    TOYOTA_1_2011
    TOYOTA_2_2012
    TOYOTA_3_800
    TOYOTA_4_AUTODUBAI
    TOYOTA_5_AUTOPITER
    TOYOTA_6_BASE2014
    TOYOTA_7_IMPEX
    TOYOTA_8_P
    TOYOTA_9_UA
    TOYOTA_91_UAE
    TOYOTA_92_WP
    TOYOTA_93_Z
    TOYOTA_94_TS
    TOYOTA_95_TS18
    VOLVO_1_BASE2014
    VOLVO_2_Z
    VOLVO_3_TS18
    VW_1_AUTOPITER
    VW_2_BASE2014
    VW_3_EXEGO
    VW_4_GER
    VW_5_TS18

  8. #8
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,605

    Re: how can i in visual basic console track date that is being inserted in table usin

    I'm not sure I understand what is going on.

    Is "tb_tabl" a table holding other table names?
    So the output shown are different tables in you DB.

    Code:
    Do While Not .EOF
      ' According to the MS-Access documentation the next line is updating a status or progress bar
      ' https://docs.microsoft.com/en-us/office/vba/api/access.application.syscmd
      SysCmd acSysCmdSetStatus, "Обработка таблицы: " & !nm_tabl
    
      ' In this line you you update one of the tables eg "TOYOTA_1_2011" with a inner join on "Итого"
      ' The field "TOYOTA_1_2011.Поле1" is matched against "Итого.Артикул" AND "TOYOTA_1_2011.Поле2" is matched against "Итого.Фирма"
      ' For the matched record "Итого.Вес" is updated with the content of "TOYOTA_1_2011.Поле3"
      CurrentDb.QueryDefs("Вес").SQL = "UPDATE " & !nm_tabl & " INNER JOIN Итого ON (" & !nm_tabl & ".Поле1 = Итого.Артикул) AND (" & !nm_tabl & ".Поле2 = Итого.Фирма) SET Итого.Вес = [" & !nm_tabl & "].[Поле3]"
      CurrentDb.Execute "Вес"
      .MoveNext
    Loop

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Jun 2019
    Posts
    24

    Re: how can i in visual basic console track date that is being inserted in table usin

    we have access program how do i find where it takes values to insert in fild 0.02 from which table for element PT92542130
    tb_table is table that contain list of tables
    Name:  1.jpg
Views: 64
Size:  26.2 KBName:  1.jpg
Views: 64
Size:  26.2 KB

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jun 2019
    Posts
    24

    Re: how can i in visual basic console track date that is being inserted in table usin

    we have access program how do i find where it takes values from which table to insert into result file field 0.02 from which table for element PT92542130



    tb_table is table that contain list of tables
    Name:  1.jpg
Views: 64
Size:  26.2 KB

  11. #11
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,605

    Re: how can i in visual basic console track date that is being inserted in table usin

    That's what I tried to explain in "green" in the code section:

    In this line you you update one of the tables eg "TOYOTA_1_2011" with a inner join on "Итого"
    The field "TOYOTA_1_2011.Поле1" is matched against "Итого.Артикул" AND "TOYOTA_1_2011.Поле2" is matched against "Итого.Фирма"
    For the matched record "Итого.Вес" is updated with the content of "TOYOTA_1_2011.Поле3"
    Just write some SQL statements by hand and try them in MS-Access

    This would be the query for the first table name returned
    Code:
    SQL = "UPDATE TOYOTA_1_2011 INNER JOIN Итого ON (TOYOTA_1_2011.Поле1 = Итого.Артикул) AND (TOYOTA_1_2011.Поле2 = Итого.Фирма) SET Итого.Вес = [TOYOTA_1_2011].[Поле3]"
    Replace the UPDATE with a SELECT and display the field TOYOTA_1_2011.Поле3

    It would be something like this (top of my head, untested of course!!)
    Code:
    sSQL = "SELECT Поле3 FROM TOYOTA_1_2011 INNER JOIN Итого WHERE (TOYOTA_1_2011.Поле1 = Итого.Артикул) AND (TOYOTA_1_2011.Поле2 = Итого.Фирма)"

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Jun 2019
    Posts
    24

    Re: how can i in visual basic console track date that is being inserted in table usin

    this line do not return any output
    sSQL = "SELECT Поле3 FROM TOYOTA_1_2011 INNER JOIN Итого WHERE (TOYOTA_1_2011.Поле1 = Итого.Артикул) AND (TOYOTA_1_2011.Поле2 = Итого.Фирма)"

  13. #13
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,605

    Re: how can i in visual basic console track date that is being inserted in table usin

    Did you execute the query in Access??

    https://support.office.com/en-us/art...a7f09f18#bmsql

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Jun 2019
    Posts
    24

    Re: how can i in visual basic console track date that is being inserted in table usin

    no output? empty field
    Name:  1.jpg
Views: 64
Size:  29.4 KB

  15. #15
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,605

    Re: how can i in visual basic console track date that is being inserted in table usin

    Sorry I can't read the screenshot, to small.
    Only post relevant pieces of the screen, not the complete screen

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Jun 2019
    Posts
    24

    Re: how can i in visual basic console track date that is being inserted in table usin

    according to instruction do not run

    picture previewer
    https://drive.google.com/file/d/1EMe...WM0F1w2yC/view
    Last edited by daveramsey; Aug 13th, 2019 at 05:11 AM.

  17. #17
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,605

    Re: how can i in visual basic console track date that is being inserted in table usin

    You should execute the query from an open connection or directly in MS-Access

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Jun 2019
    Posts
    24

    Re: how can i in visual basic console track date that is being inserted in table usin

    how do we do it? instruction

  19. #19
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,605

    Re: how can i in visual basic console track date that is being inserted in table usin

    Uh, you have already shown some code to read a table in a recordset.
    Just do the same...

    Code:
    Private Sub Command1_Click()
    Dim sSQL As String
    Dim oRS as ADODB.Recordset
    
    
    sSQL = "SELECT Поле3 FROM TOYOTA_1_2011 INNER JOIN Итого WHERE (TOYOTA_1_2011.Поле1 = Итого.Артикул) AND (TOYOTA_1_2011.Поле2 = Итого.Фирма)"
    
    Set oRS = New ADODB.Recordset
    oRS.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTableDirect
    With oRS
      Do Until .EOF
        Debug.Print oRS.Field(0)
        .MoveNext
      Loop
    End With

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width