Temporary Table

[asm]

Hi

I have to create a temporary table for generating a report in VB.
Pls help how to check the temporary table name in database.
I want if exits than drop and create a new one.
(database : access2000)

thanks

asm

[trisuglow]

I believe that this code will give you a list of tables in an Access database. I haven't been able to test it though.

SELECT Name FROM MSysObjects
WHERE Type=1 AND Flags=0

Dropping tables is simple:

DROP TABLE t_test

I got all the above information from here

[Dnereb]

What rally springs to mind is this:

Why do you need a table instead of a Query?

But you want an anwer instead of a question I suppose.....

Sub AddTable()

Dim db As DAO.Database
Dim tbl As DAO.TableDef

' you should look at the optional arguments too

Set db = DAO.OpenDatabase("Yourdatabase")

Set tbl = db.CreateTableDef("Test2")

tbl.Fields.Append tbl.CreateField("Id", dbLong)
tbl.Fields.Append tbl.CreateField("Name", dbText, 50)
tbl.Fields.Append tbl.CreateField("Stmp", dbDate)

db.TableDefs.Append tbl

Set tbl = Nothing
db.Close
Set db = Nothing

End Sub

will make a table the SQL DROP table as suggested will delete it
you could also use the SQL CREATE TABLE to build one but in Access DAO should work fine

[mendhak]

One could often use Temporary tables for very complex queries.

Thing is, I don't believe temporary tables exist in Access.

[Hack]

Thing is, I don't believe temporary tables exist in Access.

Sure they do....you just create a table you want to use temporarily and then DROP it afterwards.

[mendhak]

Rofl.

[asm]

Hi

I am generating a accounts ledger (using cr6)
1. generate query (GenLed)
2. from vb6 - create two tables Ogenled & Tgenled from query genled
Ogenled - Data before from date enter by user
Tgenled - Data between from date - To date enter by user
3. Update tgenled table from ogenled table (summary of amount) as opening in tgenled (if code found in tgenled else addnew in tgenled)

Private Sub CmdPreview_Click()
    CmdPreview.Enabled = False
    Dim strRepName As String
    Dim SqlString As String
    Set cmdobj = New Command
    Set ComObjUpd = New Command
    Set RoGenLed = New ADODB.Recordset
    Set RtGenLed = New ADODB.Recordset
    Dim opamt As Double
    Dim opac As String
    
'   Create Temporary Table - Preview Report
    With cmdobj
        .ActiveConnection = ConnectAcct
        .CommandText = "SELECT * INTO OGENLED FROM GENLED WHERE DOCDATE <#" & TxtFromDate.Text & "# OR (OAMOUNT <> 0 AND DOCDATE IS NULL) ORDER BY GLNAME"
        .Execute
        .CommandText = "SELECT * INTO TGENLED FROM GENLED WHERE DOCDATE >=#" & TxtFromDate.Text & "# AND DOCDATE<=#" & TxtToDate.Text & "# ORDER BY GLNAME"
        .Execute
    End With
 
'   Update Opening Amount from Ogenled table to TGENLED table
    With cmdobj
        .ActiveConnection = ConnectAcct
        .CommandText = "select glname, oamount, sum(tamount) as tamt from ogenled group by glname, oamount order by glname"
    End With
    Set RoGenLed = cmdobj.Execute
    RtGenLed.Open "TGENLED", ConnectAcct, adOpenKeyset, adLockPessimistic, adCmdTable
    If Not ((RoGenLed.BOF = True) And (RoGenLed.EOF = True)) Then
        RoGenLed.MoveFirst
        Do While Not RoGenLed.EOF
            opamt = 0
            opac = RoGenLed!glname
            If IsNull(RoGenLed!tamt) Then
                opamt = RoGenLed!oamount
            Else
                opamt = RoGenLed!oamount + RoGenLed!tamt
            End If
            If opamt <> 0 Then
                If Not RtGenLed.EOF And Not RtGenLed.BOF Then
                    RtGenLed.MoveFirst
                End If
                Do While Not RtGenLed.EOF
                    If UCase(opac) = UCase(RtGenLed!glname) Then
                        RtGenLed!oamount = opamt
                        RtGenLed.Update
                        GenFound = True
                    End If
                    RtGenLed.MoveNext
                Loop
                'Not found then add new
                If GenFound = False Then
                    RtGenLed.AddNew
                    RtGenLed!glname = opac
                    RtGenLed!oamount = opamt
                    RtGenLed.Update
                End If
            End If
            RoGenLed.MoveNext
        Loop
    End If
    RtGenLed.Requery
    RoGenLed.Close
    RtGenLed.Close
 
'   Report Preview in Crystal Report
    strRepName = "Ledger.rpt"
    SqlString = "SELECT * FROM TGENLED ORDER BY GLNAME, DOCDATE"
    Me.CrystalReport1.ReportFileName = strRepName
    Me.CrystalReport1.SQLQuery = SqlString
    Me.CrystalReport1.Destination = crptToWindow
    Me.CrystalReport1.Connect = "dsn= " & DsnName & ""
    Me.CrystalReport1.Formulas(0) = "compname = '" & CompanyName & "'"
    Me.CrystalReport1.Formulas(1) = "rfdate = '" & TxtFromDate.Text & "'"
    Me.CrystalReport1.Formulas(2) = "rtdate = '" & TxtToDate.Text & "'"
    Me.CrystalReport1.Action = 1
 
'   Drop Temporary Table - Preview Report
    With cmdobj
        .ActiveConnection = ConnectAcct
        .CommandText = "DROP TABLE OGENLED"
        .Execute
        .CommandText = "DROP TABLE TGENLED"
        .Execute
    End With
    CmdPreview.Enabled = True
    cmdobj.ActiveConnection = Nothing
    Set RoGenLed = Nothing
    Set RtGenLed = Nothing
End Sub

Can it possible with query or single table.
pls guide because this will take too much time.


thanks

asm

[si_the_geek]

There are a few code "issues" in the section "' Update Opening Amount from Ogenled table to TGENLED table", such as:

• You do not need to use a Command to open a recordset with SQL, you could do this instead:
  

  SqlString = "select glname, oamount, sum(tamount) as tamt from ogenled group by glname, oamount order by glname"
      RoGenLed.Open SqlString, ConnectAcct, adOpenKeyset, adLockPessimistic, adCmdText

• There is no need for "RoGenLed.MoveFirst", as this is done automatically.
• The line "If Not ((RoGenLed.BOF = True) And (RoGenLed.EOF = True)) Then" has redundant parts, this would do the same job a little quicker:
  

  If Not (RoGenLed.BOF And RoGenLed.EOF) Then

• There is no need for a Requery at the end - all this does is reload the data

Of course the quickest method is not to use VB code at all (as SQL is quicker), and the entire section can be replaced by these two SQL statements:

UPDATE tgenled
SET oamount = o.oamount + NZ(o.tamt,0)
FROM ogenled o
WHERE tgenled.glname = o.glname

INSERT INTO tgenled (glname, oamount)
SELECT o.glname, o.oamount + NZ(o.tamt,0)
WHERE UCase(o.glname) NOT IN (SELECT UCase(glname) FROM tgenled)

Or, even better, look at the SQL that you use to generate the two temporary tables - they can be combined into one fairly easily. As they can be combined, you could just use this as the SQL for the report rather than creating the table(s) at all!

I think this would work:

sqlString = "SELECT * " _
          & "FROM GENLED " _
          & "WHERE (OAMOUNT <> 0 AND DOCDATE IS NULL) " _
          & "OR DOCDATE <#" & TxtFromDate.Text & "# " _
          & "OR (DOCDATE >=#" & TxtFromDate.Text & "# AND DOCDATE<=#" & "TxtToDate.Text & "#) " _
          & "ORDER BY GLNAME

I'm not 100% sure, but I think that Between would do the same as the last 'or' line, and would probably be a little quicker.

[Dnereb]

Between is slower in most cases.
Between is executed when the data has been tranfered to the recordset (it's a filter like instruction) and WHERE is executed before the data is transfered in normal recordsets (hirarchal recordsets are a diffrent ballgame)
So stick to WHERE

[si_the_geek]

What??? That doesn't make any sense to me, Between is as much a part of the Where clause as the > and < signs it is used to replace.

My only doubt is whether it is the equivalent of ">= AND <=" or "> AND <"

[Dnereb]

I've got it from a pretty reliable source ->
Frank balena, programming visual basic 6.0 (Microsoft press)

[si_the_geek]

I would be very surprised if it is any different from "> and <", as that is essentially what the SQL means. I know that on SQL Server there is no difference between the two, and I would be surprised if Access is any different.

I'm guessing that you may have mis-interpreted what was said, or it applies to something different. I would be interested to read it tho, if you can post the section that explains that.

[Dnereb]

I could be mistaken between and having but I'll look it up for you.

[salvelinus]

HAVING operates on the query results.
BETWEEN works like >= ...<=, but not sure at what point.

[si_the_geek]

Yep, Having works on the 'results', and should only be used for computed columns created as part of the Group By.

For most DBMS's Between works in the same manner as using >= etc, ie: within the initial Where clause, in order to build the result set.

[Dnereb]

searched long and hard but I have been mixing them up.
>, <, = , Like and between are all proccesed the same.