Solved

Convert DAO to ADO?

Posted on 2004-08-31
29
1,240 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
  • 16
  • 11
  • 2
29 Comments
 
LVL 84
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ID elevates to 0, shanesuebsahakarn; I don't know about tblData--that's the table.
0
 

Author Comment

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

Expert Comment

by:shanesuebsahakarn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
This is very complicated...I aint having any luck here.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Is there a table in the database called tblData?
0
 

Author Comment

by:John Account
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You're a good man, shanesuebsahakarn.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Thanks John :-)
0
 
LVL 84
Comment Utility
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
Comment Utility
The default type is adOpenForwardOnly (according to the Access help file anyway) :)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now