Please help

[Shiv Kumar G.M.]

Hello gurus,

May i get some code on how to connect to a database/table through ADO (only through code) not by setting its properties.

Regards,

Shivakumar G.M.

[siddharth_sp]

Public Function OpenConnection(strConnectionString As String) As Connection

On Local Error GoTo OpenConnection

Dim objConnection As Connection

'create a new connection object
Set objConnection = New ADODB.Connection

'open a connection to the database
Call objConnection.Open(strConnectionString)

'return true if success
Set OpenConnection = objConnection
Exit Function

OpenConnection:
'return false if failure
Set OpenConnection = Nothing

End Function

S/W Developer
India.

[srinika]

u can create an ODBC connection as per the following;
http://63.236.73.79/cgi-bin/bbs/wt/showpost.pl?Board=vb&Number=63834

The u'll have to define a variable as New ADODB.Connection
use the method Open of that Object and pass the DSN as the Parameter

Then Pass the SQL String u want, to get that executed to the Execute Method of that Object


Dim con as new ADODB.Connection
Dim rs as new ADODB.Recordset

con.Open "My_dsn"

set rs = con.Execute("Select * from t1", , adCmdText)
Do While rs.EOF = false
List1.AddItem rs!f2
rs.MoveNext
Loop

con.Execute "Delete from t1 where f2 = " & Var1

[Cimperiali]

option Explicit
'put a reference to Microsoft Activex Data Object 2.x Library
'where x stands for 1 or 5 or 6 or more
private cnn as ADODB.Connection
private rs as ADODB.Recordset

private Sub Command1_Click()
Dim strSql as string, strConnection as string
Dim DbPathName as string
'change the following to your db path and name
DbPathName = "D:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb"
'you can copy the following string from the
'connection string of a Adodc control if you need
'a help in building it (remove the component after you've used.
' This is connection string for an Access database without password
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DbPathName & ";Persist Security Info=false"
If cnn is nothing then
set cnn = new ADODB.Connection
End If
cnn.ConnectionString = strConnection
cnn.Open
'this for updating, deleting, inserting:
'Dim retVar 'here ado will put the number of records affected
'strsql = "Update" etc.
'cnn.Execute strSql, retVar
'this for selecting records:
strSql = "SELECT * From Authors WHERE (Authors.Au_ID)<12"
set rs = cnn.Execute(strSql)
Do While rs.EOF = false 'if you have records then do...
'now, what do you want to do?
'suppose you simply want to display values on form
'You vcan refer to a filed by name or by index position
me.print rs("Author").Value & Chr(9) & " Born in " & rs(2).Value & Chr(9) & "Id record= " & rs(0).Value
'chr(9)= spaces informations by a tab
rs.MoveNext 'go to next record
Loop
'when you finish using recordsets and connections,
'close them and release memory:
rs.Close
set rs = nothing
cnn.Close
set cnn = nothing
End Sub





Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Michael
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[Shiv Kumar G.M.]

Hello,

This works out but the .RecordCount is -1. What I have been doing is loooping till .EOF and then get total number of records.
Anyone can say how do i get it.

Regards,
Shivakumar G.M.

[srinika]

Pass the Following SQL :
"Select Count(*) as NoOfRecords from table1 "

Get the results as
rs.Fields(0)

Srinika

[wajahath]

set the connection object
(what ever u are using let us say it is "con")

con.CursorLocation=adUseClient




this must be written before you open the record set

Hope it is clear ,