Create table from a linked table?

[wrightyrx7]

Hi all,

New to Access so please be gentle haha

I have a Linked table in my Database. This table is live data from the tables that store the data for our payroll software. It is connected through ODBC and is protected so i cannot add my data to the data in the back end.

What i need to do is create another table from this, not a query because i cannot add data to a query.

The data in the linked table, is stuff like:-
-Employee ID
-Forename
-Surname

I want a table that keep up to data with the linked table (any new employees that show in the linked table get added to my NEW table)

The purpose of this is because im making a Telephone call log. And i want to be able to pull the data up from the NEW table (Forename, Surname etc) and add notes to that person and what the call related to.

I cannot do this if i create a Query from the linked table.

Any suggestions would be great.

Thanks in advance Chris

 

[TerryHogarth21]

What you could do is have a make table run every time you open up the Database.

 SELECT * INTO Table2
FROM Table1;

The above would be the make table, then just follow the below and create a macro to open up the query and rename the Macro to AutoExec.
Create a macro that runs when you open a database - Access

I'm not sure if that is what you want - there are other ways of doing it depending on how you want to handle the data. Say you have table 2 already and all you want is to append new records that don't exist (you would create an append query and look for Employee ID's that aren't in existence in table 2 compared to table 1 Employee ID's)

 

[wrightyrx7]

Many thanks for your reply Terry,

SOrry if this souds dumb but where would i put the SQL code? I only know where to put it in a query. Not in a table.

Regards
Chris

 

[TerryHogarth21]

It should be put in the SQL View of a query. (There should be 3 views if you are on Access 2007 -2013 I believe, Design, SQL, Datasheet) You can create a new query and plug the SQL in there.

Also now that I think about it from reading your initial post, it looks like you do not want to have a make table query but append query.

So you have TableA(linked from another source) TableB will have TableA's field + other fields (Your notes and comments etc).

INSERT INTO TableB
SELECT TableA.*
FROM Table1
WHERE (TableA.Employee_ID) NOT IN (SELECT Employee_ID FROM TableB);

This way you only append those records from Table A to Table B where the Employee ID don't exist in Table B but do exist in Table A. Afterwards you could go and update the comment field in Table B since Table A has no comment field as it's a linked table.

Am I understanding you correctly?

 

[wrightyrx7]

Hi Terry,

Yes as you have explained it above is exactly how I want it to work. Except it will auto update when the SQL View is updated.

I will go give it a try, wish me luck haha