Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Unanswered: Create a copy of a table and make query on it

    Sorry for the topic!...

    I have to copy an entire SQL Server Table into a "recordset" in VB6 and mantain the possibility of execute sql query on it without querying SQL Server.

    The operation's structure is something like this:

    Table SQL Server --> Recordset in VB6

    Recordset in VB6 --> Query SQL --> Another Recordset in VB6

    etc...

    I have to make something like 300 query per second and my SQL Server is already busy...


    thanks a lot.

    Mauro

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    A recordset in VB (DAO or ADO) is nothing else but a representation of a cursor. You cannot run queries against it. If you cannot put load on your database server to run your queries you will have to copy the data into another database engine on the local machine and run the queries against that.

  3. #3
    Join Date
    Jan 2004
    Posts
    184

    Re: Create a copy of a table and make query on it

    Apel is incorrect in his statement.

    You can do what you want by creating a disconnected recordset.

    To make a disconnected recordset all you need is 2 conditions

    1) You must choose a CursorLocation of adUseClient
    2) After the query has been run you must set the ActiveConnection to Nothing.
    (If you plan on updating the database after you changes set the LockType to adLockBatchOptimistic)

    Now you can make all the changes you want to your local recordset, when you want to update the database on the server you reset your ActiveConnection to a valid connection object and you call the method

    Recordset.UpdateBatch

    Let me recommend this very usefull tool on download.com:

    http://download.com.com/3000-2404-10...ml?tag=lst-0-1

    It does all this for you automatically.

    Originally posted by impelsys
    Sorry for the topic!...

    I have to copy an entire SQL Server Table into a "recordset" in VB6 and mantain the possibility of execute sql query on it without querying SQL Server.

    The operation's structure is something like this:

    Table SQL Server --> Recordset in VB6

    Recordset in VB6 --> Query SQL --> Another Recordset in VB6

    etc...

    I have to make something like 300 query per second and my SQL Server is already busy...


    thanks a lot.

    Mauro

  4. #4
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    You missed the point there. Impelsys wants to run queries against the recordset. Of course you can use the recordset without a database connection but a recorset does not allow you to run queries against it just because it does not provide the database engine to run these (with at least a sql parser and a query engine) . Again, a recordset is a cursor in the SQL sense. You can scroll through it, read, update, delete or whatever with it but not run queries against it.

    Edit:
    You can of course always sequencially scan the recordset to find your data but I don't even want to think about the memory bandwith you'd need for that when doing 300 queries/s.
    Last edited by Apel; 01-30-04 at 10:14.

Posting Permissions

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