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("S ELECT " & 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
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("S
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(
" 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
ASKER
Tried to add a new record, but got a "Syntax in error FROM clause"--
rs.Open "SELECT " & ID & " FROM " & tblData & ";", CurrentProject.Connection
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
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
http://www.w3schools.com/ado/default.asp
ASKER
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.
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?
When you get the error, what does ID and tblData evaluate to?
ASKER
ID elevates to 0, shanesuebsahakarn; I don't know about tblData--that's the table.
ASKER
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.
ASKER
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
Me.txtID.Value = NewID("ID", "tlbdata")
Cancel = False
ASKER
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("S ELECT " & ID & " FROM " & tlbdata & ";")
Do
n = Int(Rnd * 1000000)
rs.FindFirst ID & "=" & n
Loop Until rs.NoMatch
rs.Close
NewID = n
End Function
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("S
Do
n = Int(Rnd * 1000000)
rs.FindFirst ID & "=" & n
Loop Until rs.NoMatch
rs.Close
NewID = n
End Function
ASKER
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 ";").
?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 ";").
ASKER
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.
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
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
ASKER
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("S ELECT " & 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
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("S
'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
ASKER
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?
ASKER
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.
ASKER
But, if I come back to the database, I notice that the record still gets saved.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am forever grateful for you help & kindness--and unwavering patience!--shanesuebsahaka rn. 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!
ASKER
Oh, BTW, so others may know--this one worked:
rs.Open "SELECT " & ID & " FROM " & tlbdata, CurrentProject.Connection, adOpenStatic
rs.Open "SELECT " & ID & " FROM " & tlbdata, CurrentProject.Connection,
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?
ASKER
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.
ASKER
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) :)
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("S
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.
'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