Table Def in SQL ??

[CJBIRKIN]

Hi,

Can someone tell me if this is possible. I want to use a table in an unlinked database in an sql. It is possible i could link the table to the database but i would rather not.

I've tried the following but i get an error saying TblHub is not recognised.

Am i mistaken in thinking that the table def is a copy of the table data and all or is it just the table design.

Any clues greatfully received.

 Set DbRep = CurrentDb
Set RstRep = DbRep.OpenRecordset("SELECT TBL_SYNCPATH.* FROM TBL_SYNCPATH;")
Set DbHub = OpenDatabase(RstRep![SYNCHRON-PATH], , False, RstRep![SYNCHRON-PATH])
Set TblHub = DbHub.CreateTableDef("TBL_PATIENT_HUB", , "TBL_PATIENT", RstRep![SYNCHRON-PATH])
Set RstRep = Nothing

StrSQL = "SELECT TblHub.*" _
        & " FROM TblHub INNER JOIN TBL_PATIENT ON TblHub.HOSPITAL_NUMBER = TBL_PATIENT.HOSPITAL_NUMBER" _
        & " WHERE (((TblHub.NHS_NUMBER)<>[TBL_PATIENT].[NHS_NUMBER]))" _
        & " OR (((TblHub.DOB)<>[TBL_PATIENT].[DOB]))" _
        & " OR (((TblHub.PATIENT_CREATED_DATE)<>[TBL_PATIENT].[PATIENT_CREATED_DATE]))" _
        & " OR (((TblHub.PMH)<>[TBL_PATIENT].[PMH]))" _
        & " OR (((TblHub.PATIENT_SEX)<>[TBL_PATIENT].[PATIENT_SEX]))ORDER BY TblHub.HOSPITAL_NUMBER;"


Set RstRep = DbRep.OpenRecordset("SELECT TBL_PATIENT.* FROM TBL_PATIENT;")
Set RstHub = DbHub.OpenRecordset(StrSQL)

Cheers

Chris

 

[CJBIRKIN]

Hi,

Did this in the end

DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\chris's stuff\OUTREACH\SERVER\MOD-1.00-HUB.mdb", acTable, "TBL_PATIENT", "TBL_PATIENT_HUB", False


Then dropped it when i'd run my sql.

Chris

 

[CJBIRKIN]

Hi Pat,

Thanks for the advice, Basically i am creating a synchronisation tool for a database that i am working on. The inbuilt synchronisation wizard was just to complicated for the users to figure out the conflicts that were occuring.

Consequently i have 2 databases containing the same tables. The Hub and the Replica (can have many replicas)

It is just a case of not wanting the 20+tables duplicated in the replicas. I just think it's easier for me to see what's going on and i am less likely to accidently do something to the wrong table.

As for the In statement, it sounds like exactly what i want. Cheers.

Chris