Having Trouble with ADO Connection to SQL Server

[Rich25]

I'm trying to connect to a SQL Server 6.5 Database with the following code:

Option Explicit

Private Sub Form_Load()
Dim cn As Connection
Dim rs As Recordset

Set cn = New Connection
cn.Open "Provider=SQLOLEDB.1;Password=firmlink;Persist Security Info=True;User ID=firmlink;Initial Catalog=FirmLink;Data Source=SQLTEMP"

Set rs = New Recordset
rs.Open "FIRMLINK", cn, adOpenForwardOnly, adLockReadOnly
cn.Close
Set cn = Nothing
Set rs = Nothing


End Sub


I know this will work because I had it working when creating a Data Project, but when I do it this way I get an error that says 'Execute Permission denied on object FIRMLINK, database FirmLink, owner dbo' I know I have access to this, what could be going wrong?

Thanks

 

[Tinman]

You are trying to just open the database?
If you are getting past cn.Open statement you have the connection made.
a record set expects a query... like a select statement not a database name.
rs.Open "Select * from $tablename", cn, adOpenForwardOnly, adLockReadOnly
Do until rs.eof
For i = 0 to rs.fields.count - 1
Debug.print rs.fields(i).name
Debug.print rs.fields(i).value
next
rs.movenext
Loop
rs.close
set rs = nothing
cn.close
set cn = nothing
If you are erroring out on the cn.open check the connection string and your project references make sure you have ado as a reverence which ever version you are using... also I personally don't like the New Recordset I try always to say exactly what I want. New ADODB.RECORDSET that just incase if some nit-wit I bum a dll from named something the same as in another dll I am referencing.
Chad
tinman@thelandofoz.org

Tinman,

Welcome to OZ.