How many tables can one query???

[ribhead]

O.K.
First, I have about 72 hours of experience with Access so not much but I'm sorta getting the hang of doing simple things.

What I've already done... Created approx. 100 XL spread sheets each having the same column header and each containing similiar data. 15 of the XL files belong to one department the next 15 to another and so on...

So, I would like to link all the tables run a UNION query for each department then eventually run a "GIANT" UNION Query for the whole team (all the departments).

Question1: Is this possible.
Question2: Is this the easiest way to do it?
Question3: If Question1=False Then
Do
With Me.Head1
.Shoot
End With
Loop Until Me.Dead

Thanks,
rib


I know a little about nothing and even less about everything.

 

[Rolliee]

rib,

I would add all these to a single table and do the summary on that huge table. This could be coded quite easily in VB.
First link the xls tables in ACCESS and then oepn them one at a time and build you r new table. Email me if you wish an example. I will code it, debug it, send it to you and post the code.

rollie@bwsys.net

 

[ribhead]

hey rollie,

Currently I have 8 XL files linked to 8 different Access tables just to get my feet wet. I have only done a UNION Query with two tables and a SQL statement. If you say it's easy to do with VB I believe you I'm just not sure where to start. By the way if what I read is correct an Access db can have approximately 2 GB of space is this correct? I just don't want to run out of rows like in XL 65,536.

Thanks for the repost

rib

I know a little about nothing and even less about everything.

 

[mcdonalds]

hey rollie,
the limit of 2GB can be very easily avoided by moving all you tables to single databases or excel-sheets and linking them to your "master"-database. So each single database (external) can be 2GB and this should last for some time. The searching for external tables/databases can be done automatically and the linking to the "master"-db and the creation of the union-query as well. If you really have so many data i don't recomend to create a table with all data in you "master"-db.
For displaying the data you could use the union-query. but this could be very slow if you really have so much data.
so only if you really want to access to all data, use the union-query. better create (and maintain) a table (metadata) that stores the external table names and the data that is inside. So, if you want to access some special data - look in the metadata db and access only the single table.

examples for access 97

1. find special files:
Public Function findFiles() As boolean
Dim strFileName As String
Dim strFileNameLook As String

strFileName = Dir(strCurrentPath)
Do Until strFileName = ""
strFileNameLook = strCurrentPath & strFileName

<<2. Link table>>

strFileName = Dir(strCurrentPath)
Loop
End Function

2. Link Table

very easy with the DoCmd.TransferDatabase function


3. make union sql

Public Function makeUnionSQL() As Boolean
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdef As DAO.TableDef
Dim strSQL As String
Dim strUnion As String

Set db = CurrentDb
strSQL = &quot;&quot;
Application.RefreshDatabaseWindow
strUnion = &quot;&quot;
For Each tdef In db.TableDefs
If Left(tdef.Name, 9) = &quot;external_&quot;
strSQL = strSQL & strUnion
strSQL = strSQL & &quot; SELECT * FROM &quot; & tdef.Name & &quot;; &quot;
strUnion = &quot; Union&quot;
End If
Next

Set qdf = db.CreateQueryDef(strQueryName, strSQL)
db.QueryDefs.Refresh
Application.RefreshDatabaseWindow

Set db = Nothing
makeUnionSQL = True

End Function


if you change the function with the union-query, you can automaticaly add a field with the table name and path. so you always know where the single recordset is stored.

tell me if you have more questions.

 

[MichaelRed]

For &quot;Recordsets&quot;, Ms. A. (and many RBDMS)limit the &quot;field Count to 255 (or at least SOME number). In terms of a &quot;Query&quot;, the actual field count includes objects other than the fields in the visible result, such as each unique occurance of a field or index or order. While I have never actually attempted / tested it, I believe a Union query would include each field from each 'link' seperatly in that &quot;count&quot;, so 100 seperate spreadsheet links would almost certainly exceede the limit. Regardless of an actual error re too many fields, Union queries are somewhat less than optimal in performance, so attempting to include / list 100 linked spreadsheets would probabnly set off 'ye olde snooze alarm'.

While the thought of doing 100 append queries to build a temporary table for use in your project is only slightly less apealing, I would think that it would be at least doable.

On the other hand, I would appreciate knowing wheather the Union query approach would return an error or work, and if it did run to completion how long it took to runn (and how many (observable) fields you generated with it.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mcdonalds]

Hi,

i have tested to link 100 external Tables (in seperated Access databases) to a &quot;master&quot; database and creating a union-query with this 100 tables and it worked (in Access 97). No error message. The performance is quite poor and it should be used only in case you really need to access all (!) data in once.
But you can use this union-query to get the location (path and filename) of a specific recordset. Then you can access the full recordset by accessing the single table directly.

Regards, mcdonalds

 

[Rolliee]

rib,

If the fields are the same, you do not want a query many many fields wide, do you. What you want is to 'stack' the data. This code works. You perform the link just once and then run the form whenever you want the data concatenated.

rollie@bwsys.net

= = = = = = = = = =
Private Sub cmdSumAll_Click()
Dim rs As DAO.Recordset, ar(5) As String, i As Integer, r2 As DAO.Recordset, j As Integer

Set r2 = CurrentDb.OpenRecordset(&quot;SummAll&quot
If Not r2.BOF And r2.EOF Then
Do While Not rs.EOF
r2.Delete
r2.MoveNext
Loop
End If
For i = 1 To 4
ar(i) = &quot;Sheet&quot; & Mid(Str(i), 2)
Next i

Set r2 = CurrentDb.OpenRecordset(&quot;SummAll&quot
For i = 1 To 4
Set rs = CurrentDb.OpenRecordset(ar(i))
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
r2.AddNew
For j = 0 To 3
MsgBox Str(j + 1) & &quot; &quot; & r2.Fields(j + 1) & &quot; &quot; & Str(j) & &quot; &quot; & rs.Fields(j)
r2.Fields(j + 1) = rs.Fields(j)
Next j
r2.Update
rs.MoveNext
Loop

rs.Close
Next i
Me.Requery
MsgBox &quot; I am done &quot;
r2.Close
Set rs = Nothing
Set r2 = Nothing

End Sub
= = = = = = = = =

 

[lameid]

rib,

I think the best thing for you to do if at all possible is to store all the data in one table. You could even add a field to your table to show which department the record belongs to.

The one table model works best assuming you can get people to use Access for maintaining the data and that the departments are connected by a LAN that is sufficient to support the traffic.

If you are new to databases altogether, you ought to learn about Data normalization... The first three rules will serve you well as a beginner and the rest will likely give you a headache the first dozen times you look at them (I'm not sure the headache ever goes away completely). Head over to

http://www.datamodel.org/NormalizationRules.html

to read up on it.

If you are truly an Excel expert, you probably have normalized a little bit if you have used VSLOOKUP. And please remember anytime you have to use VSLOOKUP in Excel, you should have used Access.

One more tip... The Eval function can save you from floating point headaches when used cleverly in code.