Appending Multilpe Tables into One Master Table

[robburton]

Hi, I've been looking for a while for a means to achieve this but have so far drawn a blank so any help would be much appreciated.

I have 1 Access 2K3 database with circa 300 tables in, each of these tables have the same structure, but vary in thier length, and in some cases are just field headers. ( It was easier to import all data, as couldn't work out how to check prior to the import).

What I need to be able to do is to import all tables into 1 master table, however would like to avoid the need to append each table manually if possible.

The import requirement will pull the same columns from all source tables, and will also add the first 5 characters of the table name into the first field of the master table ( this first field does not exist within the source tables).

The code I have got so far is as follows:

Function data_upload_new()
 
Dim v_date, v_periodweek, x
 
x = 1
 
Do While x > 0
 
v_date = DLookup("[fld_name]", "tbl_table_names", "[fld_key] = x")
 
v_periodweek = Left(v_date, 5)
 
v_sql = "INSERT INTO tbl_master_data ( [v_periodweek], [customer id], [customer name], [mc pallets advised], [mc cages advised], [mc totes advised], [mc sets advised], [elc pallets advised], [mc cube in], [elc cube in], [arrival time], [departure time], [dwell time])" & _
"SELECT " & v_periodweek & " as v_periodweek, [" & v_date & "].[customer id], [" & v_date & "].[customer name], [" & v_date & "].[mc pallets advised], [" & v_date & "].[mc cages advised], [" & v_date & "].[mc totes advised], [" & v_date & "].[mc sets advised], [" & v_date & "].[elc pallets advised], [" & v_date & "].[mc cube in], [" & v_date & "].[elc cube in], [" & v_date & "].[arrival time], [" & v_date & "].[departure time], [" & v_date & "].[dwell time]" & _
"FROM [" & v_date & "];"
 
DoCmd.RunSQL v_sql
x = x + 1
Loop
End Function

I have created a table with all the table names in it as a point of reference, but I can't seem to get this code to work. Any help would be much appreciated.

Rob

 

[robburton]

Thanks Uncle Gizmo,

The message is as follows:

Runtime error 2001
You cancelled the previous operation.

This occurs on the following line:

v_date = DLookup("[fld_name]", "tbl_table_names", "[fld_key] = x")

Rob

 

[robburton]

I've checked it, the code you revised was correct, I copied that straight into access.

 

[robburton]

Yes, it's an autonumber field put against each of the tables names in the refrence table. An example would be as follows:

fld_key = 1
fld_name = P01W1

The fld_name field refers to a table.

 

[robburton]

I've had a bit more of a look at the dlookup and have now managed to get it working, as a result the message box programed in by Uncle Gizmo has bougght back the following, hopefully someone can see where i am going wrong!

---------------------------
Microsoft Office Access
---------------------------
>>> INSERT INTO tbl_master_data ( [v_periodweek], [customer id], [customer name], [mc pallets advised], [mc cages advised], [mc totes advised], [mc sets advised], [elc pallets advised], [mc cube in], [elc cube in], [arrival time], [departure time], [dwell time])SELECT P01W1 as v_periodweek, [P01W1 - Sun].[customer id], [P01W1 - Sun].[customer name], [P01W1 - Sun].[mc pallets advised], [P01W1 - Sun].[mc cages advised], [P01W1 - Sun].[mc totes advised], [P01W1 - Sun].[mc sets advised], [P01W1 - Sun].[elc pallets advised], [P01W1 - Sun].[mc cube in], [P01W1 - Sun].[elc cube in], [P01W1 - Sun].[arrival time], [P01W1 - Sun].[departure time], [P01W1 - Sun].[dwell time]FROM [P01W1 - Sun];
---------------------------
OK
---------------------------
Rob

 

[robburton]

It works!!!

Thankyou so much for your help.

Rob

 

[robburton]

My apologies, got carried away with the triumph!!

The dlookup solution was as follows:

v_date = DLookup("[fld_name]", "tbl_table_names", "[fld_key] = " & x)

I just needed to take the variable I was validating fld_key against outside of the quote marks, if memory serves me correctly, I believe this is because the key field is a numeric, had it been an alpha or some other sort of character it would need to be within the quote marks.

Thanks again.