Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Dublin,Ireland
    Posts
    13

    Red face Unanswered: VBA and SQL Server

    Hi this is my first thread ever!!!

    I want to create a temporary table that i can use and then drop later,
    I know the temp table is created because a reference to the table appears in Tempdb in
    SQL but i cant run a select on it Heres the VBA code i wrote to do it .......

    PLEASE HELP COS IT DOESNT WORK RIGHT!!!


    '================================================= ===========
    ' retrieve the table name from the global variable "strTmpQdTblName"
    strTmpQdTblName = "#QdBatchTbl" & TrimSpaces(Forms![MainMenuFrm].tbAMName)

    ' CHECK if the temp table exists already, if so drop it and create a new one.
    strSql = ""
    strSql = "IF OBJECT_ID('tempdb.." & strTmpQdTblName & "') IS NOT NULL" & vbCrLf & _
    "DROP TABLE " & strTmpQdTblName & ""

    DoCmd.SetWarnings False
    DoCmd.RunSQL strSql
    DoCmd.SetWarnings True


    ' CREATE the empty temporary batch table here
    strSql = ""
    strSql = "CREATE TABLE [dbo].[" & strTmpQdTblName & "] (" & _
    "[BatchKey] [int] IDENTITY (1, 1) NOT NULL ," & _
    "[Ordering] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ," & _
    "[TaskKey] [int] NOT NULL ," & _
    "[ClientTaskAlias] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ," & _
    "[Description] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ," & _
    "[Quantity] [real] NOT NULL ," & _
    "[Rate] [money] NOT NULL ," & _
    "[Unit] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ," & _
    "[ProdComp] [nvarchar] (100) COLLATE Latin1_General_CI_AS NOT NULL" & _
    ") ON [PRIMARY]"

    DoCmd.SetWarnings False
    DoCmd.RunSQL strSql
    DoCmd.SetWarnings True

    ' set up the datasource for the batch quote forms sub-form.
    Form_QtBatchTaskSubform.Form.RecordSource = "Select * from " & strTmpQdTblName

    '================================================= ===========



    ANY IDEAS AS TO WHY IT FAILS ????

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: VBA and SQL Server

    Your temp table is a local one ("#QdBatchTbl & ....), once the statement from the command line executed, the table is dropped by the SQL Server. (temporary tables are automatically dropped when they go out from scope) You should use a global one instead (preceded by ##)




    Originally posted by MaxJam
    Hi this is my first thread ever!!!

    I want to create a temporary table that i can use and then drop later,
    I know the temp table is created because a reference to the table appears in Tempdb in
    SQL but i cant run a select on it Heres the VBA code i wrote to do it .......

    PLEASE HELP COS IT DOESNT WORK RIGHT!!!


    '================================================= ===========
    ' retrieve the table name from the global variable "strTmpQdTblName"
    strTmpQdTblName = "#QdBatchTbl" & TrimSpaces(Forms![MainMenuFrm].tbAMName)

    ' CHECK if the temp table exists already, if so drop it and create a new one.
    strSql = ""
    strSql = "IF OBJECT_ID('tempdb.." & strTmpQdTblName & "') IS NOT NULL" & vbCrLf & _
    "DROP TABLE " & strTmpQdTblName & ""

    DoCmd.SetWarnings False
    DoCmd.RunSQL strSql
    DoCmd.SetWarnings True


    ' CREATE the empty temporary batch table here
    strSql = ""
    strSql = "CREATE TABLE [dbo].[" & strTmpQdTblName & "] (" & _
    "[BatchKey] [int] IDENTITY (1, 1) NOT NULL ," & _
    "[Ordering] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ," & _
    "[TaskKey] [int] NOT NULL ," & _
    "[ClientTaskAlias] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ," & _
    "[Description] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ," & _
    "[Quantity] [real] NOT NULL ," & _
    "[Rate] [money] NOT NULL ," & _
    "[Unit] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ," & _
    "[ProdComp] [nvarchar] (100) COLLATE Latin1_General_CI_AS NOT NULL" & _
    ") ON [PRIMARY]"

    DoCmd.SetWarnings False
    DoCmd.RunSQL strSql
    DoCmd.SetWarnings True

    ' set up the datasource for the batch quote forms sub-form.
    Form_QtBatchTaskSubform.Form.RecordSource = "Select * from " & strTmpQdTblName

    '================================================= ===========



    ANY IDEAS AS TO WHY IT FAILS ????
    Steve

  3. #3
    Join Date
    Dec 2003
    Posts
    17
    Don't use a global temp table because when your application goes live and there are multiple users, it means that all users will automatically reference the same global temp table. You can query a temp table from VBA if you use ADO. If you create your temp table using an ADO connection you can query the temp table using the same ADO connection. As long as the ADO connection is not closed or goes out of scope the temp table will still be there.

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Exclamation

    I definitely agree. Temporary tables exist per-connection and disappear when the connection goes. There is no effective substitute for them (i.e. a permanent table of any name), because of conflicts.

    Gotta tell ya, tho', I get really nervous when I see temp-tables being used in any database. I don't like to see per-connection resources of any kind piling up on the server, especially potentially-large ones. I like to see the server-environment remaining pristine and thus not as likely to run into capacity-problems as the number of users (inevitably) explodes. (Timing's easy. I can simply "add more iron" to fix timing. But it's much harder to uncork a plugged-up system...)

    If you need a temporary table, can you for example get the data off the server and put the necessary data, say into a local MS-Access-style table on the workstation, where it's basically "free of charge?" Provided that the amount of data to be transferred is not excessive (and if the user wants to query 'everything' then "just say No!") then this is a really nice way to do things. Now the user's computer is being used as a computer in its own right...

    As a rule of thumb, all SQL-servers are most friendly to an environment (whether a "connection" is persistent or not), where you: bang! go in, run your query, get your results, and bang! close the query and the server's on to the next transaction. Servers of all kinds can reliably chom through thousands of those things a second. But it's when things build-up or start "lasting a long time" (and one-second is a real long time) that you get inefficiencies, escalating locks, and all sorts of other undesirable things. Particularly under load. And the avoidance of that is a software design issue.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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