Linking Table to 'Linked SQL table'

[sehmke]

Hello forum,

I just can't seem to figure this out... I hope I explain well enough for you to help me:

I have an access 2007 database which shows data from an SQL server (via ODBC) in a LINKED table (so not imported). I do not have write access to this so it only reads data.

I would like to make a separate table which links to this read-only table so I'm able to make a few editable fields for extra info. Then I could make a form which shows the read-only data from SQL server, but also some custom fields for me to add data to...

The SQL table is already full of data...

So the write-enabled table should produce records which are linked to the SQL table, each time a field is filled out on the form...

I can't seem to find how to link a table to an existing table (with relationships, childs, parents, indexes, field types, referential integrity, etc.)

 

[DCrake]

You will need a new table that has the same primary key as your SQL table then create a join between the two. Next create a new query that has fields from both the SQL and Access tables.

Then in your form you will be able to edit the Acces fields whilst still mainitaining a relationship between SQL and Acces.

 

[sehmke]

Thanks for your input. I went ahead as follows:

I went into design mode of the SQL-linked table (I get a warning that I can't change things but still it lets me see the fields and field types).

In the SQL-linked table there are two fields Primary Key, field type is TEXT. I guess this is how the SQL tables are set up. I can't change that.

So following your advice, I went to create a new table and made following fields:

1) ITEMID - as TEXT (this is exactly how it is in the SQL table) and made it Primary Key.
2) COMMENT - as MEMO (this is where I want to place my comments on the read-only records).

And saved it.

Then I made a small test Query which has a one-to-one link from ITEMID to ITEMID, the editable comment field and some of the read-only fields.

And saved it.

When I open the query I see no records. This is not what I want. I want to see all the records of the SQL table and a blank comments field which I can fill in, edit, ...

How to do this???

When I change the join to a one-to-many, where the existing SQL table is ALL records, and the extra table is 'where equal', I can see all the records, but I can't edit the comments field... The recordset is not updateable...

Getting close, but not there yet...

Greetings

 

[DCrake]

Ok, in your query to need to change the join properties to show all records in the SQLtable and the records in the support table.

Remember when you want to add comments to the memo field you need to also add the primary key in the ItemId field as well.

 

[sehmke]

There is the catch. I don't want to do that. I want Access to be smart enough (hahaha). I did the one/many change. Like I described, it shows all records. But the comments field is blank and can't be changed. I want the parent child behaviour to make the necessary joint data.... Don't know how this works, or if it is possible...

 

[DCrake]

So you have the parent record on the screen and you want to attach comments to it. So you enter text into your textbox and get it to add the record along with the PK if it does not exist in the child table.

 

[sehmke]

The recordset cant be updated. so I cant fill in the ITEMID in the 'editable' table, nor can I add comments...

This puzzles me!

 

[sehmke]

I found a solution...

I made a MAKE TABLE query which copies all the unique ITEMIDs from the SQL table in a new table. Now I can make a relation between the read-only table and the editable table. I just need to write a procedure to add new records from time to time, so the ITEMIDs are always an exact replication, because they serve as common field.

A good night thinking helped...

Thanks for your support.