[AccessD] ADO recordsets and connection objects

What you want to do is download the MDAC SDK to learn ado
http://msdn.microsoft.com/data/mdac/downloads/default.aspx
Microsoft Data Access Components (MDAC) 2.8 Software Development Kit (15 
Meg)
In there, it will install a help file ADO260.CHM in
C:\Program Files\Microsoft Data Access SDK\Docs\
Look through help file index for open ADO recordset parameters
There are lots of sample VB code examples in the help file.

I don't use udl's that much, this may work
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String

sSQL = "SELECT * FROM Products"

' May need to Set a reference to "Microsoft OLE DB Service Component 1.0 
Type Library
Set con = New ADODB.Connection
con.connectstring = "c:\path to \your file.udl"
con.open
Set rs = New ADODB.Recordset
rs.Open sSQL, con
If Not rs.EOF Then
MsgBox rs(0)
rs.MoveNext
End If

This should work with SQL and northwind mdb haven't tested in awhile

Sub adotest()
Dim sSQL As String
Dim iNumRecords As Integer

Dim oConnection As ADODB.Connection
Dim oRecordset As ADODB.Recordset
Dim rstSchema As ADODB.Recordset
Dim sConnStr As String

'sConnStr = "Provider=SQLOLEDB;Data Source=MySrvr;" & _
"Initial Catalog=Northwind;User Id=MyId;Password=123aBc;"
' Connection "Provider=Microsoft.Jet.OLEDB.3.51;Data 
Source=D:\DataBases\Northwind.mdb"

'Access 97 version Jet 3.51
' sConnStr = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data Source=C:\Program Files\Microsoft 
Office\Office\Samples\Northwind.mdb;" & _
"User Id=admin;" & "Password="
'Access XP Jet 4
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft 
Office\Office\Samples\Northwind.mdb;" & _
"User Id=admin;" & "Password="

On Error GoTo GetDataError

' Create and Open the Connection object.
Set oConnection = New ADODB.Connection
oConnection.CursorLocation = adUseClient
oConnection.Open sConnStr

sSQL = "SELECT ProductID, ProductName, CategoryID, UnitPrice " & _
"FROM Products"

' Create and Open the Recordset object.
Set oRecordset = New ADODB.Recordset
oRecordset.Open sSQL, oConnection, adOpenStatic, _
adLockBatchOptimistic, adCmdText


blah blah with recordset

' Disconnect the Recordset.
Set oRecordset.ActiveConnection = Nothing
oConnection.Close
Set oConnection = Nothing

Exit Sub
GetDataError:
If oConnection Is Nothing Then
HandleErrs "GetData", oRecordset.ActiveConnection
Else
HandleErrs "GetData", oConnection
End If

Exit Sub
End Sub
Sub HandleErrs(sSource As String, ByRef oConnection1 As ADODB.Connection)
Dim sDisplayMsg As String
sDisplayMsg = sDisplayMsg & "ADO (OLE) ERROR IN " & sSource
sDisplayMsg = sDisplayMsg & vbCrLf & "Error: " & Err.Number
sDisplayMsg = sDisplayMsg & vbCrLf & "Description: " & Err.Description
sDisplayMsg = sDisplayMsg & vbCrLf & "Source: " & Err.Source

If Not oConnection1 Is Nothing Then
If oConnection1.Errors.Count <> 0 Then
sDisplayMsg = sDisplayMsg & vbCrLf & "PROVIDER ERROR"
Dim oError1 As ADODB.Error
For Each oError1 In oConnection1.Errors
sDisplayMsg = sDisplayMsg & vbCrLf & "Error: " & oError1.Number
sDisplayMsg = sDisplayMsg & vbCrLf & "Description: " & oError1.Description
sDisplayMsg = sDisplayMsg & vbCrLf & "Source: " & oError1.Source
sDisplayMsg = sDisplayMsg & vbCrLf & "Native Error:" & oError1.NativeError
sDisplayMsg = sDisplayMsg & vbCrLf & "SQL State: " & oError1.SQLState
Next oError1
oConnection1.Errors.Clear
Set oError1 = Nothing
End If
End If

MsgBox "Error(s) occurred. See sDisplayMsg for specific information.", , _
"Hello Data"
MsgBox sDisplayMsg
Err.Clear
End Sub
Bud Goss wrote:

>I am trying to learn the basics of ADO recordsets and connections.
>  So far, I have been unable to get to first base. The two methods listed below give me the following error:
>   
>  
>  Run-time error'-2147467259(80004005)
>  [Microsoft][ODBC Driver Manager] Data source name not found and no
>  default driver specified
>   
>  First attempt with udl file:
>   
>  Public Sub MyADOTest01()
>  Dim rec As ADODB.Recordset
>  Set rec = New ADODB.Recordset
>  rec.Open "Select * from [Orders] ", "File Name = C:\B\A\x.udl"
>  rec.CursorLocation = adUseClient
>  rec.LockType = adlockBatchOptimistic
>  MsgBox "OPEN HAS OCCURED " & rec!Customer
>  End Sub
>   
>  Second attempt without udl file

>   
>  Public Sub MyADOTest02()
>  Dim rec As ADODB.Recordset
>  Set rec = New ADODB.Recordset
>  rec.Open "Select * from [Orders]", "File Name = C:\B\A\Northwind.mdb"
>  rec.CursorLocation = adUseClient
>  rec.LockType = adlockBatchOptimistic
>  MsgBox "OPEN HAS OCCURED " & rec!Customer
>  rec.activeconnection.Close
>  End Sub
>   
>  C:\B\A\     &. Contains    x.udl   and  Northwind.mdb
>  x.udl points to C:\B\A\Northwind.mdb and has MicrosoftJet 4.0 OLE Provider specified
>   
>  Subs are contained in another Access 2003 database that is located in C:\B\A_Access_Tr
>   
>  This database does have the  reference 
>  Microsoft ActiveX data Objects 2.1 Llibrary
>  
>    
>  Any help would be appreciated.
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada