DSNless ADO connection

[matthooper]

Hello all,

I have this peice of code that connects to a SQL server without the DSN

  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
 
   'Create a new ADO Connection object
  Set cn = New ADODB.Connection
 
   'Use the Access 10 and SQL Server OLEDB providers to
   'open the Connection
   'You will need to replace MySQLServer with the name
   'of a valid SQL Server
   With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = "SERVER"
      .Properties("User ID").Value = "username"
      .Properties("Password").Value = "password"
      .Open
   End With
 
   'Create an instance of the ADO Recordset class, and
   'set its properties
   'Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT field1, field2 FROM table WHERE field1=1234 order by pk01;"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With
 
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
 

It works fine on form load, but when i change it to form current, when the form is loaded up, access actually crashes!!

Thanks

[matthooper]

okay, new development, if I go through the code with a break, it keeps looping through the code time and time again until access crashes

[ADezii]

Hello all,

I have this peice of code that connects to a SQL server without the DSN

  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
 
   'Create a new ADO Connection object
  Set cn = New ADODB.Connection
 
   'Use the Access 10 and SQL Server OLEDB providers to
   'open the Connection
   'You will need to replace MySQLServer with the name
   'of a valid SQL Server
   With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = "SERVER"
      .Properties("User ID").Value = "username"
      .Properties("Password").Value = "password"
      .Open
   End With
 
   'Create an instance of the ADO Recordset class, and
   'set its properties
   'Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT field1, field2 FROM table WHERE field1=1234 order by pk01;"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With
 
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
 

It works fine on form load, but when i change it to form current, when the form is loaded up, access actually crashes!!

Thanks

It sounds as if the Form's Current() Event is being fired for every Record in the underlying Recordset as it may very well be since it is dynamically assigned. Why would you want to place this code in this Event, anyway?

Is this line intentionally commented out (Line 22)?

'Set rs = New ADODB.Recordset

[matthooper]

No, I commented it out so i could fire up the forms and then uncommented it out for the post!

I wonder if its doing it for every record in the database - for now, i think ill do it in another form that pops up.

If you can think of a solution - let me know.

Thanks

[ADezii]

No, I commented it out so i could fire up the forms and then uncommented it out for the post!

I wonder if its doing it for every record in the database - for now, i think ill do it in another form that pops up.

If you can think of a solution - let me know.

Thanks

matthooper, just for curiosity - whay can't you place the code in the Form's Load Event?