Convert DAO to ADO?
Posted on 2004-08-31
I had a very interesting experience today. I went to a client's office to install their new database and get it all set up with their MySQL back-end. Well, I was having many problems, and finally realized it's because I wasn't using an ADO reference in my VBA. Well, the reason it was working on my machine but not theirs was because I'm using Access 2002, and they were on 2000. Anyway, now they're database if referencing both ADO & DAO. I didn't even know it was possible to be using both references on the same database. Still, here's my question, for one of you smart Experts out there...
Of this code below, can you please convert it to ADO for me?--so that I won't need the DAO reference. And, if you know any good links where I can learn how to do this myself, for future reference, please provide that as well? Many kind thanks in advance!
Function NewID(ID As String, tlbdata As String) As Long
Dim rs As DAO.Recordset
Dim n As Long
' create a random key and search whether it's already used; if so, try again
Set rs = CurrentDb.OpenRecordset("SELECT " & ID & " FROM " & tlbdata & ";")
n = Int(Rnd * 1000000)
rs.FindFirst ID & "=" & n
Loop Until rs.NoMatch
NewID = n
NickName = Me.txtMyLogin
Dim rs As Recordset
Set rs = CurrentDb().OpenRecordset("SELECT Users.* FROM Users" & _
" WHERE (((Users.UserName)='" & Me.txtMyLogin & "') AND ((Users.Password)='" & Me.MyPassword & "'));")
If rs.RecordCount = 0 Then
MsgBox "Could not authenticate user, please check user name and PWD.", 16, "Failed to logon"
On Error GoTo Err_InsertAction
DoCmd.SetWarnings False ' switch warnings off
'Empty the localuser table
Dim sql As String
sql = "Delete * From [UserLocal]"
'Run query to append the authinticated users from the users table to the localuser table
DoCmd.Close acForm, "Login"
'set error warnings back on
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List14], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark