Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to set PrimaryKey for a Linked Table from VB

Status
Not open for further replies.

inuman

Programmer
Feb 23, 2006
20
RO
Hi,

I've searched for 3 days on the internet for an answer to this problem.
I have a access frontend with a linked table to a mysqltable.
I have to create the linked table through vb code.
But the linked table does not have a primary key, so I can't update it, right?
How to set a primary key for the linked table through code?
If I use the wizard to create a linked table, it asks me which fields should be the primary keys, but it doesn't help me, because I have to do it through vb...

Please give me a hint people...
 
AFAIK, you can't modify a linked-table structure, unless you make your changes at the data source where the table is local. So go to mySQL, add a PK and link the table.

If it has to be done with VBA, get a proper connection ( and execute an ALTER TABLE statement against that connection. Then relink your table.
 
Thanks for the advice, it worked.
Anyway, I was looking for a way to link a table without alter it's structure on the server.
I want to update a local table with the records from a linked table, without modifying the linked table.
But when I try to run an UPDATE RIGHT JOIN query, it says that "Operation must use an updatable query".

UPDATE stoc RIGHT JOIN tbl ON (stoc.cod = tbl.cod) AND (stoc.codg=tbl.codg) SET stoc.cod=tbl.cod,stoc.codg=tbl.codg"

stoc = local table
tbl = linked table
 
In fact, looking at the JOIN and SET clauses I think you want simply an append query:
INSERT INTO stoc (cod, codg)
SELECT tbl.cod, tbl.codg
FROM tbl LEFT JOIN stoc ON tbl.cod = stoc.cod AND tbl.codg = stoc.codg
WHERE stoc.cod Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
tbl changes it's contents, so I want to run the update everytime I the form is loaded without deleting the current records in table stoc.
 
Actually, I guess PHV is right. My mistake, I didn't read the Set part.

Inner Join does absolutely nothing, as the joins are made on the same field combinations - so no update.
Right Join makes the query non-updatable, because you can't set values in non-existing records.

If tbl changes its contents, you can't find the matching records in stoc anymore.

For this purpose you need a field that stays the same in both tables.

Have you tried PHV's suggestion?

[pipe]
Daniel Vlas
Systems Consultant

 
Thanks a lot PHV, it works great.
What is the role of "WHERE stoc.cod Is Null" ?
 
spoke too soon...
now I have the correct result but I can't make an updateable query with stoc and tbl...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top