Table name Query

[EL-g]

Hey Guys.
I was wondering if any of you know how to make a Query that would get all the tables names.

Thanks in advance

 

[PeterF]

I normally do this in VBA but the following SQL should work to find the userdefined tables.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "msys*") AND ((MSysObjects.Type)=1));

 

[EL-g]

Awesome, the code works fine. I actually think ill make this as a module. Maybe it will be easier.
So far I have this code

Sub Average()
Dim Table As String
Dim mindate As String
Dim maxdate As String
Dim ssql As String

Table = apollo
mindate = 11 / 6 / 2003
maxdate = 11 / 7 / 2003

ssql = "SELECT Avg([Value]) AS " & Table & "_AVG FROM " & Table & "WHERE (((" & Table & ".Date) Between #" & mindate & "# And #" & maxdate & "#));"

End Sub

Ill make an array with the table name and maybe 2 strings with the dates.
The code doesnt seem to work tho. It doesnt have any error messages but it doesnt open the query. Do you know what is going on?

 

[EL-g]

ok...the static statement is solved

Sub Average()

Dim dbs As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim productName As String
Dim strSql As String

Set dbs = CurrentDb()

strSql = "SELECT Avg([Value]) AS Apollo_AVG FROM apollo WHERE (((apollo.Date) Between #11/6/2003# And #11/7/2003#));"
Set rs = dbs.OpenRecordset(strSql, dbOpenSnapshot)

With dbs
Set qdf = .CreateQueryDef("tmpProductInfo", strSql)
DoCmd.OpenQuery "tmpProductInfo"
.QueryDefs.Delete "tmpProductInfo"
End With
dbs.Close
qdf.Close
End Sub

Now ill work on the dynamic

Sub Average()
Dim dbs As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim productName As String
Dim strSql As String
Dim Table As String
Dim mindate As String
Dim maxdate As String

Set dbs = CurrentDb()

Table = apollo
mindate = 11 / 6 / 2003
maxdate = 11 / 7 / 2003

strSql = "SELECT Avg([Value]) AS " & Table & "_AVG FROM " & Table & " WHERE (((" & Table & ".Date) Between #" & mindate & "# And #" & maxdate & "#));"

Set rs = dbs.OpenRecordset(strSql, dbOpenSnapshot)

With dbs
Set qdf = .CreateQueryDef("tmpAvgInfodynamic", strSql)
DoCmd.OpenQuery "tmpAvgInfodynamic"
'.QueryDefs.Delete "tmpAvgInfodynamic"
End With
dbs.Close
qdf.Close
End Sub

I have no clue what is wrong here

strSql = "SELECT Avg([Value]) AS " & Table & "_AVG FROM " & Table & " WHERE (((" & Table & ".Date) Between #" & mindate & "# And #" & maxdate & "#));"

Can anyone help please?

Ive tried adding a msg box to see if the string was being passed and it seems that neither tha table nor the mindate, maxdate are

 

[gemma-the-husky]

your mindate maxdates wont work as they are, so this may be the problem

mindate as string
eg mindate = 11/6/2003 will divide 11 by 6 by 2003 and format it as a string (possibly!) - certainly not a date, anyway.

you want to dim mindate as date
then mindate = datevalue("11/06/2003")
then it might be ok in your sqlstrg

except a tricky catch is that some of these functions expect US dates, so you might find yourself using november 6th, instead of 11th June.

 

[EL-g]

Oh gemma thanks for the reply. YOu are always saving me here
About the US date i found it out earlier but thanks a lot for the tip because this is indeed tricky........

my code now look like this:

Sub Average_dynamic()

Dim dbs As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim productName As String
Dim strSql As String
Dim Table As String
Dim mindate As Date
Dim maxdate As Date

Set dbs = CurrentDb()

Table = apollo
mindate = DateValue("11/06/2003")
maxdate = DateValue("11/07/2003")

strSql = "SELECT Avg([Value]) AS " & Table & "_AVG FROM " & Table & "WHERE (((" & Table & ".Date) Between #" & mindate & "# And #" & maxdate & "#));"

Set rs = dbs.OpenRecordset(strSql, dbOpenSnapshot)

With dbs
Set qdf = .CreateQueryDef("tmpAvgInfodynamic", strSql)
DoCmd.OpenQuery "tmpAvgInfodynamic"
'.QueryDefs.Delete "tmpAvgInfodynamic"
End With
dbs.Close
qdf.Close
End Sub

Still getting an error.............i'll make a few tries with the msgbox to see what i can find out here
As far as msgbox the dates seem to be fine. I think there is something wrong in the table name.

EDIT: I did replace all the " & Table & " by apollo and i got a Run-time error '3075', Syntax error in date in query expression ' (((apollo.date) Between #11.06.2003# And #11.07.2003#))'.
Also i get an error if i replace the #" & mindate & "# by the actual date and leave as variable onle the " & Table & "

 

[gemma-the-husky]

try this - i'm not sure about avg([value]) though. is there a field called value?
You need a space before the where also.

strSql = "SELECT Avg([Value]) AS " & Table & "_AVG FROM " & Table & " WHERE " & Table & ".Date Between #" & mindate & "# And #" & maxdate & "#;"

 

[EL-g]

Yes there is a field called value. The strange is that the static statement works fine

strSql = "SELECT Avg([Value]) AS Apollo_AVG FROM apollo WHERE (((apollo.Date) Between #11/6/2003# And #11/7/2003#));"

And the dynamic is acting strange
Ill try your code.........

EDIT: With your code i got a Run-time error '3131' Syntax error in FROM clause

 

[gemma-the-husky]

put a msgbox after you construct the strsql, and see what it looks like. There must be something different between the sql strg, and your handcoded string, but i can't see what it is.

 

[EL-g]

ok...let me try it.....

EDIT: I get the maxdate and mindate fine, but table is blank..............very odd

 

[EL-g]

actually the dates are being passed as: 11.07.2003 and 11.06.2003
And the Table is being passed too

The error seems to be on the date. I changed the code to

strSql = "SELECT Avg([Value]) AS " & Table & "_AVG FROM " & Table & " WHERE (((" & Table & ".Date) Between #11/6/2003# And #11/7/2003#));"

And it works. The problem seems to be after the Between

Ok. I found it out. It seems to be a problem with regional settings. I changed the Dim as Date to Dim as String and then classfied the function like this: mindate = "09/29/2006" and it works now.

 

[EL-g]

Now i'm trying to make a loop with an array as table. I wanted to get all of them in the same query

Sub Average_dynamic_loop()

Dim dbs As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim strSql As String
Dim mindate As String
Dim maxdate As String
Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
Const Path As String = "G:\xTemp\txt.NET\"

Set dbs = CurrentDb()

FileName = Dir(Path & "*.*")
mindate = "09/29/2006"
maxdate = "10/01/2006"

Do While (Len(FileName) > 0)
nFiles = nFiles + 1
' If array is full...
If nFiles = UBound(Files) Then
' Make room for 10 more files
ReDim Preserve Files(1 To nFiles + 10)
End If
Files(nFiles) = FileName
'If no more files exist exit loop
If FileName = "" Then Exit Do
'Make FileName without .txt extension
FileName = Left(FileName, InStr(1, FileName, ".") - 1)
'Make The SQL query
strSql = "SELECT Avg([Value]) AS [" & FileName & "_AVG] FROM [" & FileName & "] WHERE ((([" & FileName & "].Date) Between #" & mindate & "# And #" & maxdate & "#));"

'a = MsgBox(FileName, 1, "a")

Set rs = dbs.OpenRecordset(strSql, dbOpenSnapshot)

With dbs
Set qdf = .CreateQueryDef(FileName & "AVG", strSql)
DoCmd.OpenQuery FileName & "AVG"
'Close the query window
DoCmd.Close acQuery, FileName & "AVG"
End With
'Get next file

FileName = Dir
Loop
dbs.Close
qdf.Close
End Sub

The data is being passed correctly. I checked with the MsgBox
So far i can loop and create a query for each individual table. I just couldt manage to create one for them all together. Can i add all the loops to one single query?. Or should i try to make an array with the results from the sql statemnt for each loop value and than make only one command to export?

 

[EL-g]

Also after putting them in the same table i wanted to traspose the values. I got this function

Function Transposer(strSource As String, strTarget As String)

Dim db As DAO.Database
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.Field
Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
Dim i As Integer, j As Integer

On Error GoTo Transposer_Err

Set db = CurrentDb()
Set rstSource = db.OpenRecordset(strSource)
rstSource.MoveLast

' Create a new table to hold the transposed data.
' Create a field for each record in the original table.
Set tdfNewDef = db.CreateTableDef(strTarget)
For i = 0 To rstSource.RecordCount
Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
tdfNewDef.Fields.Append fldNewField
Next i
db.TableDefs.Append tdfNewDef

' Open the new table and fill the first field with
' field names from the original table.
Set rstTarget = db.OpenRecordset(strTarget)
For i = 0 To rstSource.Fields.Count - 1
With rstTarget
.AddNew
.Fields(0) = rstSource.Fields(i).Name
.Update
End With
Next i

rstSource.MoveFirst
rstTarget.MoveFirst
' Fill each column of the new table
' with a record from the original table.
For j = 0 To rstSource.Fields.Count - 1
' Begin with the second field, because the first field
' already contains the field names.
For i = 1 To rstTarget.Fields.Count - 1
With rstTarget
.Edit
.Fields(i) = rstSource.Fields(j)
rstSource.MoveNext
.Update
End With

Next i
rstSource.MoveFirst
rstTarget.MoveNext
Next j

db.Close

Exit Function

Transposer_Err:

Select Case Err
Case 3010
MsgBox "The table " & strTarget & " already exists."
Case 3078
MsgBox "The table " & strSource & " doesn't exist."
Case Else
MsgBox CStr(Err) & " " & Err.Description
End Select

Exit Function

End Function

But i still dont know how to call it in my code
Actually i think this code is to traspose tables. Ill have to fix it for query. But 1st i need to group all the other queries in one. Ive been reasearching on that for a few weeks now and still have no clue what to do. Can anyone help?

EDIT: Does it exist a statement that follows? SELECT ALL Queries From All Queries?

 

[EL-g]

problem solved

thx everyone