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

 

[Cotty42]

Hi Chris

If you are running your log through a form then you could run a Data Append Query as part of the forms 'On Load' event. That way each time you load the form it would automatically update your local table.

Link the local table to the ODBC table through Employee ID (I assume that is the primary key). Set your join property to All records from ODBC table and only those matched from Local table and set a criteria for the local table Employee Id to Null, this way only records not in your current local table will be added (although first time you run it it will add all records).

Hope this helps

Dave

 

[Privateer]

Chris
First things first, you can add data through a query. You need to create a table with all the telephone log fields. Then add an EmployeeID field to it with type long and indexed dupes OK. Open a query and add both tables. Draw a line from the EmployeeID field in the Employee table to the EmployeeID field in the Log table. Now double click on the name fields in the employee table and they should show up as a field in the query; do not select the EmployeeID field from the linked table. Then double click on the EmployeeID field in the log table and the fields you need there. To test this, pop the query and enter an EmployeeID number in that field and the name should show up. Then enter the log information. To finish this off, this query is the recordsource for a form, put all the other fields on the form, with the EmployeeID field as a combo box. The source for the combo box is EmployeeID and Surname & ", " & Formname as Flip, so now, you look someone up by their last name, select a person, the EmployeeID gets entered into the log table, and you fill in the log fields. Kind of a long answer, but this is standard operating procedure. Good luck.
Privateer

 

[wrightyrx7]

Thank you both so much for the information you have provided.

I will go give these a go and will come back to you with some feedback.

Regards
Chris

 

[wrightyrx7]

Hi Guys,

Cotty,
Im not sure if i didnt follow your instructions right.. but the Employee ID's ended up missing after the Append Query had run.


Privateer,
I followed you instructions and it works. When I create a FORM from the QUERY and put the employeeID in, it pulls up the employee details.

Because the FORM is created from the QUERY rather than the LOG table, once I have added the notes to the form. How to i direct it to save in the Table log?


Thanks again for your help.

Regards
Chris

 

[Privateer]

Chris
It kind of does that automatically. If you move from one field1 to field2 the information in field1 gets written to the table. When you close the form, all the fields get "saved"; it's kind of what queries do. You can watch this. Open the log table and the form and edit the form. You will see the fields get updated as you move around. If you use a command button to close the form, you could put me.refresh in there but it's not necessary.

One other thing that your question brought to mind. You need to determine whether you are editing a current record or adding a new one. I don't know your level of programming, but if you leave the navigation buttons turned on, you can move to other records, by clicking on the arrows, and the arrow with the yellow starburst next to it will open a new record.

Good luck

p.s. I just reread you question and realized there might be a bigger problem. Your query should be something like, SELECT tblEmployee.FirstName, tblEmployee.LastName, tblLog.EmployeeID, tblLog.Notes From tblEmployee INNER JOIN tblLog.Employee ON tblEmployee.EmployeeID = tblLog.EmployeeID; so you have both tables under the form. If that is the case, my initial comments will apply.

 

[wrightyrx7]

Hi Privateer,

Sorry about the delay I have been on holiday

Back to trying to figure out Access. I dont have much knowledge of programming.

Only VBA in Exel and a bit of application building in vb.net

I think I get how it works, I just need to make sure the Employee ID, Forename and Surname etc are added to the call log (i think) because when a new call is being logged and I put in the employee ID in, I want a subform (of the call log) to filter any calls that previous employee ID has made. And im guessing to do this the employee ID will have to be on the "call log"

I might be wrong, and there might be and easier way.

Any suggestions are great.

Im just finding it difficult to figure out queries and how they work.

Also im trying to amend a template I downloaded from the Microsoft Office website. I dont know if it will be easier starting fresh and building this from scratch.

 

[gemma-the-husky]

given a table called ODBCPayroll, you can create a new table called Payroll

run a maketable query, which will take all the records from ODBCPayroll, and inserts them into Payroll. job done. The wizard will guide you. Your database should now use the local table. The great thing about this, is that your users cannot inadvertently modify the ODBC table data.

now the issues come when you want to update the data - so what you then need are queries that append new records into the local table, and queries that update existing records in the local table. (I think there is a single query version that will do both at once)

you can then add extra fields to the local table which are not in the ODBC table.

you may also need some auditing facility, so when details change, you can identify what the changes are.

 

[vbaInet]

With Privateer's idea you have one thing to consider, i.e. archived/deleted employees. What happens on your side when an employee is archived and how are they archived on the main db?

 

[wrightyrx7]

No employees are ever archived or deleted. Even if they leave they remain on the main/linked table.

 

[vbaInet]

Ok, I think gemma-the-husky and Cotty42 has covered pretty much the approach I had in mind.

You'll run the update query before you run the append query. You'll execute them in the Open event (not the Load event) of the form and have DoEvents there as well.

 

[wrightyrx7]

given a table called ODBCPayroll, you can create a new table called Payroll

run a maketable query, which will take all the records from ODBCPayroll, and inserts them into Payroll. job done. The wizard will guide you. Your database should now use the local table. The great thing about this, is that your users cannot inadvertently modify the ODBC table data.

now the issues come when you want to update the data - so what you then need are queries that append new records into the local table, and queries that update existing records in the local table. (I think there is a single query version that will do both at once)

you can then add extra fields to the local table which are not in the ODBC table.

you may also need some auditing facility, so when details change, you can identify what the changes are.

Oh I didnt see this post from gemma-the-husky. Thank you for your reply.

I have been playing with it all morning (the database ) . I think I have managed to get my head around relationships and queries and how they work (kind of).

I have two tables:-

• Employees (ODBC) - List of employees
• Call Log - Employee ID field, then all other fields call related.

I then have a query:-

• Call log extended

This looks at the Employee ID from the Call Log table and pulls all the employee details through to the query.

I then created a form from the query 'Call log extended', and all seems to be working as I want.

So pretty much the way Privateer explained. I'm Slowly but surely figuring access out, and starting to wonder why I ever used Excel to log data.. Access is much more powerful.

Thanks again for all your suggestions.

Sure you will see more threads from me when I next get stuck :banghead:

Regards
Chris

 

[vbaInet]

With Privateer's suggestion, I would have expected the record source to be read-only, but if the ODBC linked table is read/write then Privateer's suggestion is the way forward.

 

[wrightyrx7]

The odbc table is read only. But it's only used when I open an existing call log or create a new one. It just looks up the employee Id each time. So the data from the odbc table only ever shows in the form. The only new data that is saved is the call details and employee number to my other table.

If I look at the call log table without the form I do not see the data from the odbc table. But it will never be viewed like that anyway.

Chris

 

[vbaInet]

How do you know which employee your adding a call log for by just the Employee ID? In order to do that you will need to join the ID in your local table to the ID in the linked table which in turn will make your record source read-only altogether. Not unless you're synchronising two forms/subforms, one which is your local table and other being the linked table.

 

[Privateer]

Chris, I reread your original post and now I realize your linked table is to a payroll program rather than an access back end. Sorry.

Make a log table and a caller table in your access database with the following fields.

LogID, EmployeeID, CallerID, CallBegan, CallEnded, Notes.

CallerID, FirstName, LastName, Telephone

Once these two tables are created, open a blank query and add these two along with the Employee table. You should get lines between the tblEmployee.EmployeeID (ODBC table) and tblLog.EmployeeID and another line from tblLog.CallerID and tblCaller.CallerID. The problem is this line defaults to one to one, and there are no entries in the log yet, so the query looks empty. You want to edit these lines. Click the line once so it goes bold. Then click the secondary button, (right) and select "Join Properties". Then click button two, all records from employees and only those from Log that match. You should get an arrow pointing to the log table. If the arrow is going the other way, delete the line and drag the employee ID field from the employee table to the log table. The way the arrow is pointing is important. Now put the names from the employee table in the query and the log fields in, and pop the query. (datasheet view)

Lots of posts. Let us know if you are making any progress on the tables and the query I detailed above. If you get these things sorted out properly, the form will be easy.
Privateer

 

[wrightyrx7]

Morning (here in the UK) - The time difference means by the time you see my posts I have finished work

Privateer, I'm confused the first way you suggested first is working the way I wanted? Is this way going to cause me problems in the future.

I have tested what you suggested this morning but I have a few questions:-

• How come the tblLog needs to have a CallID (AutoNumber), EmployeeID (everybody has a unique employeeID) and CallerID. What is the CallerID for? Should I just use the employeeID as this is unique number for each employee in the ODBC table?
• Which table or Query should i create the Form from?

Thanks again for taking your time to help me.

Chris

 

[vbaInet]

Privateer, I'm confused the first way you suggested first is working the way I wanted? Is this way going to cause me problems in the future.

Perhaps upload a mock up db of your setup. Only include the related linked table, the local table, queries and forms. Use a text/Excel file to simulate the linked table.

 

[wrightyrx7]

Ok I have attached my database. And replaced all data with dummy data.

Its what I have so far, just think of the "Employees" table as my ODBC (read-only) table. This table updates from our payroll system. When a new starter is put onto the payroll system. They will show up on the Employees table when refreshed.

(Sorry about all the additional fields in the Employees table, im using a "SQL View" through ODBC that contains ALL basic details which is more than I need).

"Call details" is my form that I use to input an new call, and what i want to display any old calls.

Hopefully, I haven't made to much of a mess haha

Thanks

Chris

 

[vbaInet]

Its what I have so far, just think of the "Employees" table as my ODBC (read-only) table. This table updates from our payroll system. When a new starter is put onto the payroll system. They will show up on the Employees table when refreshed.

Precisely the setup I envisioned you created. If you join your local table to a read-only linked table your entire query becomes read-only. Yes the records will show up but you won't be able to change anything on your form.