Using Connection String instead of ODBC for form (1 Viewer)

BennyLinton

Registered User.
Local time
Today, 05:45
Joined
Feb 21, 2014
Messages
263
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

Super Moderator
Staff member
Local time
Today, 12:45
Joined
Feb 19, 2013
Messages
16,553
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.
 

Users who are viewing this thread

Top Bottom