Runing a quey in the back end?

[jbr87]

Hi guys this is my first post but I've been reading alot and appreciate all the help that's been provided so far. I have a question that I hope somebody can answer.

So I have a database that tracks projects we've done here at work. It has 2 tables Public and Private.

Before splitting the database I had a query that would run on update from a form. Essentially the query was an append that would add a record in the Private table when a record was added in the Public table. The reason for splitting the database is to ensure that most users only have access to the Public table and a select few can go into the back end (password protected) to enter some sensitive information into the Private table. I know this is not an Ideal security setup but for the employees that will be using the database it will be an adequate roadblock.

Now when I entre data in the form on the front end I get this error message (attatched).




this is the code I'm using to call the query:

Private Sub Job_Number_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.OpenQuery ("update")
DoCmd.SetWarnings True
End Sub

and this is the query:
INSERT INTO Private ( [Job Number] )
SELECT [Job Number]
FROM [Public];

Is there a way to have the back end execute a querry on a trigger that occurs in the front end database?

[vicrauch]

The first thing that comes to my mind is to create a form in the BE database with a button on it that will run your query. Then with automation, you can open the BE database from your FE database, and run the code behind that button, therefore getting your query to run. Actually, you should be able to just run the query from your automation object too, without the form or button. I have done this sort of thing, but that was a long time ago. It would be just like controlling MS Word or MS Excel from Access.

[NTC]

Append queries work fine in split dbs. I tend to think the split and the error are coincidental rather than cause/effect.

Sanity check for yourself a manual append query of a test value and see if it works ok. Then set it up to be triggered.

Hope this helps.

[vicrauch]

NTC,
I may have misread the original post, but I believe the "Private" table is ONLY in the BE database. That is his security method to keep the wrong people from viewing the private database. So the query itself would have to be in the BE database, because that is the only place both tables are available.
jbr87 --**
Is the situation that you actually have like what I have outlined above?

[jbr87]

NTC,
I may have misread the original post, but I believe the "Private" table is ONLY in the BE database. That is his security method to keep the wrong people from viewing the private database. So the query itself would have to be in the BE database, because that is the only place both tables are available.
jbr87 --**
Is the situation that you actually have like what I have outlined above?

vicrauch that is what I meant

[NTC]

unless I'm having a major brain freeze (...which is always possible...) - it doesn't matter. A table is a table. yes - in a split db where the table is linked then that table will have an arrow icon next to it....... but an append query will write to either local or remote table.

if you don't want users messing around going direct to any table - make objects not visible and/or use the mde/accde method.

all dbs should be split. unless it is truly a single user personal db. any db that has multiple users must be split by definition for a correct...

so there are 3 separate issues;
*splitting a db,
*security,
*append queries...

maybe helps a bit.....

[rpeare]

he'd have to do the proper setup to write to a table that's not in the existing database though and the users would have to have access (even though they don't know it) to that file and permission (network) to write to it. So depending on the level of sophistication of your user this may be an acceptable 'risk' to you.

this is the code you could use from your 'restricted' side:

Dim db As Database
Dim ssql As String

Set db = CurrentDb
ssql = "INSERT INTO Tbl_Test (Field01) in 'c:\pathinfo\targetdatabase.mdb' VALUES ('QQQ')"
db.Execute ssql

note the IN portion of the SQL code, that's where you'd define where the back end of your table exists.

note that if the person is savvy enough to view the code they can go right to the back end database, you could possibly avoid this but using

\\servername\rest of path\ (I didn't try this) and not use a mapped drive name which might take care of most of your users

[jbr87]

That works for the extent I need
thanks guys