none
VB Form Project

    Question

  • Hello,

    I am trying to create a VB form project using visual studio community. My ultimate goal is to run Access Macro and once it is done, display current time in one of the textboxes. For this I am using "ON LOAD" event. When the form is opened, it runs the macro and closes Access. I noticed if I put "Textbox6.text = now" before opening Access code, it populates the textbox, but does not work after access code. Any reason for this?

    Dim oAccess As New Access.Application

    oAccess = CreateObject("Access.Application")

    oAccess.Visible = True

    oAccess.OpenCurrentDatabase("table.accdb", False)

    oAccess.DoCmd.RunMacro("macro1")

    oAccess.DoCmd().Quit()

    System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)

    oAccess = Nothing

    Textbox6.Text = Now

    Wednesday, November 29, 2017 4:06 PM

Answers

  • Put a break-point on the line of code prior to TextBox2.Text = Now but change it to 

    TextBox6.Text = Now.ToString

    Or perhaps

    TextBox6.Text = Now.ToShortTimeString

    Then add the two Option statements e.g.

    Option Strict On
    Option Infer Off
    
    Public Class Form1

    As TextBox6.Now is invalid


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by VB_Learner2017 Thursday, November 30, 2017 12:45 PM
    Thursday, November 30, 2017 12:41 PM
    Moderator
  • I figured the issue. Since my Access macro was quitting access file, it never went back to VB.NET code. I got rid of that and closed it using VB.NET and it works completely fine.

    Thank you all!

    • Marked as answer by VB_Learner2017 Thursday, December 07, 2017 4:48 PM
    Thursday, December 07, 2017 4:48 PM

All replies

  • You are busy with Visual Basic for Application. 

    Basic was a program language once created to teach students programming. 

    https://en.wikipedia.org/wiki/BASIC

    Gates and partners made from this MS-Basic, his employees made Visual Basic. That did sell fine so there was created Basic inside Office. 

    However, that misses in fact most of the Visual part. Still it is named Visual Basic for Applications. 

    The forum for that is :

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=isvvba

    Maybe a forum moderator can move this thread


    Success
    Cor


    Wednesday, November 29, 2017 4:43 PM
  • By the way, you can also use VSTO but then your code is complete different and it is not Windows Forms.

    Success
    Cor

    Wednesday, November 29, 2017 4:48 PM
  • Have you checked if the line ‘Textbox6.Text = Now’ is achieved? For example, simply put a message box like ‘MsgBox(Now)’, or put a breakpoint and start the program from Visual Studio. Confirm that all of the lines are executed successfully. Check the behaviour if you temporarily comment the RunMacro line.

    Also note that the CreateObject line is probably not necessary.

    • Edited by Viorel_MVP Wednesday, November 29, 2017 5:09 PM
    Wednesday, November 29, 2017 5:05 PM
  • You could try something like the following. If code is in a class this will still work.

    Private Async Sub Button1_Click(sender As Object, e As EventArgs) _
        Handles Button1.Click
    
        ' feel free to write to a TextBox or Label
        Console.WriteLine(Now.ToLongTimeString)
    
        ' keep app responsive
        Await Task.Run(Sub()
                            ' mock a process which takes time to complete
                            ' but in your case this may not be so yet worth a try.
                            ' Replace the following line with your current code
                            Threading.Thread.Sleep(5000)
                        End Sub)
    
        Console.WriteLine(Now.ToLongTimeString)
    
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, November 29, 2017 5:18 PM
    Moderator
  • I don't see any code after assigning the value of Now to TextBox6.Text so you will need to indicated what you mean by "does not work after Access code".

    Please include the Sub or Function code so we can see where it is running from.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, November 29, 2017 6:47 PM
  • By that I mean once this code runs Access Macro and closes Access, instead of updating time in textbox 6 it just leaves blank.

    Imports Access= Microsoft.office.interop.access

    public class Form1

    provate sub Form1_Load(sender as object, e as EventArgs) Handles MyBase.Load

    Dim oAccess As New Access.Application

    oAccess = CreateObject("Access.Application")

    oAccess.Visible = True

    oAccess.OpenCurrentDatabase("table.accdb", False)

    oAccess.DoCmd.RunMacro("macro1")

    oAccess.DoCmd().Quit()

    System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)

    oAccess = Nothing

    Textbox6.Text = Now

    End Sub

    End Class

    Thursday, November 30, 2017 12:29 PM
  • Put a break-point on the line of code prior to TextBox2.Text = Now but change it to 

    TextBox6.Text = Now.ToString

    Or perhaps

    TextBox6.Text = Now.ToShortTimeString

    Then add the two Option statements e.g.

    Option Strict On
    Option Infer Off
    
    Public Class Form1

    As TextBox6.Now is invalid


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by VB_Learner2017 Thursday, November 30, 2017 12:45 PM
    Thursday, November 30, 2017 12:41 PM
    Moderator
  • Thank you!
    Thursday, November 30, 2017 12:45 PM
  • I tried what you told me to do. However, it works for one access database but does not work for another. Is there any reason for that?

    Monday, December 04, 2017 1:55 PM
  • Since it works for one and not another there could be many possible differences between the two databases.

    So when you say it doesn't work for another, the first thing I would do is try this on a third database, see if it works for that one, if it does you now know the second database has something different with it.

    Are you getting errors? Have you checked permissions internally to the 2nd database? Is the 2nd database in a location you have folder permissions too?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, December 04, 2017 2:01 PM
    Moderator
  • Both files are in the same folder and both databases are the same as well. The one that works takes around 4 minutes to run the macro and the one that does not work take around 30 seconds. I am also not getting any errors while running any of them.
    Monday, December 04, 2017 2:32 PM
  • This may need someone from the Access for developer forum to look at.

    I would ask in the forum below, give them the link to this thread and provide the part about the second database not working as I see it this is internal to the database and nothing to do with the VB.NET code.

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, December 04, 2017 2:40 PM
    Moderator
  • Both files are in the same folder and both databases are the same as well. The one that works takes around 4 minutes to run the macro and the one that does not work take around 30 seconds. I am also not getting any errors while running any of them.

    Could you be encountering a run time error in your Access database? Perhaps the code is in an infinite loop? Does the Access code work when running it directly from Microsoft Access?

    You may want to consider setting a Debug breakpoint in your Access code. You can also set a Debug breakpoint in your VB.NET code (after DoCmd.Quit) in order to make sure it is returning from Access.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, December 04, 2017 2:48 PM
  • Yeah, Access code is completely error free. We have been running those macros using MS DOS windows. Since they are not very stable, we decided to use VB.NET to run the macros.
    Monday, December 04, 2017 3:19 PM
  • Yeah, Access code is completely error free. We have been running those macros using MS DOS windows. Since they are not very stable, we decided to use VB.NET to run the macros.

    So when you step through the code in Debug from Visual Studio, line by line, the line that assigns the date to the TextBox executes but it's still blank on the Form? Is this correct?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, December 04, 2017 3:47 PM
  • I realized for the file it doesn't work, when I debug step by step it actually stops debugging once it runs the macro.
    Tuesday, December 05, 2017 1:49 PM
  • I'm posting at the top level as the other responses are getting difficult to read.

    How about considering logging errors (if any) in MS-Access to a log file to rule out internal issues in Access.

    https://stackoverflow.com/questions/8823259/method-for-logging-errors-and-warnings-in-ms-access


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, December 05, 2017 2:38 PM
    Moderator
  • I realized for the file it doesn't work, when I debug step by step it actually stops debugging once it runs the macro.
    After the macro has finished executing in Access then debugging should continue in VB.NET. If it doesn't then it's because the macro is still running. If it never returns control to VB.NET then the Access app is either awaiting a response from the user or the app is hung or executing an infinite loop.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, December 05, 2017 3:31 PM
  • I figured the issue. Since my Access macro was quitting access file, it never went back to VB.NET code. I got rid of that and closed it using VB.NET and it works completely fine.

    Thank you all!

    • Marked as answer by VB_Learner2017 Thursday, December 07, 2017 4:48 PM
    Thursday, December 07, 2017 4:48 PM