[AccessD] database window appearing when I run code in VBE

[Jurgen Welz]

I would likely have cleared the table with a delete query followed by an insert.  Since this sounds like temp table stuff, I would have done it in an external mdb if using mdb's to house the data in order to combat bloat.  Perhaps copy over a new mdb with the table pre-created to a pre-linked location with a path or file name derived from the user login name.

I've been looking at using a series of pop ups and hiding the database window.  There's a ton of messed up nonsense out on the web about that and some approaches require everything to be modal to work.  I find it simplest just to move the database window off screen.

Declare the following API function above the procedures in a code window:

Declare Function SetWindowPos Lib "user32" (ByVal hWnd As Long, ByVal hWndInsertAfter As Long, _
    ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long

'and declare a couple of constants in the same module

    Public Const HWND_TOP = 0
    Public Const SW_SHOWWINDOW = &H40

In your code below, include the following line of code near the start:

    SetWindowPos Application.hWndAccessApp, HWND_TOP, 50, -50, 50, 50, SW_SHOWWINDOW

This sets the window to 50 pixels tall and moves it to a position 50 pixels above the screen so it is effectively hidden.  You can right-click on the task bar icon and select move in order to enable dragging the window into an accessible area when done or you can set the window position to where it was (Api call to GetWindowRect, store the top, left, height and width and set them back by restoring the values for the database window size and position in another call to SetWindowPos.


Ciao
Jürgen Welz
Edmonton, Alberta

> Date: Fri, 11 Sep 2015 22:55:28 -0400
> From: bensonforums at gmail.com
> To: accessd at databaseadvisors.com
> Subject: [AccessD] database window appearing when I run code in VBE
> 
> Ac2013.
> 
> Running a macro from the VBA Editor, using F5, I am destroying a database
> object (table) and recreating from code. In the process, while running from
> the VBE, the VBE window loses focus and the DB window appears.
> 
> This wouldn't be an issue at runtime when the UI is all the user would be
> seeing, but it is annoying when debugging.
> 
> Anyone have a clue what is causing this and if it is avoidable?
> 
> Option Compare Database
> Option Explicit
> Sub CreatePeriods()
> Dim rst As DAO.Recordset
> Dim db As DAO.Database
> Dim SQL As String
> Dim i As Long
> Dim rstItems As DAO.Recordset
> Set db = CurrentDb
> On Error Resume Next
> DoCmd.DeleteObject acTable, "Periods"
> On Error GoTo 0
> DoCmd.CopyObject CurrentDb.Name, "Periods", acTable, "PeriodsTemplate"
> SQL = "SELECT Dates.Day, TimeSlots.SlotBegin, TimeSlots.SlotEnd, Dates.ID,
> TimeSlots.SlotID"
> SQL = SQL & " From TimeSlots, Dates"
> SQL = SQL & " ORDER BY Dates.ID, TimeSlots.SlotID"
> Set rstItems = db.OpenRecordset(SQL)
> With rstItems
>     While Not .EOF
>         SQL = "Insert into periods (Dat,TimeSlot,Period) Values ("
>         SQL = SQL & "#" & .Fields("Day").Value & "#"
>         SQL = SQL & "," & .Fields("SlotID").Value
>         SQL = SQL & ",'" & Format(.Fields("SlotBegin").Value, "hh:mm AMPM")
> & " - " & Format(.Fields("SlotEnd").Value, "hh:mm AMPM") & "')"
>         db.Execute SQL
>         i = i + 1
>         Debug.Print i & " records inserted"
>         .MoveNext
>     Wend
> End With
> 
> 
> End Sub
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com