How to set PrimaryKey for a Linked Table from VB

[inuman]

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...

 

[JerryKlmns]

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 (

http://www.connectionstrings.com/?carrier=mysql)

and execute an ALTER TABLE statement against that connection. Then relink your table.

 

[inuman]

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

 

[JerryKlmns]

Maybe you should take a look here: Harnessing the Power of Updatable Queries

 

[danvlas]

Try Inner Join instead of Right Join

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[PHV]

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

 

[inuman]

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.

 

[danvlas]

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?


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[inuman]

Thanks a lot PHV, it works great.
What is the role of "WHERE stoc.cod Is Null" ?

 

[PHV]

What is the role of "WHERE stoc.cod Is Null" ?
(cod,codg) is in tbl but not in stoc.
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[inuman]

spoke too soon...
now I have the correct result but I can't make an updateable query with stoc and tbl...