CODEWITHSUNDEEP
vbams-accessms-word
Wayne Whittenberg
Wayne Whittenberg

Reputation: 13

How Do I Keep Access Window Invisible When Queried from Word VBA

I can't keep Access Application Window from displaying when accessing an Access RecordSet from Word VBA.

I have VBA code in Word that creates an Access RecordSet from SQL, manipulates the RecordSet and then closes the Database. I have used Application.ScreenUpdating = False and set the Access Database object .Visible = False, but the Access Application Window keeps flashing on screen for an instant when the code runs.

Code fragment:

Dim acc as Access.Application
Dim db as Database
Dim rst as Recordset

Application.ScreenUpdating = False
Set acc = New Access.Application

With acc
   .Visible = False
   .OpenCurrentDatabase stAccPath
   Set db = .CurrentDb
   Set rst = db.OpenRecordset(stSQL)

Other code for manipulating recordset here.

   .Quit
End With
set rst = Nothing
Set acc = Nothing
Application.ScreenUpdating = True

What I want to happen is to have Access running invisibly in the background when this code is executed, but in practice, the Access Application window appears on screen for a second before disappearing.

Upvotes: 1

Views: 480

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

If the code does not need to interact with the user in the Access environment then it's better to not open the database at all. Instead, use an ADO connection retrieve the data directly from data storage, rather than opening the database in Access. This will not only avoid the problem with handling the (unwanted) Access application window, it will also be faster.

Here's some sample code for connecting to an Access database (both mdb and accdb connection strings are provided). 

Sub AdoConnectAccess()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConn As String, sSQL As String

    'sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb"
    'sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.mdb"
    sSQL = "SELECT * From [Table Name]"
    Set conn = New ADODB.Connection
    conn.ConnectionString = sConn
    conn.Open
    Set rs = conn.Execute(sSQL)
    rs.MoveFirst
    Debug.Print rs.RecordCount, rs.Fields.Count
    Do While Not rs.EOF
        Debug.Print rs.Fields("Vorname").value
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

Upvotes: 1

Related Questions