Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create table from recordset (2000)

    Is there an easy way to create a table from a recordset? I am using Access to run enquiries on a SQL Server database and in an effort to speed things up I am calling stored procedures on the SQL Server rather than linking the SQL Server tables to Access (using ADO, Set rst = cmd.Execute where cmd is a command object). This works but in order to use the returned recordset I am looping through it and adding each record to an existing (empty) table. Although this works I suspect that by using VBA to step through the recordset I am loosing a fair chunk of the performance gains that resulted from using stored procedures. Also, if I add a field to the stored procedure I will have to change the template table that I copy the records into. As I intend to set up several of these this could be a pain.

    Is there an easier/more efficient way of doing this? Do any of the ADO objects have a methods that might help?

    Ian

  2. #2
    New Lounger
    Join Date
    May 2002
    Location
    Gen, Gen, Switzerland
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table from recordset (2000)

    A more efficient way to do this is to ask SQL Server to do it for you.
    Just tell SQL Server to create a table and to fill it and all will be done on the server. After that you can query the result

    with another procedure.

    With SQL Server you can create temporary tables that are automatically dropped at the end of the connection (when you close

    Access).
    For creating a temporary table just put ## at the beginning of the name of the table.
    If you want to keep the table just remove the ##.

    You need to have two stored procedure: one to create the table the other to show the result (a view can't access a temp

    table).
    Two procedures aren't needed for SQL server but for Access. Access has a lot of problem (A LOT!!!) with stored procs which

    give different query for result.

    The COLLATE database_default is needed because the temp tables are created in the tempdb database and it could have a

    different collating than your work's database.

    This is simpler that it could look. The harder is to make the first one, after that just copy and paste.

    If you don't know Transact-SQL (the language of SQL Server) buy a book. It's amazing what could be done with a stored proc

    executed on the server. Don't need VBA ADO code anymore !!! And it's much much faster.

    Two good books:
    Microsoft Access projects with Microsoft SQL Server at Microsoft Press (a lot of answer and workaround on bugs of Access

    projects)
    and Inside Microsoft SQL Server 2000 at Microsoft Press (very technical on SQL Server)

    For example:
    Before to open a report/form init the temp table by :
    CurrentProject.AccessConnection.Execute "EXEC dbo.procListWhatIWant 'Create'"
    in the report RecordSource put "dbo.procListWhatIWantResult"
    in the close event of the report
    CurrentProject.AccessConnection.Execute "EXEC dbo.procListWhatIWant 'Delete'"

    Hope this help !!!



    CREATE PROCEDURE dbo.procListWhatIWant
    (
    @What varchar(10)
    -- Your parameters if needed
    )
    AS
    SET NOCOUNT ON

    IF @What = 'Create'
    BEGIN

    EXEC dbo.procDeleteTable '##ListWhatIWantResult'

    CREATE TABLE ##ListWhatIWantResult
    (
    AString varchar(20) COLLATE database_default,
    ADate DateTime,
    AnAmount money,
    ANumber decimal(18,5),
    ABoolean bit
    )
    INSERT ##ListWhatIWantResult
    SELECT AString, ADate, AnAmount, ANumber, ABoolean FROM MyTable
    END
    ELSE IF @What = 'Delete'
    BEGIN
    EXEC dbo.procDeleteTable ##TempLSSLA
    END

    RETURN


    CREATE PROCEDURE dbo.procListWhatIWantResult
    AS
    SELECT * FROM ##ListWhatIWantResult

    RETURN

    CREATE PROCEDURE dbo.procDeleteTable
    (
    @TableName varchar(100)
    )
    AS
    SET NOCOUNT ON
    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = @TableName)
    BEGIN
    EXEC ('DROP TABLE ' + @TableName)
    END

    RETURN
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table from recordset (2000)

    What are you doing with the recordset, that you need to put the data into tables.....isn't it already available to you as the recordset?

  4. #4
    New Lounger
    Join Date
    May 2002
    Location
    Gen, Gen, Switzerland
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table from recordset (2000)

    I'm not sure to understand your question.

    There's no more ADO.Recordset to create the table. All is done on the server. In my sample I made a query in the first stored proc (SELECT AString, ADate, AnAmount, ANumber, ABoolean FROM MyTable) to fill the table, but if you have already a query you can replace the

    INSERT ##ListWhatIWantResult
    SELECT AString, ADate, AnAmount, ANumber, ABoolean FROM MyTable

    by

    INSERT ##ListWhatIWantResult
    SELECT * FROM MyQuery

    or if it doesn't work

    INSERT ##ListWhatIWantResult
    SELECT AString, ADate, AnAmount, ANumber, ABoolean FROM MyQuery
    (* in INSERT doesn't work all the time, for order of the fields reason)

  5. #5
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table from recordset (2000)

    I was asking the original poster why they were creating a table, once they had a recordset. Did the data need to be stored permanently, or were they just storing it temporarily. I wanted to know to present other options.

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table from recordset (2000)

    Hi Drew and jp_George,

    I am creating a product enquiry that contains, amongst other things, three transaction lists in subforms. These show all active sales orders containing the selected product, all active purchase orders containing the selected product and a list of the stock holdings for the selected product at each of the company's branches. The users use this enquiry form a lot but complain that it is grindingly slow. Currently I use standard Access queries based on linked tables, the linked tables being those on the SQL Server.

    The recordsets returned from the SQL Server will be used to populate these subforms. At present I am thinking of creating local tables then pointing the subforms at these but I suspect that creating the tables using VBA will be inefficient. Is there a way of populating the forms more directly? Should I consider calling the stored procedure using DAO so that the recordset can be applied to the forms directly? Can you even call stored procedures using DAO?

    Ian

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Create table from recordset (2000)

    How are you planning to return the recordset from SQL Server? You might consider using pass through queries - they are much quicker than running a query in Access, and only return a small amount of data. However I'm a bit surprised that you are having performance issues with subforms. We've used them very successfully with more than a million records in a table using a simple linked table and found them to be very quick. Are your subforms linked as master child or are you doing some sort of query to restrict the data? Also, is your data source a single linked table, or do you have joins to local (Access) tables?
    Wendell

  8. #8
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table from recordset (2000)

    I would recommend creating a class for each 'data set type', and then populating a collection of those classes. Then populate your forms from those collections. I have an example of doing this in <!post=Using Class modules for a fragmented data structure,216645>Using Class modules for a fragmented data structure<!/post>.

  9. #9
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table from recordset (2000)

    I am also a bit confused as to why some queries are running faster than others. I will have to find out what fields have been indexed in the SQL Server tables. Is there an easy way to do this from an mdb?

    You have alerted me to something I vaguely remember hearing someone else talking about. I always thought that Access does all the records selection, so the main problem with Access as a front end to SQL Server is that all the records have to be sent back to the mdb and that this cloggs up the network. Am I right in thinking that this is not always the case? We have three scenarios when using Access queries to examine linked SQL Server tables:
    1) A query looks at one linked table.
    2) A query looks at two linked tables, joined in the Access query.
    3) A query looks at linked tables loined to local tables.
    Do you happen to know if these are treated differently? Maybe SQL Server will send (1) just the seklected records and (3) all the records for the PC running the mdb to process?

    Ian

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Create table from recordset (2000)

    [i/<<Am I right in thinking that this is not always the case? >>[/i]
    You are correct - the ODBC drivers for SQL Server are generally smart enough to figure out that a query involves SQL Server tables, and modify the SQL string to be compatible with the T-SQL dialect so the query runs on the server and only returns the appropriate records. However when a local table is involved, it has to bring back the entire table or tables to the Jet engine in order to run the query locally. If you are running on a fast LAN (100Mbit or 1GBit) you may not notice a great difference with small tables, but otherwise the difference is dramatic.
    Wendell

  11. #11
    New Lounger
    Join Date
    May 2002
    Location
    Gen, Gen, Switzerland
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table from recordset (2000)

    Perhaps is it the time to think about Access's project ?

    Access's project (.adp instead of .mdb) are connected directly to SQL server : all the tables, views (sql server's name for query), schemas, stored procs are on the sql server.
    So your query are always executed on the server. But you can't have local tables.
    With project you can create tables and query directly on sql server.
    Microsoft says that it's the faster way to work with SQL server from Access. I haven't tested other ways, but it's true that is fast.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create table from recordset (2000)

    Access projects are faster because there is no Jet involved, but you do have to learn to write ANSI-92 SQL/T-SQL because there's really no such thing as a query in an ADP, only Views and Stored Procedures.
    Charlotte

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Create table from recordset (2000)

    I would like to see the ASP example that you mentioned in the .doc file.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  14. #14
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table from recordset (2000)

    I didn't write an ASP interface for the example db I wrote. I did write an ASP project for a similar database. The products database for our website is a larger and more complex version of the sample I wrote. http://www.marlow.com. Go to products. It's the 'viewing' side....the Admin side that let's us modify data in the database is on a secured site.

  15. #15
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create table from recordset (2000)

    Thanks everybody for your help.

    Ian

Page 1 of 2 12 LastLast

Posting Permissions

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