|
| |
Come faccio a collegare, oltre le tabelle, anche le >relazioni tra tabelle,
tra un DB SQL Server ed un DB Access ??? Per le tabelle no problem, ma per le relazioni,
ogni volta le bevo ridefinire in access...non è una cosa simpatica
From: "Michele Amato" <mae@imolanet.com>
Newsgroups: it.comp.database.access
La seguente query ti restituisce in forma tabellare le relazioni tra le tabelle del tuo
db.
select o.name, o2.name, c.name, c2.name
from sysobjects as o, sysobjects as o2, sysreferences as r, syscolumns as c, syscolumns as
c2
where r.fkeyid = o.id and r.rkeyid = o2.id and c.id = o.id
and ( ((r.keycnt > 0 and r.fkey1 = c.colid) or (r.keycnt < 1 and r.fkey1 = 0))
and ((r.keycnt > 1 and r.fkey2 = c.colid) or (r.keycnt < 2 and r.fkey2 = 0))
and ((r.keycnt > 2 and r.fkey3 = c.colid) or (r.keycnt < 3 and r.fkey3 = 0))
and ((r.keycnt > 3 and r.fkey4 = c.colid) or (r.keycnt < 4 and r.fkey4 = 0))
and ((r.keycnt > 4 and r.fkey5 = c.colid) or (r.keycnt < 5 and r.fkey5 = 0))
and ((r.keycnt > 5 and r.fkey6 = c.colid) or (r.keycnt < 6 and r.fkey6 = 0))
and ((r.keycnt > 6 and r.fkey7 = c.colid) or (r.keycnt < 7 and r.fkey7 = 0))
and ((r.keycnt > 7 and r.fkey8 = c.colid) or (r.keycnt < 8 and r.fkey8 = 0))
and ((r.keycnt > 8 and r.fkey9 = c.colid) or (r.keycnt < 9 and r.fkey9 = 0))
and ((r.keycnt > 9 and r.fkey10 = c.colid) or (r.keycnt < 10 and r.fkey10 = 0))
and ((r.keycnt > 10 and r.fkey11 = c.colid) or (r.keycnt < 11 and r.fkey11 = 0))
and ((r.keycnt > 11 and r.fkey12 = c.colid) or (r.keycnt < 12 and r.fkey12 = 0))
and ((r.keycnt > 12 and r.fkey13 = c.colid) or (r.keycnt < 13 and r.fkey13 = 0))
and ((r.keycnt > 13 and r.fkey14 = c.colid) or (r.keycnt < 14 and r.fkey14 = 0))
and ((r.keycnt > 14 and r.fkey15 = c.colid) or (r.keycnt < 15 and r.fkey15 = 0))
and ((r.keycnt > 15 and r.fkey16 = c.colid) or (r.keycnt < 16 and r.fkey16 = 0)))
and c2.id = o2.id
and ( ((r.keycnt > 0 and r.rkey1 = c2.colid) or (r.keycnt < 1 and r.rkey1 = 0))
and ((r.keycnt > 1 and r.rkey2 = c2.colid) or (r.keycnt < 2 and r.rkey2 = 0))
and ((r.keycnt > 2 and r.rkey3 = c2.colid) or (r.keycnt < 3 and r.rkey3 = 0))
and ((r.keycnt > 3 and r.rkey4 = c2.colid) or (r.keycnt < 4 and r.rkey4 = 0))
and ((r.keycnt > 4 and r.rkey5 = c2.colid) or (r.keycnt < 5 and r.rkey5 = 0))
and ((r.keycnt > 5 and r.rkey6 = c2.colid) or (r.keycnt < 6 and r.rkey6 = 0))
and ((r.keycnt > 6 and r.rkey7 = c2.colid) or (r.keycnt < 7 and r.rkey7 = 0))
and ((r.keycnt > 7 and r.rkey8 = c2.colid) or (r.keycnt < 8 and r.rkey8 = 0))
and ((r.keycnt > 8 and r.rkey9 = c2.colid) or (r.keycnt < 9 and r.rkey9 = 0))
and ((r.keycnt > 9 and r.rkey10 = c2.colid) or (r.keycnt < 10 and r.rkey10 = 0))
and ((r.keycnt > 10 and r.rkey11 = c2.colid) or (r.keycnt < 11 and r.rkey11 = 0))
and ((r.keycnt > 11 and r.rkey12 = c2.colid) or (r.keycnt < 12 and r.rkey12 = 0))
and ((r.keycnt > 12 and r.rkey13 = c2.colid) or (r.keycnt < 13 and r.rkey13 = 0))
and ((r.keycnt > 13 and r.rkey14 = c2.colid) or (r.keycnt < 14 and r.rkey14 = 0))
and ((r.keycnt > 14 and r.rkey15 = c2.colid) or (r.keycnt < 15 and r.rkey15 = 0))
and ((r.keycnt > 15 and r.rkey16 = c2.colid) or (r.keycnt < 16 and r.rkey16 = 0)))
order by o.name, o2.name
Per creare in automatico le relazioni tra le tabelle access non ti resta che definire una
funzione che a partire dal resultset restituito da questa query manipoli la collezione
Relations in modo adeguato.
Le parole chiavi da ricercare nello help sono:
Relations, Relation, ForeignTable, ForeignName
Subject: Re: [ot (poco)] Sql Server 7
From: Guido dal Faro light.house@flashnet.it
Newsgroup: it.comp.appl.access
Date: mercoledì 9 agosto 2000 17.44
>Esiste un modo per far ripartire da 1 un campo contatore dopo che nella tabella sono
stati immessi dei dati?
>So che in Access bastava cancellare tutti i record dalla tabella e fare un
"Compatta Database" e mi chiedevo se esiste una funzione simile anche con
"SQL Server 7"
DBCC CHECKIDENT (tblName , reseed , 0);
|