Best code for form load event..

Hi,

Kindly let me know the best way to start coding for ADO:
How the form load event should be now?

------------------------------------------------
(GENERAL) ---- (DECLARATIONS)
------------------------------------------------
Option Explicit
Dim cn As ADODB.Connection
Dim rs As adbodb.Recordset
Dim scn As String
Dim sql As String

--------------------------------------------------

Private Sub Form_Load()


End Sub


---------------------------------------------------
Regards,

sweetie_2005

What exactly you need?

Dim should be used to declare variables at the event level.

Private should be used to declare variables at the Form level.

Public should be used to declare variables at the Module level.

So, the variable declarations in your code example should use Private not Dim and I would add the following to those variables.

VB Code:

Private cn As ADODB.Connection
Private rs As adbodb.Recordset
Private strConnString As String 'add this
Private scn As String
Private sql As String

The best code for the Form Load event will vary for each project and will be/should be dictated by the needs of that project. A general rule of thumb would be to use the Form Load to connect to your database.

VB Code:

Private Sub ConnectDb()
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\yourMdb.mdb;" & _
        "Persist Security Info=False"
 
Set cn = New ADODB.Connection
cn.ConnectionString = strConnString
cn.Open strConnString
End Sub
 
Private Sub Form_Load()
ConnectDb
End Sub

Quote:

---

Originally Posted by mayurvb

What exactly you need?

---

Mayur,

Could you please write code for the form load event by looking at the general declarations.

Sweetie_2005

Quote:

---

Originally Posted by Hack

Dim should be used to declare variables at the event level.

Private should be used to declare variables at the Form level.

Public should be used to declare variables at the Module level.

End Sub[/Highlight]

---

Wow, Hack, you have written a lot of code for me. Thanks.
Suppose I have a database called "database.mdb"
Two tables called "table1" & table2" and I need both tables to be accesseble for me then how exactly should be the code?

Can you explain, Hack?

Sweetie_2005

Quote:

---

Originally Posted by sweetie_2005

Wow, Hack, you have written a lot of code for me. Thanks.
Suppose I have a database called "database.mdb"
Two tables called "table1" & table2" and I need both tables to be accesseble for me then how exactly should be the code?

Can you explain, Hack?

Sweetie_2005

---

Exactly the way I've already posted. Once you open the database, it doesn't matter how many tables are there. They are all available.

Quote:

---

Originally Posted by Hack

Exactly the way I've already posted. Once you open the database, it doesn't matter how many tables are there. They are all available.

---

Wow! like that. Can I access the both tables at the same time.

Quote:

---

Originally Posted by sweetie_2005

Wow! like that. Can I access the both tables at the same time.

---

Sure. I have databases with 50 and 60 tables, sometimes more.

All you need to do is open the database, and they are there for you to do whatever you need to do.

Quote:

---

Originally Posted by Hack

Sure. I have databases with 50 and 60 tables, sometimes more.

All you need to do is open the database, and they are there for you to do whatever you need to do.

---

Oh! I see.

Thanks

yuck ADO!! lol... as I have said before.. Access was designed to use DAO and I think it works better with it :) not to mention its MUCH easier to use!

VB Code:

'General Declaraitons
Public ws As Workspace
Public db As Database
 
'Form Load
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("C:\database.mdb")
 
'run a query...
Dim rs as recordset
Set rs = db.OpenRecordset("SELECT * FROM TABLE1 WHERE ID = 1")
 
'edit?
rs.Edit
rs!CustomerName = "New Name"
rs.update
 
'or u can set the rs right to a table
 
Set rs = db.openrecordset("Table1")
 
'add?
rs.AddNew
rs!CustomerName = "New Customer"
rs!SomeOtherField = "Othe Data"
rs.update
 
'other stuff:
'if u have a query in access u want to run?
access.DoCmd.OpenQuery "QryName"
'plus lots of other goodies easily available...
db.Execute "SQL"
access.SysCmd [Option]
'how about Dlookup!??
'quick way to get the value of one field without a whole SQL statement
str = dlookup("FieldName","TableName","ID = 3")

ok.. enough pushing from me ;) but its so easy!! lol.. but of course this is my opinion.. and most here will say to use ADO since DAO is a dying code.. but hey.. so is VB6 right ? ;)

Just to let you know, it's best to use the Form_Activate() event rather than Form_Load() because the Form_Load event fires before the form and all of its objects are even loaded! And as a result, you may sometimes find odd results depending on what you do. Form_Activate fires after everything has loaded, and everything should work fine with that. ;)

I thought that form_activate can fire again..?

Sweet, as an addendum to hack's code, place the connection object in a module (*.bas) so its available for use in the entire project and not just in one form.

yup, the activate event can fire again... I guess jacob was referring to problems with controls placed on the form.. eg you can't use their setfocus method during form load.