Union multiple identical tables

[Sharon H.]

It's been 5 years since I've programmed in VB6. I'm now using Access 2003. We
have hardware that obtains data and downloads it into an Access database.
Each reading is one line in one table, resulting in multiple identical
tables. I can combine these tables by creating a unique Union query for each
set of readings, however, I doubt I can teach my co-workers how to do this.
What I need to do is to create a macro that maybe calls a function that will
look for tables created on the same day and either create one table with
multiple lines or append into one master table. I don't think that I've ever
used Visual Basic within MS Access so please take this into account when
advising.

Thanks much,
Sharon

 

[pietlinden]

It's been 5 years since I've programmed in VB6. I'm now using Access 2003.. We
have hardware that obtains data and downloads it into an Access database.
Each reading is one line in one table, resulting in multiple identical
tables. I can combine these tables by creating a unique Union query for each
set of readings, however, I doubt I can teach my co-workers how to do this.
What I need to do is to create a macro that maybe calls a function that will
look for tables created on the same day and either create one table with
multiple lines or append into one master table. I don't think that I've ever
used Visual Basic within MS Access so please take this into account when
advising.

Thanks much,
Sharon

If you have a limited number of standard structures, then coding this
is about as hard as falling down. You could create a generic SELECT
statement and then use REPLACE to change the fake table name to the
real one. Then you just slap a UNION ALL between the different
selects. and return the final result from your function. Create some
code that creates a querydef and then use the function result as the
SQL string

 

[Sharon H.]

Thank you for your help.

All the tables preprogrammed in the office begin with an F - that's a
default set up by the equipment that imports them. If a site is added in the
field ("ad hoc"), the equipment sets up the fields differently and the naming
convention is probably lost. Each import from the site reader creates a new
database, and ultimately all these databases need to be combined into one
table. I think the ad hoc tables would be a fairly rare event and could be
handled manually.

Would it be possible to create a macro that runs a user function that
combines the tables based on a UNION query (polling the tabledefs?)? Could
that macro (and the associated module) be copied by my coworkers into the new
databases so that one combined table could be exported out? (The reason I'm
favoring the UNION query is that it prevents duplicates, but I wouldn't sneer
at an APPEND query, either.)

If this would work and be easy for my coworkers to do, then I would need
help setting up a module and creating a macro...I've never done either of
these in Access before.

I realize that I am probably not seeing the easiest (best) solution at this
point.

Sharon

 

[Jim Burke in Novi]

I would create a master table that will hold all of the data in it. If
there's only one row per instance, it should never get all that big. The
master table would have all the fields that are already defined, plus a
DateCreated field with a field type of Date/Time. I don't know how you're
getting the tables into Access exactly, how tables are named, etc. so there
could be a simple way to do this, like running an Append query and then an
Update query that runs right after the Append query to add the value for the
DateCreated field. But here's a routine that should work.
Create a routine something like this (i'm coding this using ADO. If you're
using DAO, make appropriate changes. Also, I set it up to handle >1 record in
new table):

Public Sub AddNewTable

dim rstNew as ADODB.Recordset
dim rstMaster as ADODB.Recordset

Set rstNew = New ADODB.Recordset
Set rstMaster = New ADODB.Recordset
rstNew.Open "tblNamenew", CurrentProject.Connection,... 'options you want
rstMaster.Open "tblNamemaster", CurrentProject.Connection, ... 'options

rstNew.Movefirst
While not rstNew.EOF
rstMaster.AddNew
rstMaster!field1 = rstNew!Field1 'one line for each field in the table
rstMaster!DateCreated = Date()
rstMaster.Update
rstNew.MoveNext
Wend
rstNew.Close
rstMaster.Close
set rstNew = Nothing
set rstMaster = Nothing

End Sub

add error handling in case of errors also. Call this routine from wherever
you're creating your newly imported access table. Make it a function if
necessary. Hope this helps.

Jim B

 

[Sharon H.]

I may be in trouble. I vaguely remember using Access 5 years ago as a front
end for an Oracle database, and ADO and DAO are tickling neurons, but I have
forgotten almost every thing I once thought I knew.

Now for the really dumb question. I'm using Access 2003. No forms are used
in these databases. How do I determine/decide whether to use DAO or ADO?

Thanks much for the help.

 

[John W. Vinson]

Now for the really dumb question. I'm using Access 2003. No forms are used
in these databases. How do I determine/decide whether to use DAO or ADO?

If your database doesn't have any code (freestanding modules, or form or
report modules) then the question is moot - ADO and DAO apply only to VBA
code, not to tables directly.

By default 2003 assigns the DAO 3.6 object library. You can open the VBA
editor by typing Ctrl-G, and choose Tools... References from the menu. Two of
the (many!) possible references are

Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Object Library

Neither, either, or both may be checked.

 

[Sharon H.]

Right now, there is no code to anything, except for the examples I have been
given by the above kind people, which aren't yet working and are so then
"commentted out". I think they don't work is because I have to put code in
once at the beginning of the module to tell it how to connect to the
database, right? (You would think that since we are coding from within the
Access database, it would figure out that you want to connect to that
database, sheesh! That made sense when using Access as a front end to another
database, but...)

 

[John W. Vinson]

Right now, there is no code to anything, except for the examples I have been
given by the above kind people, which aren't yet working and are so then
"commentted out". I think they don't work is because I have to put code in
once at the beginning of the module to tell it how to connect to the
database, right? (You would think that since we are coding from within the
Access database, it would figure out that you want to connect to that
database, sheesh! That made sense when using Access as a front end to another
database, but...)

Ummm...

Sorry, this is making no sense. No, you do NOT need to write any code to
connect a database to its own tables.

Please explain what is in your database. Do you have local tables? Do you have
a Form or Forms? Are you trying to have a form open automatically when you
open the database? If so use Tools... Startup and select that form.

 

[Sharon H.]

Since these databases are created each time that data is exported from "the
equipment", there is nothing but several one-row local tables in each
database. There are no forms or anything else.

Regards,
Sharon H.