How to hardcode an Adodc?

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

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:

Code:

---

    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

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():

Code:

---

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

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

Code:

---

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

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

Deepfreeze