"is there a way to open the connection when the form opens, and have the connection close when the form is closed?"
Put the connection in the standard module. Define the ADO connection object as public with the scope at the module level. Open the connection when you enter the main form and leave it open until you exit the app. You can always close the connection just don't set it to nothing and it will be available to use until you no longer want it, then set it to nothing and it go out of scope.
Ideally the SQL Server should have both the SQL Server logons and the NT logons enabled that way you could have used the prior suggestion.
In SQL Server 2000, I am not sure about 7, there is an OpenRowSet that will allow you to combine both the Access and the SQL Server tables in a single sql statement. This would allow accessing SQL Server tables without linking through ODBC. I will paste in an example from an access program that uses both Northwind databases, the sql server and access. You should be able to run it to test it out.
Public Function rowset()
Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString
sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
'--- could put variables in at this point for location and database name
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "
rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly
If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If
End Function
