Connect the â€fe†to the encrypted â€beâ€

[BrunoKP]

I have asked before, but never found a useful solution, so please.

I want to connect the â€fe†to the encrypted â€beâ€, and I have tried with this
code:
Public Sub OpenDB()
Dim ServerFile As String
Dim db As DAO.Database
Dim ws As DAO.Workspace

Set ws = DBEngine.Workspaces(0)
ServerFile = "d:\home\data\ATR\Tabeller\ATR_be.accdb"
Set db = ws.OpenDatabase(ServerFile, False, False, "MS Access;pwd=be")
‘ db.Close
End Sub
My problem is that I don’t know from where I should call OpenDB when opening
the â€feâ€, in order to connect to the â€be†and maintain the connection during
the whole session.
I’m also in doubt if I have to use db.Close.
I'm still new in Access as you may notice

 

[Jack Leach]

My problem is that I don’t know from where I should call OpenDB when opening

the â€feâ€, in order to connect to the â€be†and maintain the connection during
the whole session.

If you want this be connected for the entire time that the fe is open, you
should have an autoexec macro that opens a hidden form. The form ideally has
your startup code in it (everything for startup up your app, including this),
and in the unload event of the form, clean everything up. This form serves
no purpose other than to control how the db opens and closes. (a good
practice startup should consist of an autoexec macro to run an autoexec
module, checking all references first. Then run your startup code, and the
hidden form should be connected to some table in the back end... this forces
the application to keep an open connection to the be, increasing performance
greatly).

I'm not exactly sure what context you're trying to use this in though, so
you may need something different than above (if you only want this open for a
specific task, persay).

I’m also in doubt if I have to use db.Close.

Always close what you open and destroy what you create.

Cleanup code for ws and db should resemble this:

db.Close
Set db = Nothing
'Maybe close the workspace?
'I'm not sure if they're closable,
'but at least clear the variable
Set ws = Nothing

Otherwise access might leave these referenced objects floating around in
space, and if you use that same nomenclature somewhere else in your code,
access may get confused by lingering settings, which could result in some
pretty nasty bugs.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

My problem is that I don’t know from where I should call OpenDB when opening

the â€feâ€, in order to connect to the â€be†and maintain the connection during
the whole session.

If you want this be connected for the entire time that the fe is open, you
should have an autoexec macro that opens a hidden form. The form ideally has
your startup code in it (everything for startup up your app, including this),
and in the unload event of the form, clean everything up. This form serves
no purpose other than to control how the db opens and closes. (a good
practice startup should consist of an autoexec macro to run an autoexec
module, checking all references first. Then run your startup code, and the
hidden form should be connected to some table in the back end... this forces
the application to keep an open connection to the be, increasing performance
greatly).

I'm not exactly sure what context you're trying to use this in though, so
you may need something different than above (if you only want this open for a
specific task, persay).

I’m also in doubt if I have to use db.Close.

Always close what you open and destroy what you create.

Cleanup code for ws and db should resemble this:

db.Close
Set db = Nothing
'Maybe close the workspace?
'I'm not sure if they're closable,
'but at least clear the variable
Set ws = Nothing

Otherwise access might leave these referenced objects floating around in
space, and if you use that same nomenclature somewhere else in your code,
access may get confused by lingering settings, which could result in some
pretty nasty bugs.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[BrunoKP]

Now I have a macro which opens a hidden form.
In the hidden form I have:
Private Sub Form_Load()
Dim ServerFile As String
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rstPerson As DAO.Recordset
Set ws = DBEngine.Workspaces(0)
ServerFile = "d:\home\data\ATR\Tabeller\ATR_be.accdb"
Set db = ws.OpenDatabase(ServerFile, False, False, "MS Access;pwd=be")
Set rstPerson = db.OpenRecordset("tblPerson", dbOpenDynaset)
End Sub
Private Sub Form_Close()
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rstPerson As DAO.Recordset
rstPerson.Close
db.Close
Set db = Nothing
Set ws = Nothing
End Sub

In each procedure/function where I connect to some table I always have
Set dbs = CurrentDb()
and in the end:
dbs.Close
Do I have to remove that? OK now I can make a try.

Thanks for your help

"Jack Leach" skrev:

 

[BrunoKP]

Now I have a macro which opens a hidden form.
In the hidden form I have:
Private Sub Form_Load()
Dim ServerFile As String
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rstPerson As DAO.Recordset
Set ws = DBEngine.Workspaces(0)
ServerFile = "d:\home\data\ATR\Tabeller\ATR_be.accdb"
Set db = ws.OpenDatabase(ServerFile, False, False, "MS Access;pwd=be")
Set rstPerson = db.OpenRecordset("tblPerson", dbOpenDynaset)
End Sub
Private Sub Form_Close()
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rstPerson As DAO.Recordset
rstPerson.Close
db.Close
Set db = Nothing
Set ws = Nothing
End Sub

In each procedure/function where I connect to some table I always have
Set dbs = CurrentDb()
and in the end:
dbs.Close
Do I have to remove that? OK now I can make a try.

Thanks for your help

"Jack Leach" skrev:

 

[BrunoKP]

No, I didn't work with my suggested code. I get "Password is not valid"
(translated from danish) as soon as I try to open a form or a table.
Your answer sounds to me that it is not normal to keep connected all the time.
I just want to have my application to work like before I introduced the
encryption in the "be".

I also had to remove
rstPerson.Close
db.Close

"BrunoKP" skrev:

 

[BrunoKP]

No, I didn't work with my suggested code. I get "Password is not valid"
(translated from danish) as soon as I try to open a form or a table.
Your answer sounds to me that it is not normal to keep connected all the time.
I just want to have my application to work like before I introduced the
encryption in the "be".

I also had to remove
rstPerson.Close
db.Close

"BrunoKP" skrev:

 

[BrunoKP]

Sorry, I had forgotten to remove the call of the old startup code. It works
fine now after I have removed that.
Thank you very much.

 

[BrunoKP]

Sorry, I had forgotten to remove the call of the old startup code. It works
fine now after I have removed that.
Thank you very much.

 

[Jack Leach]

You are declaring these variables inside a procedure, which means that as
soon as the procedure is done, their are no longer usable.

In order to make this db usable across the entire application, you will need
to declare them as Public in an standard module.

They don't necessarily have to be set from the form's open, but most
certainly do need to be closed with the form. So you'll wind up with code in
a standard module that defines these, and then code in the unload event of
the hidden form to properly close them.


Ex:

'(modSecureDB)
Option Compare Database
Option Explicit

Public SecureDB As DAO.Database
Public SecureRST. AS DAO.Recordset



and then in the form's open code:

Private Sub Form_Open(Cancel As Integer)
Set SecureDB = "....."
Set SecureRST = SecureDB.OpenRecordset(.....)
End Sub

in the hidden form's Unload procedure:

Private Sub Form_Unload(Cancel As Ineger)
SecureRST.Close
Set SecureRST = Nothing
SecureDB.Close
Set SecureDB = Nothing
End Sub


and at that point you should be able to reference from anywhere:

<something> = SecureRST.Fields(#)

etc..


As for actually opening the database and encryption goes, I unfortunately
have no idea.

good luck!

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

You are declaring these variables inside a procedure, which means that as
soon as the procedure is done, their are no longer usable.

In order to make this db usable across the entire application, you will need
to declare them as Public in an standard module.

They don't necessarily have to be set from the form's open, but most
certainly do need to be closed with the form. So you'll wind up with code in
a standard module that defines these, and then code in the unload event of
the hidden form to properly close them.


Ex:

'(modSecureDB)
Option Compare Database
Option Explicit

Public SecureDB As DAO.Database
Public SecureRST. AS DAO.Recordset



and then in the form's open code:

Private Sub Form_Open(Cancel As Integer)
Set SecureDB = "....."
Set SecureRST = SecureDB.OpenRecordset(.....)
End Sub

in the hidden form's Unload procedure:

Private Sub Form_Unload(Cancel As Ineger)
SecureRST.Close
Set SecureRST = Nothing
SecureDB.Close
Set SecureDB = Nothing
End Sub


and at that point you should be able to reference from anywhere:

<something> = SecureRST.Fields(#)

etc..


As for actually opening the database and encryption goes, I unfortunately
have no idea.

good luck!

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)