Opening Database with code!

[Tequila_worm]

Here is how i am opening it.


MODULE:

Option Explicit
 
Global gsMasterDatabasePath As String
 
Global gsMasterConnectionString As String
 
Global Const gsADOProvider = "Microsoft.Jet.OLEDB.4.0"
Public Sub EstablishDataConnections()
 
gsMasterDatabasePath = "C:\Documents and Settings\Ado\My Documents\School\Programming\Final Assignment\Testing\db1.mdb"
gsMasterConnectionString = "Provider= " & gsADOProvider & ";Data Source=" & gsMasterDatabasePath
 
End Sub

FORM:

Option Explicit
 
Dim connTEST As ADODB.Connection
Dim rsTEST As ADODB.Recordset
 
 
Private Sub Form_Load()
    Set connTEST = New ADODB.Connection
    Call EstablishDataConnections
    connTEST.Open gsMasterConnectionString
    Set rsTEST = New ADODB.Recordset
    Dim SQL As String
    SQL = "SELECT Main.*, Info.Contents FROM Main Inner Join Info ON Main.CdId = " & _
    "Info.CdId "
    
    rsTEST.Open SQL, connTEST, adOpenStatic, adLockReadOnly, adCmdText
    connTEST.Close
    Set connTEST = Nothing
    Set rsTEST = Nothing
    
End Sub

Now what i want to do is connect my textbox to the database
i am trying this but its not working

txtText.DataSource = rsTest
txtText.DataField = rsTest!CdId


I mean basically that wont work, any ideas on how i can connect the textbox to the database?

[Pickler]

Use

Set txtText.DataSource = rsTest 
txtText.DataField = "CdId"

also remove
connTEST.Close
and
Set rsTEST = Nothing
or else I will not work either.

[Tequila_worm]

Thanks alot bro, that worked fine thanks again.

[sandin]

hi...
There is no need to use the module in this programme.
just go through with the following code..
---------------------
decularation
Public connTEST As ADODB.Connection
Public rsTEST As ADODB.Recordset

Set connTEST = New Connection
With connTEST
.Provider = "Microsoft.Jet.Oledb.4.0"
.ConnectionString = App.Path & "\Testing\db1.mdb"
.Open
End With

set rsTEST = new adodb.recordset
rsTEST .Open "select * from assemblymaster", connTEST , adOpenKeyset, adLockOptimistic (e.g.)

or...
Dim SQL As String
SQL = "SELECT Main.*, Info.Contents FROM Main Inner Join Info ON Main.CdId = " & _
"Info.CdId "


now check the BOF and EOF
i.e.
if EOF and BOf = true then
txtText.text = rsTEST! fieldname(i.e. which field U want to assign)
end if

in this way u can assign the recordset field to the textbox....
for navigation use the move next , move last , previous and next property of the recordset.

bye
sandin