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($databa se,$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
$Acc = New-Object –comObject Access.Application
$dest = "${datadir}bbdd.temp"
If (Test-Path $dest) { Remove-Item $dest }
$Acc.CompactRepair($databa
Remove-Item $database
Move-Item $dest $database
$Acc.OpenCurrentDataBase($
$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
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:
»bp
$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
»bp
ASKER
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
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.
»bp
$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()
»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
»bp
ASKER
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
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
ASKER
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_Logi n_bd')
$AccForm.Controls.Item("tx t_login"). value = "Prueba"
$AccForm.Controls.Item("tx t_contrase na").value = "Prueba123"
I tested it with the DB that I attached in my last comment
$database = 'D:\db.accdb'
$Acc = New-Object –comObject Access.Application
$Acc.Visible = $false
$Acc.OpenCurrentDataBase($
$Acc.DoCmd.OpenForm('Form_
$AccForm = $Acc.Forms.item('Form_Logi
$AccForm.Controls.Item("tx
$AccForm.Controls.Item("tx
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.
»bp
Private Sub Form_Unload(Cancel As Integer)
Dim lngRetCode As Long
lngRetCode = ShowWindow(hWndAccessApp, SW_MAXIMIZED)
End Sub
»bp
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good knowledge of the subject, very kind person
$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