By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,652 Members | 1,842 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,652 IT Pros & Developers. It's quick & easy.

Set Security on using VBA in saved Access Query

P: n/a
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.CreateQueryDef("qryCriteriaSelected",
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-ContractInformation].*, WHERE "

'Build Reference Number Clause
If Not IsNull(Forms![frmSelectCriteria]![cboReferenceNumb]) Then
SQL = SQL & "([Tbl-ContractInformation].[ReferenceNumb] = "
SQL = SQL & "Forms![frmSelectCriteria]![cboReferenceNumb]) And
"
End If

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

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

Set db = DBEngine.Workspaces(0).Databases(0)

'Deletes the stored query def: qryCriteriaSelected
DoCmd.DeleteObject acQuery, "qryCriteriaSelected"

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

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


Feb 15 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Br
to*******@msn.com 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.CreateQueryDef("qryCriteriaSelected", 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-ContractInformation].*, WHERE "

'Build Reference Number Clause
If Not IsNull(Forms![frmSelectCriteria]![cboReferenceNumb]) Then
SQL = SQL & "([Tbl-ContractInformation].[ReferenceNumb] = "
SQL = SQL & "Forms![frmSelectCriteria]![cboReferenceNumb]) And
"
End If

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

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

Set db = DBEngine.Workspaces(0).Databases(0)

'Deletes the stored query def: qryCriteriaSelected
DoCmd.DeleteObject acQuery, "qryCriteriaSelected"

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

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


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

Br@dley
Feb 15 '06 #2

P: n/a
Don't do it that way. Do this instead:

Set qryCriteria = db.QueryDefs("qryCriteriaSelected")
qryCriteria.SQL = SQL

(david)
<to*******@msn.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.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.CreateQueryDef("qryCriteriaSelected",
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-ContractInformation].*, WHERE "

'Build Reference Number Clause
If Not IsNull(Forms![frmSelectCriteria]![cboReferenceNumb]) Then
SQL = SQL & "([Tbl-ContractInformation].[ReferenceNumb] = "
SQL = SQL & "Forms![frmSelectCriteria]![cboReferenceNumb]) And
"
End If

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

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

Set db = DBEngine.Workspaces(0).Databases(0)

'Deletes the stored query def: qryCriteriaSelected
DoCmd.DeleteObject acQuery, "qryCriteriaSelected"

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

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

Feb 15 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.