ADO question

[SmileeTiger]

Hi,

I want to do the following:
open the ADO recordset tblPart on the SQL server called TESTFUSION1. Using the user genericuser with no password.

Will the following solution work?

Dim obConnection As New ADODB.Connection

obConnection.Provider = "SQLOLEDB.1"
obConnection.Properties("Data Source&quot = "TESTSERVER1"
obConnection.Properties("User ID&quot = "genericuser"

obConnection.Open

How will I then open the recordset?

Smilee

 

[mmilan]

Don't know about the connection object (I'm not a SQL Server bod), but as for the recordset...

dim rs as adodb.recordset
set rs = new adodb.recordset

rs.open "select * from tblpart",obConnection,addynamic,adoptimistic

 

[recurse]

Try the following:

dim cn as new adodb.Connection
dim rs as adodb.recordset

cn.open "Provider=SQLOLEDB.1;Persist Security info=True;User ID=genericuser;Password=;Initial Catalog=YOUR_DATABASE_NAME;Data Source=TESTFUSION1"

set rs = cn.execute("select * from tblPart"

 

[1127]

Hi,

As You said You need a recordset so..?
You need a recordset object.
You can open a recordset object with a command object result but if You just want to retreive some records then You should use a SQL string as source (in this case the last argument is adcmdtext). If You want to open the whole database use the adcmdtable argument.
Try to use the most perfect arguments for Your needs.
ex. if You want to load the data into a list You have to open the recordset adopenforwardonly and adlockreadonly.

 

[VBDoc]

Help Tip:
Use a data control and build a connection string. You can test the connection through the control. Then DIM a string (Dim sConnection as string). Then cut and paste the string created from the control (see properties). You can now delete the control for you have a connection string that will work.
It will look somewhat like this on your form.

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open sConnection

' *** Now build your SQL statement
Sql = "SELECT * FROM tbl"

' *** Now get the recordset
rs.open sql

' *** Test to see if we got something
if not rs.eof then
' do work here

end if

' *** Don't forget to close.
rs.close ' recordset first
cn.close ' connection last

regards,
vbDoc
vb_doc@hotmail.com

 

[dvannoy]

Put this in a module:

Global cn As ADODB.Connection
Global strCn As String

strCn = "driver={SQL Server};" & _
"server=Server;uid=sa;pwd=;" & _
"database=DB"

Set cn = New ADODB.Connection

With cn
.ConnectionString = strCn
.Provider = "msdasql"
.ConnectionTimeout = 60
.Open strCn
End With

on the form:

Dim rs As ADODB.Recordset
Dim strRs As String


strRs = "SELECT * FROM Table"

Set rs = New ADODB.Recordset

rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.Open strRs, strCn

rs.Close
Set rs = Nothing


Then you can check where you are in the recordset so you can do a rs.MoveFirst etc..

Hope this helps