How to hardcode an Adodc?

[Deepfreeze]

This may sound silly, but I'm a newbie and I was wondering if it's possible to set up an ADO data control using code only. Ultimately what I'm after is a way to set the Data Source in the ConnectionString to a path specified in an input file. If I set up the control by dragging one onto the form, it uses that ConnectionString value and not the one I specify during the FormLoad call. Here's what I tried in FormLoad():

Adodc1.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& strDBPath & ";Persist Security Info=False"

where strDBPath is read out of the input file.

The other reason I'd like to hard code it is because I give it a dummy SQL query for the RecordSource, and then construct the one I want during FormLoad based on other input variables. So I'm doing an extra query that doesn't need to be done. Any thoughts would be appreciated!

Deepfreeze

[Clunietp]

OK

If you are binding your controls to a data control, set up your data control as normal, pointing to the correct database. Bind all of your fields.

Then remove the connection string and recordsource properties of your data control.

put this in your form_load:

    Dim rs As Recordset
    Dim cn As Connection
    
    Set cn = New Connection
    Set rs = New Recordset
    
    cn.Open "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & App.Path & "\nwind.mdb"
    
    rs.Open "Select * from Customers", cn, adOpenKeyset, adLockOptimistic
    
    Set Me.Adodc1.Recordset = rs

it works just dandy

See ya

[Deepfreeze]

Thanks for the reply, but I'm still having problems. It's like the data control is not getting refreshed with the recordset that I 'Set' it to, and thus nothing is displayed in my DataGrid. Here's what I have so far in Form_Load():

Dim strSQLByUser, strSQLTotalsByDate As String
Dim rs As Recordset
Dim cn As Connection
    
strSQLByUser = "SELECT [Time card table].Project, [Time card table].Employee, [Time card table].Date, [Time card table].Hours, [Time card table].Activity FROM [Time card table], [Employee table] where [Employee table].Employee = [Time card table].Employee and [Employee table].SSN = " & frmLogin.txtSSN.Text & " order by [Time card table].Date desc"
    
Set cn = New Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDBPath
cn.Open
    
Set rs = New Recordset
rs.ActiveConnection = cn
rs.Open strSQLByUser, cn, adOpenDynamic, adLockOptimistic
                                                
Set Adodc1.Recordset = rs
    
'Update Data grid info with DB query based on login info
'Adodc1.RecordSource = strSQLByUser
'Adodc1.CommandType = adCmdText
    
Adodc1.Refresh

Any idea what's going on?

Deepfreeze

[Clunietp]

it took me almost half an hour, but I finally figured it out:

Private Sub Form_Load()
    
    Dim rs As Recordset
    Dim cn As Connection
    
    Set cn = New Connection
    Set rs = New Recordset
    
    cn.Open "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & App.Path & "\nwind.mdb"
    
    rs.CursorLocation = adUseClient
    rs.Open "Select * from Customers", cn, adOpenKeyset, adLockOptimistic

    Set Adodc1.Recordset = rs
    
    Set DataGrid1.DataSource = Adodc1
    DataGrid1.ClearFields
    DataGrid1.ReBind

End Sub

enjoy

Tom

[Deepfreeze]

Thanks man! You rock! That worked like a charm. Thanks again!

Deepfreeze

[Clunietp]