Solved

Convert DAO to ADO?

Posted on 2004-08-31
29
1,250 Views
Last Modified: 2010-07-27
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
0
Comment
Question by:John Account
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 11
  • 2
29 Comments
 
LVL 85
ID: 11948129
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
0
 

Author Comment

by:John Account
ID: 11948204
Tried to add a new record, but got a "Syntax in error FROM clause"--
rs.Open "SELECT " & ID & " FROM " & tblData & ";", CurrentProject.Connection
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11948223
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
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11948232
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
0
 

Author Comment

by:John Account
ID: 11948261
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.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11948271
Ah sorry, misread the code. The original lines are right :)

When you get the error, what does ID and tblData evaluate to?
0
 

Author Comment

by:John Account
ID: 11948280
ID elevates to 0, shanesuebsahakarn; I don't know about tblData--that's the table.
0
 

Author Comment

by:John Account
ID: 11948305
It doesn't elevate to anything, far as I can tell.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11948374
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.
0
 

Author Comment

by:John Account
ID: 11948393
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
0
 

Author Comment

by:John Account
ID: 11948432
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
0
 

Author Comment

by:John Account
ID: 11948457
This is very complicated...I aint having any luck here.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11948463
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 ";").
0
 

Author Comment

by:John Account
ID: 11948521
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.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11948546
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
0
 

Author Comment

by:John Account
ID: 11948587
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
0
 

Author Comment

by:John Account
ID: 11948689
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.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11948715
Is there a table in the database called tblData?
0
 

Author Comment

by:John Account
ID: 11948762
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.
0
 

Author Comment

by:John Account
ID: 11948771
But, if I come back to the database, I notice that  the record still gets saved.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 11948779
Change this line:
rs.Open "SELECT " & ID & " FROM " & tblData & ";", CurrentProject.Connection
to read:
rs.Open "SELECT " & ID & " FROM " & tblData & ";", CurrentProject.Connection,adOpenStatic

(basically just add ,adOpenStatic at the end of the line). That should do it.
0
 

Author Comment

by:John Account
ID: 11948815
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!
0
 

Author Comment

by:John Account
ID: 11948822
Oh, BTW, so others may know--this one worked:
rs.Open "SELECT " & ID & " FROM " & tlbdata, CurrentProject.Connection, adOpenStatic
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11948841
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?
0
 

Author Comment

by:John Account
ID: 11948888
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.
0
 

Author Comment

by:John Account
ID: 11948915
You're a good man, shanesuebsahakarn.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11948924
Thanks John :-)
0
 
LVL 85
ID: 11950813
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 ...
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11951274
The default type is adOpenForwardOnly (according to the Access help file anyway) :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question