Link to home
Start Free TrialLog in
Avatar of John Account
John Account

asked on

Convert DAO to ADO?

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

    Randomize

    ' create a random key and search whether it's already used; if so, try again
    Set rs = CurrentDb.OpenRecordset("SELECT " & ID & " FROM " & tlbdata & ";")
    Do
        n = Int(Rnd * 1000000)
        rs.FindFirst ID & "=" & n
    Loop Until rs.NoMatch
    rs.Close
    NewID = n
End Function
--------
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
    Beep
    MsgBox "Could not authenticate user, please check user name and PWD.", 16, "Failed to logon"
    rs.Close
    Exit Sub
Else
On Error GoTo Err_InsertAction
    DoCmd.SetWarnings False  ' switch warnings off
    'Empty the localuser table
    Dim sql As String
    sql = "Delete * From [UserLocal]"
    CurrentDb.Execute sql
     'Run query to append the authinticated users from the users table to the localuser table
    DoCmd.OpenQuery "qryAddPrivileges"
    OpenCollectionsAdmin
    DoCmd.Close acForm, "Login"
     'set error warnings back on
     DoCmd.SetWarnings True
End If
rs.Close
subexit:
Exit Sub
--------
    ' 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
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

ADO is very similar to DAO, but it's more "platform" independent ... try the following:

Function NewID(ID As String, tlbdata As String) As Long
    'Dim rs As DAO.Recordset
     Dim rs As ADODB.Recordset
     Dim n As Long
     Dim blnOK As Boolean
    Randomize

    ' create a random key and search whether it's already used; if so, try again
    Set rs = New ADODB.Recordset
    rs.Open "SELECT " & ID & " FROM " & tblData & ";", CurrentProject.Connection
    'Set rs = CurrentDb.OpenRecordset("SELECT " & ID & " FROM " & tlbdata & ";")
   
    Do Until blnOK
        n = Int(Rnd * 1000000)
        rs.Find ID & "=" & n
      blnOK = Not(rs.EOF and rs.BOF)
    Loop

    rs.Close
    Set rs = Nothing
    NewID = n

End Function
--------
NickName = Me.txtMyLogin

Dim rs As New ADODB.Recordset
rs.Open "SELECT Users.* FROM Users" & _
" WHERE (((Users.UserName)='" & Me.txtMyLogin & "') AND ((Users.Password)='" & Me.MyPassword & "'));"), CurrentProject.Connection
If (rs.EOF and rs.BOF) Then
    Beep
    MsgBox "Could not authenticate user, please check user name and PWD.", 16, "Failed to logon"
    rs.Close
    set rs = Nothing
    Exit Sub
Else
On Error GoTo Err_InsertAction
    DoCmd.SetWarnings False  ' switch warnings off
    'Empty the localuser table
    Dim sql As String
    sql = "Delete * From [UserLocal]"
    CurrentProject.Connection.Execute sql
     'Run query to append the authinticated users from the users table to the localuser table
    DoCmd.OpenQuery "qryAddPrivileges"
    OpenCollectionsAdmin
    DoCmd.Close acForm, "Login"
     'set error warnings back on
     DoCmd.SetWarnings True
End If
rs.Close
Set rs = Nothing

subexit:
Exit Sub
--------

*************************
There is no RecordSetClone in ADODB, therefore, you must open another recordset and find the desired record, then find that record in the other recordset ... kinda klunky, but there are plenty of other methods to do different things.

    ' 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.Bookm
Avatar of John Account
John Account

ASKER

Tried to add a new record, but got a "Syntax in error FROM clause"--
rs.Open "SELECT " & ID & " FROM " & tblData & ";", CurrentProject.Connection
I think there was an error in the original code. This line (in LSMConsulting's modified version):
rs.Open "SELECT " & ID & " FROM " & tblData & ";", CurrentProject.Connection
should read:
rs.Open "SELECT [ID] FROM " & tblData & ";", CurrentProject.Connection

And this line:
rs.Find ID & "=" & n
should read:
rs.Find "[ID]=" & n
This is the main reference (apart from the Access Help file) that I used to learn ADO btw:
http://www.w3schools.com/ado/default.asp
Thanks, shanesuebsahakarn, but that's not working either, unfortunately.
Interesting link you got there!--looks like I got a lotta reading to do :-)
Many kind thanks in advance, guys--I'm upping the points to 500; this is obviously a very complicated thing.
Ah sorry, misread the code. The original lines are right :)

When you get the error, what does ID and tblData evaluate to?
ID elevates to 0, shanesuebsahakarn; I don't know about tblData--that's the table.
It doesn't elevate to anything, far as I can tell.
Ah - whichever procedure that is calling the above code isn't passing a table name to the function, so the code doesn't know which table to get the data from.
ahhhh...that's what you're talking about. This is the code below...so you're saying that the randomize isn't generating the number?--but it was before...before converting it over to ado from dao:
Me.txtID.Value = NewID("ID", "tlbdata")
    Cancel = False
See, here's what I'm still trying to convert to ADO...and I'm having a real hard time:
Private Sub Form_AfterInsert()
Me.txtID.Value = NewID("ID", "tlbdata")
    Cancel = False
End Sub
'------------
Function NewID(ID As String, tlbdata As String) As Long
    Dim rs As DAO.Recordset
    Dim n As Long

    Randomize

    ' create a random key and search whether it's already used; if so, try again
    Set rs = CurrentDb.OpenRecordset("SELECT " & ID & " FROM " & tlbdata & ";")
    Do
        n = Int(Rnd * 1000000)
        rs.FindFirst ID & "=" & n
    Loop Until rs.NoMatch
    rs.Close
    NewID = n
End Function
This is very complicated...I aint having any luck here.
I see - try this, when the code crashes, in the Immediate window, type:
?ID
and then:
?tbldata

See what it comes back with. Also, change the line from:
rs.Open "SELECT " & ID & " FROM " & tblData & ";", CurrentProject.Connection
to:
rs.Open "SELECT " & ID & " FROM " & tblData, CurrentProject.Connection

(in other words, take out the ";").
Syntax error in FROM clause, again, shanesuebsahakarn.
in the Immediate window, for ?ID the response is ID
For ?tbldata, the response is empty; just a hard line break.
There's the problem - tbldata is not being passed into the function properly. I don't know why that's happening - can you just confirm that the line definitely says:
Me.txtID.Value = NewID("ID", "tlbdata")

(i.e. the tbldata part enclosed in quotes)?

Does the function always use tbldata as the table and ID as the field, or do you need to use it with other tables and fields? If you don't, you can always change the code like so:
rs.Open "SELECT ID FROM tblData", CurrentProject.Connection
Yup...here's what it says (the DAO, used before, is commented out:
Private Sub Form_AfterInsert()
Me.txtID.Value = NewID("ID", "tlbdata")
    Cancel = False
End Sub
'------------

Function NewID(ID As String, tlbdata As String) As Long
    'Dim rs As DAO.Recordset
    'Dim n As Long
    'Randomize
    ' create a random key and search whether it's already used; if so, try again
    'Set rs = CurrentDb.OpenRecordset("SELECT " & ID & " FROM " & tlbdata & ";")
    'Do
    '    n = Int(Rnd * 1000000)
    '    rs.FindFirst ID & "=" & n
    'Loop Until rs.NoMatch
    'rs.Close
    'NewID = n

     Dim rs As ADODB.Recordset
     Dim n As Long
     Dim blnOK As Boolean
    Randomize

    ' create a random key and search whether it's already used; if so, try again
    Set rs = New ADODB.Recordset
    rs.Open "SELECT " & ID & " FROM " & tblData, CurrentProject.Connection
   
    Do Until blnOK
        n = Int(Rnd * 1000000)
        rs.Find ID & "=" & n
     blnOK = Not (rs.EOF And rs.BOF)
    Loop

    rs.Close
    Set rs = Nothing
    NewID = n
End Function
Yes, shanesuebsahakarn, the function always use tbldata as the table and ID as the field, and, no, it's never used it with other tables and fields. Anyway, I changed the code to--rs.Open "SELECT ID FROM tblData", CurrentProject.Connection--to try it out, but got an error that said "The Microsoft Jet database engin cannot find the input table or query 'tblData'. Make sure it exsis and that its name is spelled correctly." Then I tired ?ID & ?tbldata, and got the same results as last time--ID, and Nothing.
Is there a table in the database called tblData?
OMG, you're gonna kill me for this!--somehow, during all this dialog, one of the references to tblData was changed to tlbData; hence all those problems. So it's almost working perfectly now. Only one thing: I'm getting an error message that says: Rowset does not support scrolling backward.
But, if I come back to the database, I notice that  the record still gets saved.
ASKER CERTIFIED SOLUTION
Avatar of shanesuebsahakarn
shanesuebsahakarn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am forever grateful for you help & kindness--and unwavering patience!--shanesuebsahakarn. I'm learning a lot from you. And, hopefully, one day, I'll be able to help others here at Experts, as you have helped me. Many MANY thanks to you!
Oh, BTW, so others may know--this one worked:
rs.Open "SELECT " & ID & " FROM " & tlbdata, CurrentProject.Connection, adOpenStatic
John, I think LSMConsulting deserves the points on this one more than I do - the conversion part was all his, I just helped trace out a few problems. You might want to get the admins to change the accepted answer on this question or post a points for question for him?
Yes, you're right, shanesuebsahakarn. I'm gonna post a points for question for him, so you both get equal points. That was a lotta help I got from y'all.
You're a good man, shanesuebsahakarn.
Thanks John :-)
Hmmm .... wonder why you had to add adOpenStatic to that line ... that's the default setting for an ADO recordset ... anyway, sorry I couldn't stay up and help, long day with lots of typing ...
The default type is adOpenForwardOnly (according to the Access help file anyway) :)