Please help

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.

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.

u can create an ODBC connection as per the following;
http://63.236.73.79/cgi-bin/bbs/wt/s...b&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

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

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.

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

Get the results as
rs.Fields(0)

Srinika

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 ,