By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

DSNless ADO connection

P: 37
Hello all,

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

Expand|Select|Wrap|Line Numbers
  1.   Dim cn As ADODB.Connection
  2.   Dim rs As ADODB.Recordset
  3.  
  4.    'Create a new ADO Connection object
  5.   Set cn = New ADODB.Connection
  6.  
  7.    'Use the Access 10 and SQL Server OLEDB providers to
  8.    'open the Connection
  9.    'You will need to replace MySQLServer with the name
  10.    'of a valid SQL Server
  11.    With cn
  12.       .Provider = "Microsoft.Access.OLEDB.10.0"
  13.       .Properties("Data Provider").Value = "SQLOLEDB"
  14.       .Properties("Data Source").Value = "SERVER"
  15.       .Properties("User ID").Value = "username"
  16.       .Properties("Password").Value = "password"
  17.       .Open
  18.    End With
  19.  
  20.    'Create an instance of the ADO Recordset class, and
  21.    'set its properties
  22.    'Set rs = New ADODB.Recordset
  23.    With rs
  24.       Set .ActiveConnection = cn
  25.       .Source = "SELECT field1, field2 FROM table WHERE field1=1234 order by pk01;"
  26.       .LockType = adLockOptimistic
  27.       .CursorType = adOpenKeyset
  28.       .Open
  29.    End With
  30.  
  31.    'Set the form's Recordset property to the ADO recordset
  32.    Set Me.Recordset = rs
  33.    Set rs = Nothing
  34.    Set cn = Nothing
  35.  
It works fine on form load, but when i change it to form current, when the form is loaded up, access actually crashes!!

Thanks
May 16 '07 #1
Share this Question
Share on Google+
4 Replies


P: 37
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
May 16 '07 #2

ADezii
Expert 5K+
P: 8,619
Hello all,

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

Expand|Select|Wrap|Line Numbers
  1.   Dim cn As ADODB.Connection
  2.   Dim rs As ADODB.Recordset
  3.  
  4.    'Create a new ADO Connection object
  5.   Set cn = New ADODB.Connection
  6.  
  7.    'Use the Access 10 and SQL Server OLEDB providers to
  8.    'open the Connection
  9.    'You will need to replace MySQLServer with the name
  10.    'of a valid SQL Server
  11.    With cn
  12.       .Provider = "Microsoft.Access.OLEDB.10.0"
  13.       .Properties("Data Provider").Value = "SQLOLEDB"
  14.       .Properties("Data Source").Value = "SERVER"
  15.       .Properties("User ID").Value = "username"
  16.       .Properties("Password").Value = "password"
  17.       .Open
  18.    End With
  19.  
  20.    'Create an instance of the ADO Recordset class, and
  21.    'set its properties
  22.    'Set rs = New ADODB.Recordset
  23.    With rs
  24.       Set .ActiveConnection = cn
  25.       .Source = "SELECT field1, field2 FROM table WHERE field1=1234 order by pk01;"
  26.       .LockType = adLockOptimistic
  27.       .CursorType = adOpenKeyset
  28.       .Open
  29.    End With
  30.  
  31.    'Set the form's Recordset property to the ADO recordset
  32.    Set Me.Recordset = rs
  33.    Set rs = Nothing
  34.    Set cn = Nothing
  35.  
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)?
Expand|Select|Wrap|Line Numbers
  1. 'Set rs = New ADODB.Recordset
May 16 '07 #3

P: 37
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
May 16 '07 #4

ADezii
Expert 5K+
P: 8,619
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?
May 16 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.