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]