Link to home
Start Free TrialLog in
Avatar of Fernando Garcia Soto
Fernando Garcia Soto

asked on

Open Access database with powershell script in hidden mode.

Hello everyone, I have an Access database, I currently have a powershell script that opens the database to be able to do data import.

$Acc = New-Object –comObject Access.Application
$dest = "${datadir}bbdd.temp"
If (Test-Path $dest) { Remove-Item $dest }
$Acc.CompactRepair($database,$dest,$true)
Remove-Item $database
Move-Item $dest $database

$Acc.OpenCurrentDataBase($database)
$conn = open_database -file $database
.
.
script to import data
.
.
close_database -connection $conn
$Acc.CloseCurrentDatabase()
$Acc.Quit()

Now I have added a login form with username and password, "I have the problem" that when running the script does two things I do not want it to do:
1.- the access database is opened, I would like this import to be made in a hidden mode, that the access window does not appear
2.- ask me the user and password that has the login form, I would like to take the login data of some text file.

I would greatly appreciate your help, they have helped me to solve other issues previously.

Thank you very much
Avatar of Bill Prew
Bill Prew

You can keep Access hidden by adding the following right after the New-Object.

$Acc.visible = $true

However, if you do that, you can't use Sendkeys to fill in the user id and password (which isn't a great idea anyway).  You will have to add logic to the Access database that somehow knows if it should display the login form, or grab the credentials from a disk file.  One approach could be to check if the application is hidden when Access starts and take different logic depending.


»bp
Avatar of Fernando Garcia Soto

ASKER

I added what you told me but it did not work for me. Regarding your comment, how could I do so that Access takes me the form if it does not run in hidden mode ?, and how do I not to ask me for username and password when running in hidden mode?
Sorry, that line should have been:

$Acc.visible = $false

As far as the decision logic, something like this:

    If Application.Visible = True Then
        ' Show your form here
    Else
        ' Read from file here
    End If

Open in new window


»bp
Hi Bill.

Powershell is taking $false as a variable, should I declare it with some data? or am I putting it wrong?

$Acc = New-Object –comObject Access.Application
$Acc.visible = $false

And

$Acc = New-Object –comObject Access.Application
$Acc.visible = false

It´s not working in any way
The following works fine here for me, Access is not visible and the database is opened, and then closed...  Can you elaborate on what is going on there?  If the Access Database is popping up a form on load then maybe that is overriding the visible setting.  I also don't have the rest of your code so can't tell if anything else is interfering.

$true and $false are built in to Powershell.

$database = 'c:\temp\test.mdb'
$Acc = New-Object –comObject Access.Application
$Acc.Visible = $false
$Acc.OpenCurrentDataBase($database)
$Acc.CloseCurrentDatabase() 
$Acc.Quit()

Open in new window


»bp
If you post the Access database here (remove any sensitive data from tables if needed) I will be more specific on how to manage the logon screen if Access is not visible.


»bp
Hi.

This is my database, you have username and password to access DB, but you can access it with the shift key bypass.
db_for_test.accdb
I have placed the instruction as you indicated in a DB without access form and it works. Now when the script is executed in the DB with authentication, it opens the DB but it asks me for the username and password of the form, I tried with this but it does not work:


$database = 'D:\db.accdb'
$Acc = New-Object –comObject Access.Application
$Acc.Visible = $false
$Acc.OpenCurrentDataBase($database)
$Acc.DoCmd.OpenForm('Form_Login_bd')
$AccForm = $Acc.Forms.item('Form_Login_bd')
$AccForm.Controls.Item("txt_login").value = "Prueba"
$AccForm.Controls.Item("txt_contrasena").value = "Prueba123"

I tested it with the DB that I attached in my last comment
It looks like you make access visible again when the user login form unloads, that probably makes it show up.

Private Sub Form_Unload(Cancel As Integer)
Dim lngRetCode As Long
   lngRetCode = ShowWindow(hWndAccessApp, SW_MAXIMIZED)
End Sub

Open in new window


»bp
It works. Thanks a lot.
Now I have a new problem, the script does not enter the user data and password to the form.
What do you recommend, open a new question or follow in this question?
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good knowledge of the subject, very kind person