External .mdb inside Forms

[Henry]

I would like to use the

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase file, , pwd

DAO method to view an external table inside a listbox and than open a record
in a form and manipulate the data.

Of course I could just import the table, but for security I can't do this,
because this way you have unprotected access to passwd protected .mdb files.

 

[Dirk Goldgar]

I would like to use the

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase file, , pwd

DAO method to view an external table inside a listbox and than open a
record in a form and manipulate the data.

Of course I could just import the table, but for security I can't do
this, because this way you have unprotected access to passwd
protected .mdb files.

You can set a form's recordsource or a combo box's rowsource to a SQL
statement that selects data from an external MDB, specifying the
database password. For example,

SELECT * FROM SomeTable
IN "" "MS Access;database=C:\Temp\SomeDB.mdb;pwd=foo";

Note that this method can be used if the external database is secured
using a database password, which is a much simpler form of security --
and easier to crack -- than user-level (workgroup) security.

Note also that, if you hard-code your password in a rowsource or
recordsource, that password isn't going to stay secret very long. You
may be able to encrypt the password in your local database, then decrypt
it and build the record/rowsource on the fly for a *little* more
security, but this is fundamentally insecure and I don't think there's
anything you can do about it short of applying user-level security
instead.

 

[Henry]

That jutst worked perfectly ! I even opened a form with external data that way.

(I knew, one day I will be force to play with sql syntax ;-)

Now, when closing the form, I want to write the selected record back to the
external .mdb like:

Private Sub Form_Close()
If Me.Dirty Then
DoCmd.RunSQL "UPDATE Employees.......

But how would I write a complete record ?

 

[Dirk Goldgar]

That jutst worked perfectly ! I even opened a form with external data
that way.

(I knew, one day I will be force to play with sql syntax ;-)

Now, when closing the form, I want to write the selected record back
to the external .mdb like:

Private Sub Form_Close()
If Me.Dirty Then
DoCmd.RunSQL "UPDATE Employees.......

But how would I write a complete record ?

If you were to make updates by way of a form bound to the table in the
external database, you wouldn't have this problem. But if that's not an
option, I can think of two basic approaches:

(1) build an SQL statement that includes the names and new values of all
fields in the table, or

(2) open a recordset on a query of the external table that returns the
record you want to update, then edit the recordset, change the values of
all the fields as needed, update the record, and close the recordset.

 

[Henry]

*G* some times the answer is so easy !

Thank you Dirk !