How to connect to an Access database with ADO after it has been secured with user-level security.
Q: How can I connect to an Access database that has user-level security? I'm using ADO. I have both the User ID and password to open the database.
A: Unless you are already logged into the same secure workgroup information file for the remote database, you'll need to provide some extra properties for the Connection object before you open it. The following example contains the syntax you will need for the workgroup information file, user ID and password:
Public Sub getRecSetFromSecureDB( )
On Error GoTo ErrHandler
Dim Cnxn As New ADODB.Connection
Dim recSet As New ADODB.Recordset
Dim sDbName As String
Dim sWkGrpFile As String
Dim sSQLStmt As String
Dim sRecords As String
Dim idx As Long
Dim fOpenedCnxn As Boolean
Dim fOpenedRecSet As Boolean
'---------------------------------------
' Init.
'---------------------------------------
sDbName = "C:\Data\MyData.MDB"
sWkGrpFile = "C:\Data\Secure.MDW"
sSQLStmt = "SELECT * " & _
"FROM tblEmps " & _
"ORDER BY EmpID;"
sRecords = vbNullString
Cnxn.Provider = "Microsoft.Jet.OLEDB.4.0;"
Cnxn.Properties("Jet OLEDB:System database") = sWkGrpFile
Cnxn.Properties("User Id") = "TheBoss"
Cnxn.Properties("Password") = "EZ2Remember"
Cnxn.Open "Data Source=" & sDbName & ";"
fOpenedCnxn = True
recSet.Open sSQLStmt, Cnxn, adOpenForwardOnly, adLockReadOnly
fOpenedRecSet = True
'----------------------------------------------------------
' Save values from every field of every record.
'----------------------------------------------------------
Do While Not recSet.EOF
For idx = 0 To (recSet.Fields.Count - 1)
sRecords = sRecords & recSet.Fields(idx).Value & "| "
Next idx
sRecords = sRecords & vbCrLf
recSet.MoveNext
Loop
'----------------------------------------------------------
' Display all values from RecordSet on Form.
'----------------------------------------------------------
Me!txtEmpRecords.Value = sRecords
CleanUp:
If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If
If (fOpenedCnxn) Then
Cnxn.Close
fOpenedCnxn = False
End If
Set recSet = Nothing
Set Cnxn = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in getRecSetFromSecureDB( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & "Error #" & _
Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub
Top
How to connect to an Access database with ADO after it has been secured with shared-level security.
Q: How can I connect to an Access database that has shared-level security? I'm using ADO, and I have the database password to open the database.
A: You'll need to provide one extra property for the Connection object before you open it. The following example contains the syntax you will need for the database password:
Public Sub getRecSetFromDBWithPswd( )
On Error GoTo ErrHandler
Dim Cnxn As New ADODB.Connection
Dim recSet As New ADODB.Recordset
Dim sDbName As String
Dim sSQLStmt As String
Dim sRecords As String
Dim idx As Long
Dim fOpenedCnxn As Boolean
Dim fOpenedRecSet As Boolean
'---------------------------------------
' Init.
'---------------------------------------
sDbName = "C:\Data\MyData.MDB"
sSQLStmt = "SELECT * " & _
"FROM tblEmps " & _
"ORDER BY EmpID;"
sRecords = vbNullString
Cnxn.Provider = "Microsoft.Jet.OLEDB.4.0;"
Cnxn.Properties("Jet OLEDB:Database Password") = "MyPassword"
Cnxn.Open "Data Source=" & sDbName & ";"
fOpenedCnxn = True
recSet.Open sSQLStmt, Cnxn, adOpenForwardOnly, adLockReadOnly
fOpenedRecSet = True
'----------------------------------------------------------
' Save values from every field of every record.
'----------------------------------------------------------
Do While Not recSet.EOF
For idx = 0 To (recSet.Fields.Count - 1)
sRecords = sRecords & recSet.Fields(idx).Value & "| "
Next idx
sRecords = sRecords & vbCrLf
recSet.MoveNext
Loop
'----------------------------------------------------------
' Display all values on form.
'----------------------------------------------------------
Me!txtEmpRecords.Value = sRecords
CleanUp:
If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If
If (fOpenedCnxn) Then
Cnxn.Close
fOpenedCnxn = False
End If
Set recSet = Nothing
Set Cnxn = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in getRecSetFromDBWithPswd( ) in" & _
vbCrLf & Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub
Top
How to copy the current record on the form.
Q: I have a form that I often want to make a duplicate of the current record and then change just a few fields in the new record. Whenever I try to copy the current record, it copies the primary key, too, which causes an error. Is there any way around this?
A: Yes. One way is to use the following two procedures:
In the form's button's OnClick( ) event:
Private Sub CopyRecBtn_Click()
On Error GoTo ErrHandler
Call copyRecord(Me.RecordSource, "ID", Me!txtID.Value)
Me.Requery
Exit Sub
ErrHandler:
MsgBox "Error in CopyRecBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub ' CopyRecBtn_Click( )
Replace ID with the name of the primary key and txtID with the name of the control bound to the primary key field. Then place the following procedure in a standard module:
Public Sub copyRecord(sDataSrc As String, sPKey As String, nPKeyValue As Long)
On Error GoTo ErrHandler
Dim recSet As DAO.Recordset
Dim rows() As Variant
Dim sqlStmt As String
Dim idx As Long
Dim fOpenedRecSet As Boolean
sqlStmt = "SELECT * " & _
"From (" & sDataSrc & _
") WHERE (" & sPKey & " = " & nPKeyValue & ")"
Set recSet = CurrentDb().OpenRecordset(sqlStmt)
fOpenedRecSet = True
rows() = recSet.GetRows(1)
recSet.AddNew
For idx = 0 To (recSet.Fields.Count - 1)
If (recSet.Fields(idx).Name <> sPKey) Then
recSet.Fields(idx).Value = rows(idx, 0)
End If
Next idx
recSet.Update
CleanUp:
If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If
Set recSet = Nothing
Erase rows()
Exit Sub
ErrHandler:
MsgBox "Error in copyRecord( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub ' copyRecord( )
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
Top
|
