Making a temp table

[Telsa]

I'm trying to aggregate data and was thinking of creating a temporary table to place the data in. Is this how I get started?

Set tblTemp = CurrentDb.CreateTableDef("tblTemp&quot
With tblTemp
.Fields.Append .CreateField("PartNo", dbText)
.Fields.Append .CreateField("ProjName", dbText)
.Fields.Append .CreateField("Base", dbText)
.Fields.Append .CreateField("CDAssembly", dbText)
.Fields.Append .CreateField("Component", dbText)
.Fields.Append .CreateField("Lang", dbText)
End With

Set rsTemp = CurrentDb.OpenRecordset("INSERT INTO tblTemp SELECT DESTINCT Partno, " _
& " projectname, base, cdassembly, component FROM tblCDAssemblyandLangJoin&quot


Then I hope to create some loops that will populate the Lang field.

Opinions anyone???

Mary

 

[Amiel]

You'll need to append the new table def before using it.

db.TableDefs.Append tblTemp

Further, although temporary conceptually, it is not temporary functionally, until you remove it. Amiel
amielzz@netscape.net

 

[Telsa]

Thanks! I did add a line before it to remove any existing table.

But now I'm getting a parameter error (too few, expected 5)... could you look at my complete procedure and see what I'm missing?


Sub LanguageRecordset()
Dim strLang As String
Dim rs As Recordset
Dim rs2 As Recordset
Dim rsTemp As Recordset
Dim sqlTable As String
'Dim sqlTemp As String
Dim tblTemp As TableDef

'Dump the old table
CurrentDb.TableDefs.Delete "tblTemp"

' Create New Table
Set tblTemp = CurrentDb.CreateTableDef("tblTemp&quot
With tblTemp
.Fields.Append .CreateField("PartNo", dbText)
.Fields.Append .CreateField("ProjectName", dbText)
.Fields.Append .CreateField("Base", dbText)
.Fields.Append .CreateField("CDAssembly", dbText)
.Fields.Append .CreateField("Component", dbText)
.Fields.Append .CreateField("Lang", dbText)
End With

' Add this table to the db
CurrentDb.TableDefs.Append tblTemp

' Update the table with distinct data but no languages (yet)
sqlTable = "INSERT INTO tblTemp SELECT DISTINCT tblCDAssemblyandLangJoin.partno, tblCDAssemblyandLangJoin.base, " _
& "tblCDAssemblyandLangJoin.projectname, tblCDAssemblyandLangJoin.cdassembly, " _
& "tblCDAssemblyandLangJoin.component FROM tblCDAssemblyandLangJoin;"

' Run that SQL!
DoCmd.RunSQL sqlTable

Set rs = CurrentDb.OpenRecordset("SELECT partno, projectname, base, cdassembly, " _
& " component, lang FROM tblTEMP&quot
rs.MoveFirst
Do While Not rs.EOF
Set rs2 = CurrentDb.OpenRecordset("SELECT language FROM tblCDAssemblyandLangJoin " _
& " WHERE partno = rs!partno AND projectname = rs!projName AND base = rs!base AND " _
& " cdassembly = rs!cdassembly AND component = rs!component;&quot

rs2.MoveFirst

Do While Not rs2.EOF
strLang = strLang & rs2!lang & ", "
Loop
strLang = Left(strLang, Len(strLang) - 2)
rs.Edit
rs!lang = strLang
rs.Update
Loop


End Sub


Thanks!

Mary

 

[Amiel]

I cannot find anything wrong with your code. I have found in the past that when creating new tables with DAO, that the new table is not available immediately for some reason.

I attribute it concurrency issues withing Access. But I don't have the definitive answer. However;

Here's how I handle this kind of thing.


Sub LanguageRecordset()
Dim strLang As String
Dim rs As Recordset
Dim rs2 As Recordset
Dim rsTemp As Recordset
Dim sqlTable As String
'Dim sqlTemp As String
Dim tblTemp As TableDef

sqlTable = " SELECT DISTINCT tblCDAssemblyandLangJoin.partno as [Part Number], tblCDAssemblyandLangJoin.base as [Base], " _
& "tblCDAssemblyandLangJoin.projectname as [Project Name], tblCDAssemblyandLangJoin.cdassembly, " _
& "tblCDAssemblyandLangJoin.component as [Component] [red]into [tblTemp][/red] FROM tblCDAssemblyandLangJoin;"


DoCmd.SetWarnings false 'no dialogs for this please
DoCmd.RunSQL sqlTable
DoCnmd.SetWarnings True

...
' rest of code
...

End Sub

The results four you are the same, execept that there is no need to delete the table. as the code above
Amiel
amielzz@netscape.net

 

[CPUburn]

hi,

instead of first creating the table why not let the query itself make the table: just change "INSERT INTO", make it SELECT parno, projname, ... INTO new_tablename FROM recordsource name.

afterwards just use

str_sql ="DROP TABLE new_tablename;"
currentdb.execute str_sql

the kid

 

[Telsa]

Hi "the kid"!

I tried that but I need the language field to be empty. It is going to contain a collection of data created by the loops. Then I can have it written to my Word template.

Mary

 

[Francescina]

Let me just turn your attention to the fact that temporary tables will blow up your database file.
In general it is better to create a CSV file instead of a temporary table. Since it stays outside the MDB/MDE structure it does not blow up your ACCESS file. The performance is equivalent to a table (provided that the file is not exceeding a certain size).

 

[Amiel]

language field is no problem, try ....


sqlTable = " SELECT DISTINCT tblCDAssemblyandLangJoin.partno as [Part Number], tblCDAssemblyandLangJoin.base as [Base], " _
& "tblCDAssemblyandLangJoin.projectname as [Project Name], tblCDAssemblyandLangJoin.cdassembly, " _
& "tblCDAssemblyandLangJoin.component as [Component],
& " "" as [Lang], [red]' notice the empty quotes for [lang][/red]
into [tblTemp] FROM tblCDAssemblyandLangJoin;"

without the formatting it looks like this
...tblCDAssemblyandLangJoin.component as [Component], "" as [Lang], ...

And, sorry I didn't get back sooner.

Amiel
amielzz@netscape.net

 

[Telsa]

Okay, how can you tell if the recordset is being built? I keep getting an error of "...too few parameters, expected 5..." (the exact amount of fields in the rs recordset!).

Here is my revised code:


Sub LanguageRecordset()
Dim strLang As String
Dim rs As Recordset
Dim rs2 As Recordset
Dim rsTemp As Recordset
Dim sqlTable As String
Dim sqlTable2 As String

'Dump the old table with this function
DoesTableExist

' Create New Table


' Update the table with distinct data but no languages (yet)
sqlTable = "SELECT DISTINCT tblCDAssemblyandLangJoin.partno as [Part Number], " _
& "tblCDAssemblyandLangJoin.base as [Base], " _
& "tblCDAssemblyandLangJoin.projectname as [Project Name], " _
& "tblCDAssemblyandLangJoin.cdassembly as [CD Assembly], " _
& "tblCDAssemblyandLangJoin.component as [Component]," _
& Chr(34) & Chr(34) & " as [Lang] into [tblTemp] FROM tblCDAssemblyandLangJoin;"

' Run that SQL!
DoCmd.SetWarnings False
DoCmd.RunSQL sqlTable
DoCmd.SetWarnings True

' Create recordset to use to update tmptable
Set rs = CurrentDb.OpenRecordset("SELECT [part Number], [base], [project name], [cd assembly], [component] FROM tblTemp;&quot

' SQL for second recordset to loop languages
sqlTable2 = "SELECT language FROM tblCDAssemblyandLangJoin " _
& "WHERE (partno = rs![part number]) AND (projectname = rs![project Name]) AND (base = rs![base]) AND " _
& "(cdassembly = rs![cd assembly]) AND (component = rs![component]);"

rs.MoveFirst
Do While Not rs.EOF
' Create the second recordset which will have only languages go with first record of rs
Set rs2 = CurrentDb.OpenRecordset(sqlTable2)

Debug.Print "If this doesn't show, error happens just before this line."

rs2.MoveFirst

Do While Not rs2.EOF
strLang = strLang & rs2!lang & ", "
Loop
strLang = Left(strLang, Len(strLang) - 2)
rs.Edit
rs!lang = strLang
rs.Update
Loop


End Sub



Driving me nuts!

Mary s-)

 

[Telsa]

Never mind... found out I need to break apart the references to the recordsets (they're objects you know! *G*) . Slowly but surely I'm getting this done!

Thanks to all!

Mary

 

[Aivars]

Hi!

"Where ..." clause in your SQL text is wrong. You may write it like following text;

' SQL for second recordset to loop languages
sqlTable2 = "SELECT language FROM tblCDAssemblyandLangJoin " _
& "WHERE (partno = '" & rs![part number] & "') AND (projectname = '" & rs![project Name] & "') AND (base = '" & rs![base] & "') AND " _
& "(cdassembly = '" & rs![cd assembly] & "') AND (component = '" & rs![component]) & "';"

You can copy this expression and paste it into your codes. I think after them it may be work immaculately.

Good luck!
Aivars