Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    96

    Question Unanswered: ADO Connection from Access to SQL Server

    Hi All,
    I'm currently implementing a c/s environment between access and SQL server. I've used ADO connections to open and return recordsets in SQL for data manipulation on Access.

    Question is that I have around 7~10 tables to open (depending on functions), would it be efficient if I simply create new adodb.recordset (s) for the for each table I'm opening to use, or is there a more efficient way? I'm not sure if my client would be making too many redundant connections to the db

    example:
    set rstA = new adodb.recordset
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic
    rst.Open "table1", sConnectionString, , , adCmdTable

    set rstA = new adodb.recordset
    .
    .

    Thanks to provide your comments.

    Cyherus..

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    If you need all 7-10 tables on the database then yes make 7-10 recordsets. Keep in mind you only need 1 connection that all 7-10 recordsets use. Furthermore if you want to keep the network traffic to a minimum use disconnected recordsets that disconnect when not needed and reconnect when a connection is needed.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Jun 2004
    Posts
    96
    so, currently, I am creating new recordsets as below, so am I creating new connections to the DB each time I create a new recordset?

    set rstA = new adodb.recordset
    rstA.Open "table1", sConnectionString, , , adCmdTable

    set rstB = new adodb.recordset
    rstB.Open "table2", sConnectionString, , , adCmdTable
    .
    .

    If I am, what can I do to use an existing connection to create multiple recordsets?

    Thanks a lot!
    Cyherus

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Make a connections

    Dim cn as ADODB.Connection

    Set cn = New ADODB.Connection

    cn.ConnectionString = "Your Connection String"

    cn.Open

    set rstA = new adodb.recordset
    rstA.CursorType = adOpenKeyset
    rstA.LockType = adLockOptimistic
    rstA.Open "table1", cn, , , adCmdTable

    set rstB = new adodb.recordset
    rstB.CursorType = adOpenKeyset
    rstB.LockType = adLockOptimistic
    rstB.Open "table2", cn, , , adCmdTable
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  5. #5
    Join Date
    Jun 2004
    Posts
    96
    okay.. thanks SIROCCO

Posting Permissions

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