Cn.open "provider=Microsoft.jet.oledb.4.0;data source=full path of database.mdb" Getting dynamicly full paths:- Cn.open "provider=Microsoft.jet.oledb.4.0;data source=" & app.path & ("\database.mdb") Opening recordsource:- a) open "table" b) connection variable c) adopen type d) adlock type syntax:- rs.open "table_name",cn,adopendynamic,adlockpessimistik 1.EOF(Ending of field) 2.BOF(Beginning of Field) 1.EOF(Ending of field) Syntax:- Rs.movenext If rs.eof=true then Msgbox " " Rs.movelast Else Call subprocedure End if 2.BOF(Beginning of Field) Rs.moveprevious If rs.bof=true then Msgbox " " Rs.movefirst Else Call subprocedure End if Rs.addnew Call [procedure] Syntax for finding records:- Rs.movefirst Rs.find "columnname= ‘" & inputcontrol & "‘" Call [equivalent to call data in front end] Define new separate recordsets for combobox:- Dim rscbo As New ADODB.Recordset Adding new records in combo box:- Syntax:- Rscbo.open "table",cn,adopn,adlock While not rscbo.eof=true Combo.additem rscbo.fileds(index) Rscbo.movenext wend rscbo.close set rscbo=nothing Finding data (records) from combo of textbox:- a. You need to use the recordset of text box. b. You need to use the click event of combobox. c. You have to add data in combo box already. d. You can use the "rs.find"method. Example:- Closing connection or recordsert objects:- closing connection if opened:- if cn.state=adstateopened then cn.close end if or if cn.state=adstateopened then cn.close closing recordset if opened:- if rs.state=adstateopened then rs.close end if or if rs.state=adstateopened then rs.close Datagrid syntax:- Rsdatagrid.cursorlocation=aduseclient Rsdatagrid.open "table",cn,adopn,adlock Set datagrid.datasource=rsdatagrid Filter syntax:- Rsdatagrid.filter="columnname=value" Syntax:- Variable="sql syntax" Cn.execute variable To create table :- Create table [table name] (fields name data type) Example:- Table:-security Fields:-username varchar(100) Password varchar(10) Insert records:- TO insert new records the following sql syntax is used:- Sql synax:- Insert into [table] values(‘value’,’value’) Updating synatax:- Update [table] set [fields,] where [referencesfields=][value] Deleting syntax:- Delete from [table] where [reference field=][value] Syntax for all retrieval:- Select * from [table] For partial:- Select [field,] from [table] Arrangin order:- Select * from [table] order by [field] asc/desc Finding data of table through the any references value:- Single:- Rs.open "select [field,] from table where [reference field=][reference field],cn,adopen,adlock Call procedure Rangewise showing in data grid:- Rs.open "select [field] from table where [field] between [value ] and [value] Working with sql(by code):- 1)provider:- Syntax:- Cn.provider="sqloledb.1" 2)Connectionstring:- Connectionstring caintains the following arguments:- Arguments meaning:- User id -valid user name Password -valid password Data source -name of the server i.e. computer’s name where the server is running eg.cse01 Initial catalog -data base name that you are using and where you are connecting server. Connection syntax:- with cn .provider="sqloledb.1" .Connectionstring="user id=;password=;data source=;initial catalog=" .open End with Disconnection syntax:- [connection code] Cn.close Set cn=nothing Or If cn.state=adstateopen then cn.close [connection code] Rs.open "select * from [table] ",cn,adopen,… Call [procedure] Connectiong server by windows authentication:- Cn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=departmental;Data Source=CSECLASS" Cursor movement:- We can use the following cursor movement methods:- 1. move 2. movenext 3. moveprevious 4. movefirst 5. movelast 1. move:- It moves the cursor to steped rows forward or backward. Syntax:- Forward to two rows steped. Rs.move [number] Call [procedure] Backward to two rows steped. Rs.move [-number] Call [procedure] 2. movenext:- It is used to move cursor forward. Syntax:- Rs.movenext Call procedure 3.moveprevious:- It is used to move cursor previous. Syntax:- Rs.moveprevious Call procedure 4.movefirst:- Used to move the cursor directly to the first rows. Syntax:- Rs.movefirst Call procedure 5.movelast:- Used to move the cursor directly to the last rows. Syntax:- Rs.movelast Call procedure Error Handling while cursor movement:- (using while—–wend) While movenext Rs.movenext While rs.eof Msg "it is last" Rs.movelast Wend Call [procedure] While moveprevious:- Rs.moveprevious While rs.bof Msg "sorry,it is first" Rs.movefirst Wend Call [procedure] Column showing syntax:- Datareport.sections("section2").controls("label").caption="Column heading" Value passing syntax:- Datareport.sections("section1").controls("textbox").datafield=rs.fields(index).name Function using syntax:- Datareport.Sections("section5").Controls("Funct control").DataField = rs.Fields(index).Name
