Microsoft Access - need help!!!

ok - this is going to be hard to explain - and is easier to draw out (I will scan it soon - watch this space!)
Anyway, my problem:

I have 4 databases (named 1234, 1235, 1236, 1237). Inside them there are 4 tables (named a,b,c,d)
Table a has the same field names as the rest of the a - b is the same as b, etc. Only the data inside of them is different

I hope you understand that, and if not - tell me.

This is what I need to do - 1 is the most important, but if u can answer others, please do so.

1) Get it so the tables are merged into 4 big tables (eg, Maina, mainb, mainc, maind).
2) get it so to copies the table names into the big database, but the table names change to 1234_a, 1234_b, 1235_a, etc.

I am sure there must be a way - I just cannot find it.

There is probabl going to be more added - and all comments will be tried.
Thank you in advance now - and hope you can help me.

Reply 1

not1

Robinb

get it so the tables are merged into 4 big tables (eg, Maina, mainb, mainc, maind).

surely a quick copy/paste of the columns could do that?

Reply 2

Unregistered

get it scanned bud!!!... l8ter

Reply 3

loftx

I'm not sure there is an easy way to do this - I can think of a hard which involves programming and SQL though.

Reply 4

Robinb

I forgot to add that it needs to be user friendly - IE press a button and it does it - so it needs to be codeing and such. I can do quite a bit in Visual basic (like userforms) but it is the merging and renaming that has me puzzled :frown:

So, I think it is going to be in programing code and SQL (no idea about that thou :s-smilie:

)

Robinb

Reply 5

Clearing and Applications Advisor

Robinb

So, I think it is going to be in programing code and SQL (no idea about that thou :s-smilie:

)

Robinb

My Access is a little rusty but have you looked into creating a "MakeTable Query" and then exporting/importing the tables (or making it directly into a different database)

Reply 6

Robinb

Pencil Queen

My Access is a little rusty but have you looked into creating a "MakeTable Query" and then exporting/importing the tables (or making it directly into a different database)

no, I have not - will do when I get back to college. cheers.

Reply 7

Clearing and Applications Advisor

Robinb

no, I have not - will do when I get back to college. cheers.

Might also be worth asking these people http://www.mrexcel.com/board2/ very very useful site (although it's aimed towards excel not access there are a lot of posters who are experts in both)

Reply 8

Robinb

right then, back -
i have done the problems, and would like to thank all that helped me -
but now I have another problem that requirs coding for it

I need it so a message box appears, where you can enter a number (easy part) but then it renames the database and the tables within it - is it possible? There will be a defualt table names (a,b,c,d) if that is any help

Robinb

Reply 9

Clearing and Applications Advisor

Robinb

Technically it should be possible to create a variable to contain the input number but my VB is more than a little rusty and it's not obvious how to edit the VB of the Macros within Access. Might be worth creating a table to contain the new table names (complete with a calculated column which takes the number + old table name) and populating it with the SetValue command?
The Rename command within Access seems pretty useless for this but if you used "RunCommand:Rename" after copying the new table name then selecting each table then pasting that might work...it would take some fiddling to get right though.