Great video and great series. Thank you! I do have a question about the EOF test before the ADORec.Find. The statement is: If Not ADORec.EOF Then ADORec.MoveFirst. It makes sense that if the cursor is somewhere in the middle of the db, that you would want to put it back to the first record. What about the case where the cursor is equal to EOF? In this case the MoveFirst will not happen. Is that ok? What happens? I'm thinking about just doing an unconditional (no "If Not ADORec.EOF Then" clause) ADORec.MoveFirst before the ADORec.Find. Does that make sense?
awesome great tut series. how would a save that recordset connection to a public or global variable so that I can call on that connection in my various forms and form controls.
Hi Steve, This is really good video series on VBA and Database , I have a Query about ADO , If i use do i need to open connection every time by connection string , can i make a global connection to refer all times i execute sql or procedure ?
Hi Steve, thank you for the videos. I copied exactly the same codes in VBA, yet I could not update and add new records. I guess I didn't run the query well enough in SQL. How about show us the details of the query in SQL? Many thanks.
Steve, I would like to know what the SQL Server query is. Did you make this in a previous video and I missed it? I would like to run the query you ran to better understand what is going on.
Hi Steve, I've got one Question on the above tutorial. When you add a new record to the table in SQL, whats the best way to get back the ID of the recently added record? I know I could do Move last and get the ID but thats not fool proof as my project would be distributed application which means there would 10's of users performing the same task and chances are I might get someone else's record ID. Any help would be appreciated. Many Thanks Saleem
+Saleem Md When you add a new record, after you perform the Update method any auto-seeded fields should also populate the value in your recordset. Make sure you perform the update method first though before you try to get the id value.
+Programming I'm confused, why would I perform an Update method when I insert a record with an insert statement? Is there anything like Output variable to catch the SCOPE_IDENTITY(). Sorry if I'm insane.
+Programming Hi Steve, I was adding a record via insert statement,. but since I posted my last comment, following are my updates - I've written a stored procedure in SQL Server and created a class module (for ex: Customer) with properties and addRecord/updateRecord/readRecord(set) methods in Access Front End -Now my form creates a Customer class object, assigns the form data to the Customer object and calls the addRecord method which invokes the Stored Procedure (Which returns me the Auto ID using the SCOPE_IDENTITY() JOB DONE I hope I'm clear. Many thanks for your tutorial & your prompt replies.
If I'm understanding this correctly, with ADO, a select query places the whole record set in the memory of the server; with DAO we retrieve the record set to the memory of the client. But with ADO we can execute commands on one record without placing the whole record set in memory, but not with DAO?
+Mark Williams No, both use memory on your local system. They just have different programming in how they connect and thus some of the options they make available.
Hello Steve, i'm study very well about your lesson, i'm not a coder or programming, just self study a few week about access, i know little about coding. i have beginner knowladge of C++,HTML,CSS,Javascrip.. now i got a block at here, i don't know how to solve it. Could you give me some advice. Thankyou very much. my Block is : when i run "ADOTEST" in Immediate.. it happen " compile error: expected variable or procedure, not module access " Public Sub ADOtest() Dim ADOConn As New ADODB.Connection Dim ADORec As New ADODB.Recordset
' On Error Resume Next ADOConn.ConnectionString = "Driver={SQL Server};Server=MaoLong-Pc\SQLEXPRESS;Database=North;Trusted_Connection=Yes;" ADOConn.Open Do While ADOConn.State 1 DoEvents If ADOConn.State = 0 Then Exit Sub Loop ' Set ADORec.ActiveConnection = ADOConn ' ADORec.LockType = adLockOptimistic ' ADORec.Source = "SELECT Company From Customers" ' ADORec.Open ' If Not ADORec.EOF Then ADORec.MoveFirst ' ADORec.Find "Company = 'Conpany CC'", , adSearchForward, 1 ' ' If Not ADORec.EOF Then ' ADORec("Company") = "Company DD" ' ADORec.Update ' End If ' ' If Not ADORec.EOF Then ADORec.MoveFirst ' Do Until ADORec.EOF ' Debug.Print ADORec("Company") ' ADORec.MoveNext ' Loop End Sub _____ My database is "North" is correct. i also try with MaoLong-Pc isn't connect. i put it MaoLong-PC\SQLEXPRESS will be work well. but it happen compile error.
its not like error in code, it pop up a window say:" compile error: expected variable or procedure, not module access ". i don't know. sorry for about that. i'm not a really programmer. so i don't know it is code error.. or other... :)
Last Night... i dont type and enter "ADOtest" in Immediate window,the pop up will not showup. i just test with F8 or F5 .the error window are not show up and the exchange data are updated and add new date to SQL. that error pop up is still happening, but the data will be change at SQL, so i'm keep go the Lesson,now. Thankyou for Reply. and Your Lesson is very easy to Learning. and Thank a Lot for everythings.
Great video and great series. Thank you! I do have a question about the EOF test before the ADORec.Find. The statement is: If Not ADORec.EOF Then ADORec.MoveFirst. It makes sense that if the cursor is somewhere in the middle of the db, that you would want to put it back to the first record. What about the case where the cursor is equal to EOF? In this case the MoveFirst will not happen. Is that ok? What happens? I'm thinking about just doing an unconditional (no "If Not ADORec.EOF Then" clause) ADORec.MoveFirst before the ADORec.Find. Does that make sense?
Mr Steve plz help me to bound ADO RS to a access form . I try but showing a single row at a time why not showing like datasheet view .
Late binding is always best in my opinion. Otherwise version control from client to client is a pain
I would agree.
awesome great tut series. how would a save that recordset connection to a public or global variable so that I can call on that connection in my various forms and form controls.
Hi Steve, This is really good video series on VBA and Database , I have a Query about ADO , If i use do i need to open connection every time by connection string , can i make a global connection to refer all times i execute sql or procedure ?
Sathish Naidu Malisetti You can create a public variable and then set it's value when the application opens. Then you can use it anywhere.
Hi Steve, thank you for the videos.
I copied exactly the same codes in VBA, yet I could not update and add new records. I guess I didn't run the query well enough in SQL. How about show us the details of the query in SQL? Many thanks.
Steve, I would like to know what the SQL Server query is. Did you make this in a previous video and I missed it? I would like to run the query you ran to better understand what is going on.
Jaz Isom The query I used is one I created on the fly for this video. You can use whatever query you would like that would return records.
Ok. I may have to do some review because I don't remember exactly the methodology and keywords of the sql query language.
Yeah I went over the SELECT statement toward the end of the beginner series.
Hi Steve,
I've got one Question on the above tutorial. When you add a new record to the table in SQL, whats the best way to get back the ID of the recently added record?
I know I could do Move last and get the ID but thats not fool proof as my project would be distributed application which means there would 10's of users performing the same task and chances are I might get someone else's record ID.
Any help would be appreciated.
Many Thanks
Saleem
+Saleem Md When you add a new record, after you perform the Update method any auto-seeded fields should also populate the value in your recordset. Make sure you perform the update method first though before you try to get the id value.
+Programming I'm confused, why would I perform an Update method when I insert a record with an insert statement?
Is there anything like Output variable to catch the SCOPE_IDENTITY(). Sorry if I'm insane.
+Saleem Md Are you doing the addition via SQL Insert query or through the recordset?
+Programming Hi Steve,
I was adding a record via insert statement,. but since I posted my last comment, following are my updates
- I've written a stored procedure in SQL Server and created a class module (for ex: Customer) with properties and addRecord/updateRecord/readRecord(set) methods in Access Front End
-Now my form creates a Customer class object, assigns the form data to the Customer object and calls the addRecord method which invokes the Stored Procedure (Which returns me the Auto ID using the SCOPE_IDENTITY()
JOB DONE
I hope I'm clear.
Many thanks for your tutorial & your prompt replies.
Yup, sounds like a pretty good solution.
If I'm understanding this correctly, with ADO, a select query places the whole record set in the memory of the server; with DAO we retrieve the record set to the memory of the client. But with ADO we can execute commands on one record without placing the whole record set in memory, but not with DAO?
+Mark Williams No, both use memory on your local system. They just have different programming in how they connect and thus some of the options they make available.
Mark Williams ado record sources are native to the server. So they are faster
Hello Steve, i'm study very well about your lesson, i'm not a coder or programming, just self study a few week about access, i know little about coding. i have beginner knowladge of C++,HTML,CSS,Javascrip..
now i got a block at here, i don't know how to solve it. Could you give me some advice. Thankyou very much.
my Block is : when i run "ADOTEST" in Immediate.. it happen
"
compile error: expected variable or procedure, not module access
"
Public Sub ADOtest()
Dim ADOConn As New ADODB.Connection
Dim ADORec As New ADODB.Recordset
' On Error Resume Next
ADOConn.ConnectionString = "Driver={SQL Server};Server=MaoLong-Pc\SQLEXPRESS;Database=North;Trusted_Connection=Yes;"
ADOConn.Open
Do While ADOConn.State 1
DoEvents
If ADOConn.State = 0 Then Exit Sub
Loop
' Set ADORec.ActiveConnection = ADOConn
' ADORec.LockType = adLockOptimistic
' ADORec.Source = "SELECT Company From Customers"
' ADORec.Open
' If Not ADORec.EOF Then ADORec.MoveFirst
' ADORec.Find "Company = 'Conpany CC'", , adSearchForward, 1
'
' If Not ADORec.EOF Then
' ADORec("Company") = "Company DD"
' ADORec.Update
' End If
'
' If Not ADORec.EOF Then ADORec.MoveFirst
' Do Until ADORec.EOF
' Debug.Print ADORec("Company")
' ADORec.MoveNext
' Loop
End Sub
_____
My database is "North" is correct. i also try with MaoLong-Pc isn't connect. i put it MaoLong-PC\SQLEXPRESS will be work well. but it happen compile error.
+Mg mglwin what line does it error on?
its not like error in code, it pop up a window say:" compile error: expected variable or procedure, not module access ".
i don't know. sorry for about that. i'm not a really programmer. so i don't know it is code error.. or other... :)
Last Night... i dont type and enter "ADOtest" in Immediate window,the pop up will not showup. i just test with F8 or F5 .the error window are not show up and the exchange data are updated and add new date to SQL. that error pop up is still happening, but the data will be change at SQL, so i'm keep go the Lesson,now.
Thankyou for Reply.
and Your Lesson is very easy to Learning.
and Thank a Lot for everythings.
sorry for my English