Using Connection String instead of ODBC for form

[BennyLinton]

I have a database that uses ODBC for most of its connection to SQL Server. I want to get away form this model. I have the basic code for the connection stored in a module:

Option Compare Database
Public Const CONN_STRING As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MHF;Data Source=NSQL01"

And the basics for referencing and querying the database's tables, but I'm not sure how to set the datasource for the form to this code:

Dim con As adodb.Connection
Dim cmd As adodb.Command
Dim rs As adodb.Recordset
Set con = New adodb.Connection
Set cmd = New adodb.Command
con.ConnectionString = CONN_STRING
con.Open
cmd.ActiveConnection = con


cmd.CommandType = adCmdText

cmd.CommandText = "Select a.peopleID from cashbatchdetail a where a.amount is null"

Set rs = cmd.Execute

 

[CJ_London]

not looked at the rest of your code but try

Set me.recordset = cmd.Execute

note you can populate your form with a ado recordset but access built in features such as find/sort/filter will only work with dao so will generate an error. If you need these features, you will need to write your own shortcut menus

if you are doing these things using vba then you can use the ado methods instead.