using VB6 ADO to connect to SQL server 7

[DougP]

I want to use a DSN less connection to connect to our SQL server Database.

I hae done this in VB Script ASP but the dialect is slightly different in VB 6.

her eis my code: in VB6
Dim Conn As Object
Dim Rs1 As New ADODB.Recordset

Set Conn = server.CreateObject("ADODB.Connection&quot
Conn.Open "driver=SQL Server;server=smallbserver;uid=sa;pwd=;database=Universal;"
Set Rs1 = Conn.Execute("SELECT [EmployeeID], [FirstName], [LastName] FROM Personnel Where [EmployeeID] = '" & EmpID1 & "'&quot

I get an error "Object required" on the "Set Conn = "
line.

 

[davidrobin]

Have you set up the references for using ADODB. Do this by going into Projects->References. Ensure the line 'Microsoft ActiveX Data Objects 2.0 Library' is checked this will move it to the top of the list. Once you have done this you can achieve what you want with the following code:

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset


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

cnn.Open '<COnnectionString>
rs.Open &quot;SELECT * from staff &quot;, cnn, adOpenStatic, adLockOptimistic

'Do some stuff with the recordset

rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing

I tend not to use the CreateObject if I can help it. That is just my preference.

 

[dellyjm]

DavidRobin seems to be a professional hammer user...hit the nail on the head

 

[DougP]

Works Groovey thanks!!!!!