Solved

Convert DAO to ADO?

Posted on 2004-08-31
29
1,245 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
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 84
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
type of query 11 41
hit enter key to run macro 13 23
Access 2007 Switch To/Retry/Cancel Pop-Up 6 30
Why can't I update my query in datasheet view (or my form). 6 20
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

813 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

16 Experts available now in Live!

Get 1:1 Help Now