Results 1 to 5 of 5

Thread: ADO question

  1. #1
    Join Date
    Mar 2005
    Posts
    13

    Unanswered: ADO question

    Want to understand how ADO work when using a SQL Server back-end with an MS Access 97 front-end.

    I can understans that the Connection object handles the "pipeline" between the application and the SQL database. HOW are connections to table(s) for accessing forms/list boxes/combo boxes/etc handled ? It seems that once you connect to a table, you don't get the pesky SQL Server login box EACH time you attempt to access a table.

    Any help would be appreciated.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    SQL Server - ADO

    Personally, I found it easiest to just link the tables into the Access application via ODBC and a DSN name but if you don't want to do that you can write code similar to the ADO code below to connect to the SQL Server tables (still using a DSN name though):

    Function GetCustomerViaADOAndDSN(PKID As Variant)
    Dim strSQL As String
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.Open "DSN=MyCustomerDSN", "sa", ""
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    strSQL = "Select * from MyCustomerTable where PKCustomerID = """ & PKID & """"
    rs.ActiveConnection = cnn
    rs.Source = strSQL
    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    rs.LockType = adLockReadOnly
    rs.Open
    If rs.EOF And rs.BOF Then
    MsgBox ("Customer not found.")
    rs.Close
    Set rs = Nothing
    Set cnn = Nothing
    Else
    Forms!CustomerForm!PKCustomerID = rs!PKCustomerID
    'Return other rs fields to form here.
    rs.Close
    Set rs = Nothing
    Set cnn = Nothing
    End If
    End Function
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2005
    Posts
    13
    Quote Originally Posted by pkstormy
    Personally, I found it easiest to just link the tables into the Access application via ODBC and a DSN name but if you don't want to do that you can write code similar to the ADO code below to connect to the SQL Server tables (still using a DSN name though):

    Function GetCustomerViaADOAndDSN(PKID As Variant)
    Dim strSQL As String
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.Open "DSN=MyCustomerDSN", "sa", ""
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    strSQL = "Select * from MyCustomerTable where PKCustomerID = """ & PKID & """"
    rs.ActiveConnection = cnn
    rs.Source = strSQL
    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    rs.LockType = adLockReadOnly
    rs.Open
    If rs.EOF And rs.BOF Then
    MsgBox ("Customer not found.")
    rs.Close
    Set rs = Nothing
    Set cnn = Nothing
    Else
    Forms!CustomerForm!PKCustomerID = rs!PKCustomerID
    'Return other rs fields to form here.
    rs.Close
    Set rs = Nothing
    Set cnn = Nothing
    End If
    End Function
    Thanks. I used ODBC to get the links in teh Access environment, but the programmatic connections to the recordsets are done through code. And the connection object is set first before any recordsets are connected.

    If I'm going to open a form in add mode, WHAT do I need to do, coding wise, to get the SQL Login box to go away ? I pretty much am doing what you're doing in code from module to module with one exception - I keep cnn open until the app is closed. My assumption being that cnn is an application-wide variable.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    SQL Login box

    A couple of things to check:

    1. If you have link tables, did you click the "Save password" when you linked the table?
    2. Are you using mixed authentication for SQL Server?
    3. Is the user's name in SQL Server for permissions (probably so.)
    4. Are you using a statement similar to: cnn.Open "DSN=MyCustomerDSN", "sa", ""?

    Other's might have some more suggestions to check.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2005
    Posts
    13
    Quote Originally Posted by pkstormy
    A couple of things to check:

    1. If you have link tables, did you click the "Save password" when you linked the table?
    2. Are you using mixed authentication for SQL Server?
    3. Is the user's name in SQL Server for permissions (probably so.)
    4. Are you using a statement similar to: cnn.Open "DSN=MyCustomerDSN", "sa", ""?

    Other's might have some more suggestions to check.
    1. Does "save password" mean that when I linked the tables in Access, THAT password will be saved ?
    2. I'm using DSN-less programming so we don't have to install DSNs on user's desktops.
    3. Mixed authentications being ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •