Can I create records based on an external data link (my connections)?

[ayupchap]

HI all



Weird question and my skillset is limited but thought I'd try to explain this and see if theres a genius here who might be able to help/shed some light on this.

I work for a primary school and the lonely data/systems person here. I'd like ot think we try hard to utilize data as much as we can. Anyhow, we have an access database for students getting the bus when they leave for the day (90% of our 700 students get a school bus home). The database records any absences and any daily changes from the normal usage so the lists are up-to-date and every student is fully accounted for. It might seem like a small thing but If someone does or doesn't get on a bus and we are not sure where they are we have some serious issues. the database has made this so much easier as it was done on paper. It creates reports etc for student lists of those who should or shouldn't be riding on the bus or may have switched for a one-off reason.

Anyway all our attendance is done in an external system called powerschool, I can pull this data into MS Access using a ODBC link. What I want to know is am I able to pull this daily attendance info (Only the absents) into the bus database directly so it can automatically create a record as we are currently having a manual entry. This would be a huge time saver and be way more accurate than doing it manually.

I am not sure how to get it to create a record each day, I was hoping there would be a macro button I could make that would pull it in one go once I was ready.

Is this possible at all? If so there a way to explain this to a novice like myself? Any help very much appreciated.

[aytee111]

I don't understand. You say you are linked to the external system using ODBC, so you already have all the data at your disposal. Are you trying to append records to an "absentee" table within Access from those missing from the linked table(s)?

[ranman256]

you can make a macro.
in the macro would be an append query to add the data from the linked table to the internal table.

if you have linked the table, make a query,
bring in the Src table,
bring down the fields to add,
set the query to append,
set the target table
save as: say qaImportData

then the macro set the command:
openquery qaImportData

[ayupchap]

Hi aytee111

Sorry if I am not making myself clear, let me try and explain it a little better.

OK So I have this fully working bus database system, thats great. I worked out how to bring up data from this external system in an access database to view it (as a test managed to get this to work). This means that I could bring up a new table of this data in the bus database. What I am struggling to know is how do I go from showing this data in an access database to making it create records in another table in the database. So how do I get the pulled ODBC data which I can see in access to create the absent record in the current database table each day.

I have not linked the ODBC to anything at all yet and haven't ever done this before, this is very much a new discovery for me from playing around which I got excited about getting to work but have no idea how to integrate this into the database I made and get it to actually do anything if that makes sense.

It should be worth adding that the bus database and the attendance database both have the necessary student ID which can be used as the lookup/Primary key

[aytee111]

The linked table from the external system can be treated the same as any other table in Access, it has become just another table. All you need is a query/form to display the data you want to see. If this linked table is permanent, i.e. always there, then if you want to see students who weren't there on any given day, create a query which reads this table, takes a date as input, and displays the data. You can make it into a nice form with a FromDate and ToDate as fields on the form which your query will use as criteria.

[ayupchap]

Wow - Ok thanks aytee111 and ranman256. I am going to see if I can do this. Let me get to work on this now and I will get back to you all. Thanks so much for your help so far. If I get this to work I will be so happy.