Linked Table Help Needed

[Rakluv]

Need help from the community. I am very new to access but I am trying to build a DB for myself in order to help with my work at the gas company. Currently this is tracked on 10 excel flat files and I want to make this process more efficient. The company DB is oracle based and the system is not set up for me to limit views just to my information. Therefore I am trying to build an access DB to handle this.

What I am doing is downloading a copy of the DB info (which includes everyones area) to an excel spreadsheet and linking this to access as a linked table so I can use this information. Unfortunately, this means it makes a linked table without a key field.

However, according to information that I have read, I am unable to use this linked table in a form because it makes the form not updateable??? Is this due to the linked table and if so, is there a work around?

The reason I link this information is due to multiple fields that are updated hourly/daily. If I turn this into a table, then I will have to either manually update these fields constantly or create additional steps in order to recreate the wheel so to speak. I don't need to be able to update the linked information in the form, I just want to see it when doing the data entry for the purpose of specific dates, previous surveys, previous survey results, and for flagging due dates. Is this possible?

So far, the only information that I have been able to research on this is how to link information or how to import excel as a table but nothing that speaks of how to use a linked table in a form or what limitations a linked table has. If anyone has a website on this or similar articles that I can read up on, that would be greatly appreciated.

Thank You.
Signed (Access Overwhelmed - HELP PLEASE)

 

[CJ_London]

You can link to an excel table and display the linked table in a form. Not a problem.

What you can't do is change the data in the excel file from a form or even from the linked table.

I think what you are missing is a unique ID in the Excel data (a typical problem). The way to solve this is to create a query along the following lines:

Select *, Field1 & "|" & Field2 & "|" Field7 as SID FROM ExlLnkTble

Lets call this query ExlView and SID is short for Source ID (my acronym)

The fields are the fields in excel which combined create a unique value for the row. One thing to watch out for is case sensitivity which this does not allow for. It is a bit wasteful of space but I've used it for many years - sometimes up to 10 fields!

I include Pipes to ensure there is no leakage between one field value and the next which combined create a unique id for the row.

Next, in your table (we'll call it DTable) you will have an ID (naturally) but create a new text field called SID and index it (no duplicates)

So to see if there are any new records you now simply need a query that is along the following lines

Select * FROM ExlView LEFT JOIN DTable ON ExlView.SID=DTable.SID WHERE DTable.SID is Null

You can use this to insert new records

To see what has changed

Select * FROM ExlView INNER JOIN ExlView ON ExlView.SID=DTable.SID WHERE ExlView.Fld1<>ExlView.Fld1 OR ExlView.Fld2<>ExlView.Fld2 OR ExlView.Fld3<>ExlView.Fld3...etc

And you could use this to update existing records

... I'm sure you can work out how to identify records which no longer exist