Set Security on using VBA in saved Access Query

[tomlebold]

The function or code below creates a saved query used as the record
source for forms and reports. The funtions is called each time a user
clicks on a button to display a form or report.
This works just after security has been set for the user by cheching
all options, which include
delete, modify, append and administer. Security is being set on the
tables, queries and forms.
The second time the user clicks on the button that runs the function
the security check boxes become unchecked. The problems seems to be
that the security needs to be checked just before the code creates the
saved query: Set qryCriteria = db.CreateQueryD ef("qryCriteria Selected",
SQL). How do set security in VBA code for query on object.

Dim SQL As String
Dim db As DAO.database
Dim qryCriteria As DAO.QueryDef
Dim ClearEndDate As String

On Error GoTo BuildSql_Err

'Select and From clause
SQL = "SELECT [Tbl-ContractInforma tion].*, WHERE "

'Build Reference Number Clause
If Not IsNull(Forms![frmSelectCriter ia]![cboReferenceNum b]) Then
SQL = SQL & "([Tbl-ContractInforma tion].[ReferenceNumb] = "
SQL = SQL & "Forms![frmSelectCriter ia]![cboReferenceNum b]) And
"
End If

'Build Reference Number Prefix Clause
If Not IsNull(Forms![frmSelectCriter ia]![cboReferencePre fixNumb])
Then
SQL = SQL & "(Left([ReferenceNumb],5) = "
SQL = SQL &
"Forms![frmSelectCriter ia]![cboReferencePre fixNumb]) And "
End If

SQL = SQL & " -1 = -1; "

Set db = DBEngine.Worksp aces(0).Databas es(0)

'Deletes the stored query def: qryCriteriaSele cted
DoCmd.DeleteObj ect acQuery, "qryCriteriaSel ected"

'Turn off Access system warning to create, delete or updates tables
DoCmd.SetWarnin gs False

'Create stored query def with return value deal criteria
Set qryCriteria = db.CreateQueryD ef("qryCriteria Selected", SQL)

[Br]

to*******@msn.c om wrote:

The function or code below creates a saved query used as the record
source for forms and reports. The funtions is called each time a user
clicks on a button to display a form or report.
This works just after security has been set for the user by cheching
all options, which include
delete, modify, append and administer. Security is being set on the
tables, queries and forms.
The second time the user clicks on the button that runs the function
the security check boxes become unchecked. The problems seems to be
that the security needs to be checked just before the code creates the
saved query: Set qryCriteria =
db.CreateQueryD ef("qryCriteria Selected", SQL). How do set security in
VBA code for query on object.

Dim SQL As String
Dim db As DAO.database
Dim qryCriteria As DAO.QueryDef
Dim ClearEndDate As String

On Error GoTo BuildSql_Err

'Select and From clause
SQL = "SELECT [Tbl-ContractInforma tion].*, WHERE "

'Build Reference Number Clause
If Not IsNull(Forms![frmSelectCriter ia]![cboReferenceNum b]) Then
SQL = SQL & "([Tbl-ContractInforma tion].[ReferenceNumb] = "
SQL = SQL & "Forms![frmSelectCriter ia]![cboReferenceNum b]) And
"
End If

'Build Reference Number Prefix Clause
If Not IsNull(Forms![frmSelectCriter ia]![cboReferencePre fixNumb])
Then
SQL = SQL & "(Left([ReferenceNumb],5) = "
SQL = SQL &
"Forms![frmSelectCriter ia]![cboReferencePre fixNumb]) And "
End If

SQL = SQL & " -1 = -1; "

Set db = DBEngine.Worksp aces(0).Databas es(0)

'Deletes the stored query def: qryCriteriaSele cted
DoCmd.DeleteObj ect acQuery, "qryCriteriaSel ected"

'Turn off Access system warning to create, delete or updates tables
DoCmd.SetWarnin gs False

'Create stored query def with return value deal criteria
Set qryCriteria = db.CreateQueryD ef("qryCriteria Selected", SQL)

The second time you run it the query is created again, therefore any
security settings will be lost.??
--
regards,

Br@dley

[david epsom dot com dot au]

Don't do it that way. Do this instead:

Set qryCriteria = db.QueryDefs("q ryCriteriaSelec ted")
qryCriteria.SQL = SQL

(david)
<to*******@msn. com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.com...

The function or code below creates a saved query used as the record
source for forms and reports. The funtions is called each time a user
clicks on a button to display a form or report.
This works just after security has been set for the user by cheching
all options, which include
delete, modify, append and administer. Security is being set on the
tables, queries and forms.
The second time the user clicks on the button that runs the function
the security check boxes become unchecked. The problems seems to be
that the security needs to be checked just before the code creates the
saved query: Set qryCriteria = db.CreateQueryD ef("qryCriteria Selected",
SQL). How do set security in VBA code for query on object.

Dim SQL As String
Dim db As DAO.database
Dim qryCriteria As DAO.QueryDef
Dim ClearEndDate As String

On Error GoTo BuildSql_Err

'Select and From clause
SQL = "SELECT [Tbl-ContractInforma tion].*, WHERE "

'Build Reference Number Clause
If Not IsNull(Forms![frmSelectCriter ia]![cboReferenceNum b]) Then
SQL = SQL & "([Tbl-ContractInforma tion].[ReferenceNumb] = "
SQL = SQL & "Forms![frmSelectCriter ia]![cboReferenceNum b]) And
"
End If

'Build Reference Number Prefix Clause
If Not IsNull(Forms![frmSelectCriter ia]![cboReferencePre fixNumb])
Then
SQL = SQL & "(Left([ReferenceNumb],5) = "
SQL = SQL &
"Forms![frmSelectCriter ia]![cboReferencePre fixNumb]) And "
End If

SQL = SQL & " -1 = -1; "

Set db = DBEngine.Worksp aces(0).Databas es(0)

'Deletes the stored query def: qryCriteriaSele cted
DoCmd.DeleteObj ect acQuery, "qryCriteriaSel ected"

'Turn off Access system warning to create, delete or updates tables
DoCmd.SetWarnin gs False

'Create stored query def with return value deal criteria
Set qryCriteria = db.CreateQueryD ef("qryCriteria Selected", SQL)