Option Compare Database
Option Explicit

Dim dbs As Database


Sub DoItAll()

' This is the top-level procedure which calls the schema-definition
' procedure and the tuple-insertion procedure.

    CreateSchemaX
    InsertTuplesIntoDatabaseX

End Sub


Sub CreateSchemaX()

Set dbs = CurrentDb()

dbs.Execute "CREATE TABLE City( " _
   & " cityName VARCHAR(20) not null, " _
   & " country VARCHAR(20) not null, " _
   & " latitude FLOAT4 not null, " _
   & " longitude FLOAT4 not null, " _
   & " population FLOAT4 not null, " _
   & " Constraint pCityName primary key (cityName));" _

dbs.Execute "CREATE TABLE Country(  " _
   & " countryName VARCHAR(20) not null, " _
   & " continent VARCHAR(20) not null, " _
   & " totalPopulation INT not null, " _
   & " capital VARCHAR(20) not null,  " _
   & " currency_unit VARCHAR(20) not null,  " _
   & " Constraint pCountryName primary key (countryName), " _
   & " Constraint fCapital foreign key (capital) references City(cityName));"

dbs.Execute "CREATE TABLE Flight( " _
   & " fnumber VARCHAR(5) not null, " _
   & " departure VARCHAR(20) not null, " _
   & " arrival VARCHAR(20) not null, " _
   & " Constraint pFNumber primary key (fNumber)," _
   & " Constraint fDeparture foreign key (departure) references City (cityName)," _
   & " Constraint fArrival foreign key (arrival) references City(cityName));" _


End Sub



Sub InsertTuplesIntoDatabaseX()



dbs.Execute "INSERT INTO City VALUES " _
 & "('Berlin','Germany',52,13,3.2);" _

dbs.Execute "INSERT INTO City VALUES " _
 & "('Munich','Germany',49,11,1.3);" _

dbs.Execute "INSERT INTO City VALUES " _
 & "('Stockholm','Sweden',60,18,.7);" _

dbs.Execute "INSERT INTO City VALUES " _
 & "('Paris','France',48,2,13);" _

dbs.Execute "INSERT INTO City VALUES " _
 & "('NYC','USA',40,-74,7);" _

dbs.Execute "INSERT INTO City VALUES " _
 & "('DC','USA',39,-77,3);" _

dbs.Execute "INSERT INTO City VALUES " _
 & "('Havana','Cuba',23,-82,2.2);" _

dbs.Execute "INSERT INTO City VALUES " _
 & "('London','UK',51,0,12);" _




dbs.Execute "INSERT INTO Country VALUES " _
 & " ('USA', 'North America', 285, 'DC', 'Dollar');" _

dbs.Execute "INSERT INTO Country VALUES " _
 & "('Cuba', 'North America', 11, 'Havana', 'Cuban Peso');" _

dbs.Execute "INSERT INTO Country VALUES " _
 & "('Sweden','Europe',8.9,'Stockholm','Swedish Crown');" _

dbs.Execute "INSERT INTO Country VALUES " _
 & "('France','Europe',59,'Paris','Euro');" _

dbs.Execute "INSERT INTO Country VALUES " _
 & "('Germany','Europe',82,'Berlin','Euro');" _

dbs.Execute "INSERT INTO Country VALUES " _
 & "('UK','Europe',65,'London','Pound');" _



dbs.Execute "ALTER TABLE City " _
        & "Add Constraint fCountry foreign key (country)" _
        & "                         references Country (countryName);"





dbs.Execute "INSERT INTO Flight VALUES" _
 & " ('B100','Berlin','London');" _

dbs.Execute "INSERT INTO Flight VALUES " _
 & "('B101','Berlin','Havana');" _

dbs.Execute "INSERT INTO Flight VALUES " _
 & "('B102','Berlin','NYC');" _

dbs.Execute "INSERT INTO Flight VALUES " _
 & "('B103','Berlin','NYC');" _

dbs.Execute "INSERT INTO Flight VALUES " _
 & "('S100','Stockholm','Berlin');" _

dbs.Execute "INSERT INTO Flight VALUES " _
 & "('M100','Munich','DC');" _

dbs.Execute "INSERT INTO Flight VALUES " _
 & "('M101','Munich','NYC');" _

dbs.Execute "INSERT INTO Flight VALUES " _
 & "('P100','Paris','London');" _

dbs.Execute "INSERT INTO Flight VALUES " _
 & "('P101','Paris','NYC');" _

dbs.Execute "INSERT INTO Flight VALUES " _
 & "('P102','Paris','Berlin');" _

dbs.Execute "INSERT INTO Flight VALUES " _
 & "('L100','London','NYC');" _

End Sub