setting up an DAOconnection to sql

[DebbieCoates]

can anyone point me in the right direction, having spent the past 3 hours looking at this, I am going up the wall.

I am working on a database that someone else has set up. the vb front end is connection to SQL 2000, using a DSB.

its been set up so that the database connection is created once, and from there on other stuff is issued against it.

God knows how the ins and outs work, its a nightmare. the trouble is I am trying to develope 2 new forms unfortunately they wont appear in the database until loads of other stuff has been done, so I was trying to isolatemy one page to tes tit (otherwise its a nightmare opening a vb project with 100+ forms before mine)

so, i have declared a public variable
Public dbGams As DAO.Database
End Function

then I open the database, i am not sure if i have this right, I have a DSN on my machine that is pointed to the database which works fine, not sure how to call it.

Public Function dbOpenMain() As Boolean
Set dbGams = OpenDatabase("Gams", dbDriverNoPrompt, False, "ODBC;DSN=Gams;Driver={SQL Server};Server=(Local);Database=txtDatabase;UID=txtUID;pwd=txtPassword;")
End Function

and this function should allow me to create a recodset by calling

dim rNew as dao.recordset
set rnew = Openrecordset(dbGams, "Select * from Customers")

Public Function OpenRecordset(oDB As Database, ssql As String, Optional bEdit As Boolean = True) As Object
Dim rs As Recordset
Set rs = dbGams.OpenRecordset(ssql, dbOpenDynaset, dbSeeChanges, dbOptimistic)
end function


would be gratefull for any input

 

[JerryKlmns]

I wouldn't use a DAO (Connection/Recordset) to connect to a datasource other than Jet but rather ADO, which also works with Jet (slowerly some times)