Hi Danny,
Here's some sample ADO code to get you going a little to help you out for running some data retrieval in MS-Access.
For example, let's say your database is named UserPwdDB.mdb and your table is named Users.
---------------------------------------------------------------------
' Here's code to open the database and recordset:
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDataSource As String
strDataSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\UserPwdDB.MDB"
cnn.Open strDataSource
' Open the recordset for read-only (random reads):
rst.Open "Users", cnn, adOpenDynamic, adLockReadOnly
' OR Open the recordset for read-only (forward-only starting at top of file
rst.Open "Users", cnn, adOpenForwardOnly, adLockReadOnly
' OR Open the recordset for random updating (allows Find, etc operations)
rst.Open "Users", cnn, adOpenKeyset, adLockPessimistic
' open mode depends on usage of the recordset in your app
---------------------------------------------------------------------
' Here's code to close the recordset and connection objects
rst.Close
cnn.Close
---------------------------------------------------------------------
' Here's code to retrieve the password for a selected user name:
Dim strUserID As String
Dim varPassword As Variant
strUserID = "jdavis"
rst.Find "UserID = '" & strUserID & "'" ' Need single-quote delims for text fields
If rst.EOF Then
MsgBox "No match was found for the selected userid", vbExclamation, "ERROR"
Else
varUserPassword = rst.Fields("Password").Value
End If
' I used a variant to store the retrieved password in case there is
' a NULL value in the password field for the selected user
---------------------------------------------------------------------
' Here's code to change the password for an existing user
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDataSource As String
Dim strUserID As String
Dim strPassword As String
strDataSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\UserPwdDB.MDB"
cnn.Open strDataSource
rst.Open "Users", cnn, adOpenKeyset, adLockPessimistic
strUserID = "jdavis"
strPassword = "garfield"
rst.Find "UserID = '" & strUserID & "'" ' Need single-quote delims for text fields
If rst.EOF Then
MsgBox "No match was found for the selected userid", vbExclamation, "ERROR"
Else
rst.Fields("Password").Value = strPassword
rst.Update
End If
rst.Close
cnn.Close
' OR, you can remove the FIND process and indicate that within your recordset open processing like this:
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDataSource As String
Dim strUserID As String
Dim strPassword As String
strDataSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\UserPwdDB.MDB"
cnn.Open strDataSource
strUserID = "jdavis"
rst.Open "SELECT * FROM Users WHERE UserID = '" & strUserID & "'", cnn, adOpenKeyset, adLockPessimistic
If rst.EOF Then
MsgBox "No match was found for the selected userid", vbExclamation, "ERROR"
Else
strPassword = "garfield"
rst.Fields("Password").Value = strPassword
rst.Update
End If
rst.Close
cnn.Close
' OR, you can do the following using SQL and not using a recordset object at all
Dim cnn As New ADODB.Connection
Dim strDataSource As String
Dim strUserID As String
Dim strPassword As String
strDataSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\UserPwdDB.MDB"
cnn.Open strDataSource
strUserID = "jdavis"
strPassword = "garfield"
strSQL = "UPDATE Users " & _
"SET Password = '" & strPassword "' " & _
"WHERE UserID = '" & strUserID & "';"
cnn.Execute strSQL
cnn.Close
---------------------------------------------------------------------
' Here's code to add a new user with a password
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDataSource As String
Dim strUserID As String
Dim strPassword As String
strDataSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\UserPwdDB.MDB"
cnn.Open strDataSource
rst.Open "Users", cnn, adOpenKeyset, adLockPessimistic
strUserID = "jdavis"
strPassword = "garfield"
rst.AddNew
rst!UserID = strUserID
rst!Password = strPassword
rst.Update
rst.Close
cnn.Close
' OR, you can do the following using SQL and not using a recordset object at all
Dim cnn As New ADODB.Connection
Dim strDataSource As String
Dim strUserID As String
Dim strPassword As String
strDataSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\UserPwdDB.MDB"
cnn.Open strDataSource
strUserID = "jdavis"
strPassword = "garfield"
strSQL & "INSERT INTO Users " & _
" ( UserID, " & _
" Password )" & _
" VALUES " & _
" ('" & strUserID & "', " & _
" '" & strPassword & "');"
cnn.Execute strSQL
cnn.Close
---------------------------------------------------------------------
' Here's how to delete an existing row from the table - I only have
' SQL example offhand, though.
Dim cnn As New ADODB.Connection
Dim strDataSource As String
Dim strUserID As String
Dim strPassword As String
strDataSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\UserPwdDB.MDB"
cnn.Open strDataSource
strUserID = "jdavis"
strSQL & "DELETE FROM Users " & _
"WHERE strUserID = '" & strUserID & "';"
cnn.Execute strSQL
cnn.Close
---------------------------------------------------------------------
I hope that this will be something to help you get started. There's actually alot of information out there explaining these and many other types of operations in much greater detail. One of the things that I found very useful was a whitepaper from Microsoft named "Migrating from DAO to ADO" - it shows quite a few examples using DAO, ADO, and even JRO in some cases. You may be able to do a search on the web to locate this.
I kind of put these together quickly and did not have a chance to verify that all code would compile cleanly or execute perfectly, but I will be happy to make any adjustments if necessary.
Good luck!
Warren
|
