Connecting and Reading records from SQL Db

[VRIT]

Hi,

Im sure this is mightly easy, but it just escapes me. I can do this with Access in my sleep, but I need to do it in SQL instead.

Can anyone explain to me how to connect to a SQL Db and manipulate the records, be it from reading them into a list or combo, to saving changes back to the Db.

I have added an Adodc control and added the settings required. All I need to know is the syntax in regards to opening/connecting to the Db and opening and reading the information from the tables held within the Db.

Mm think that makes sense.

I would use the help function but some muppet forgot to install it and I cannot get access to the CD's until next week.

Many thanks in advance

VRIT

 

[BrianLW]

Dim cnConn As ADODB.Connection
Dim rs As ADODB.Recordset

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

With cnConn
.Provider = "SQLOLEDB.1"
.Properties("Data Source&quot.Value = "SQLServerName"
.Properties("User ID&quot.Value = "UserName"
.Properties("Password&quot.Value = "Password"
.Properties("Initial Catalog&quot.Value = "DatabaseName"
.CursorLocation = adUseClient
.ConnectionTimeout = 0

.Open

End With

strSQL = "SELECT * FROM TableName"

rs.Open strSQL, cnConn, adOpenStatic, adLockReadOnly

 

[VRIT]

Wow that was quick.

Do I need to worry about closing the Db at all? If so is it as simpple as:

.Close

Thanks

 

[BrianLW]

Yep
rs.close
db.close

 

[VRIT]

Thank you BrianLW,

One last question:

The rs contains 3 entries, how to I extract these into say a combo box...

I tried to use the rs.getstring command but that seems to produce one string with returns in it

Thanks in advance,

Dave

 

[BrianLW]

do while not rs.eof
ComboBox1.additem rs!FieldName
rs.movenext
loop

-B

 

[VRIT]

BrianLW,

Thank you for your help.

VRIT

 

[BrianLW]

If you ever need this info again, Check out faq222-4845

- B