On Error Resume Next
If TCrDB.State Then TCrDB.Close
TCrDB.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source=" & ServerName & ";Password=swtj2007"
If TDBName <> "TamannaDB" Then TCrDB.Execute "DROP DATABASE " & TDBName
On Error GoTo HELL:
TCrDB.Execute "Create Database " & TDBName & " on (name = " & TDBName & "_data, filename ='" & TDBPath & TDBName & "_data.mdf') log on (name =" & TDBName & "_log, filename ='" & TDBPath & TDBName & "_log.ldf')"
TCrDB.Execute "USE " & TDBName
TCrDB.Execute "Create Table COUNTRY_MASTER (CountryName Varchar(20) Primary key)"
TCrDB.Execute "Create Table STATE_MASTER (StateName Varchar(25) Primary key, CountryName Varchar(20))"
TCrDB.Execute "CREATE TABLE CATALOG_MASTER (CatalogName Varchar(35) Primary key)"
TCrDB.Execute "Create Table CITY_MASTER(CityName Varchar(35) Primary key,StateName Varchar(25),STDCode Numeric(6))"
TCrDB.Execute "Create Table BANK_MASTER (BankName Varchar(35) Primary key)"
TCrDB.Execute "Create Table VOUCHER_TYPE_MASTER (TypeName Varchar(25) Primary key)"
TCrDB.Execute "Create Table GENERAL_ITEM_MASTER (GeneralItemName Varchar(30) Primary key)"
TCrDB.Execute "Create Table GREY_ITEM_MASTER (ItemName Varchar(30) Primary Key,ItemRatePerPcs Money,ItemRatePerMts Money)"
TCrDB.Execute "Create Table ITEM_CATEGORIES_MASTER (ItemCategories Varchar(10) Primary Key)"
TCrDB.Execute "Create Table ITEM_QUALITY_MASTER (ItemQuality Varchar(10) Primary Key)"
TCrDB.Execute "Create Table FINISHED_ITEM_MASTER (ItemName Varchar(30) Primary Key,ItemQuality Varchar(10),ItemCategories Varchar(30),ItemRatePerPcs Money,ItemRatePerMts Money,ItemCut Money,ItemTopName Varchar(30),ItemTopMts Money,ItemMiddleName Varchar(30),ItemMiddleMts Money,ItemBottomName Varchar(30),ItemBottomMts Money,ItemOtherName Varchar(30),ItemOtherMts Money,ItemCutPcsMtr Money,ItemMtsPerKG Money,ItemGrp Varchar(30),ItemImagePath Varchar(255),FoldPerc Money,ItemImagePath2 Varchar(255),ItemImagePath3 Varchar(255),ItemImagePath4 Varchar(255),ItemImagePath5 Varchar(255),PurRatePerPcs Money,PurRatePerMts Money)"
TCrDB.Execute "Create Table SCREEN_ITEM_MASTER (ItemName Varchar(30) Primary Key,ItemRatePerPcs Money,ItemRatePerMts Money,ItemGrp Varchar(30),ItemImagePath Varchar(255),ItemOn Varchar(30),ItemCut Money,FabCost Money,JobCost Money,TotCost Money,CatalogName Varchar(35),Target Money,ItemImagePath2 Varchar(255),ItemImagePath3 Varchar(255),ItemImagePath4 Varchar(255),ItemImagePath5 Varchar(255),ScreenPcs Numeric(6),PurRatePerPcs Money, PurRatePerMts Money,ProfitPerc Money,Size Varchar(15))"
TCrDB.Execute "Create Table SUB_SCREEN_ITEM_MASTER (ItemName Varchar(30) Primary Key,ItemRatePerPcs Money,ItemRatePerMts Money,ItemGrp Varchar(30),ItemImagePath Varchar(255),ItemOn Varchar(30),Packing Varchar(30))"
TCrDB.Execute "Create Table RANGE_DESCRIPTION_MASTER (ItemRangeDesp Varchar(30),ExcessRate Money,ScreenName Varchar(30),PartyCode Varchar(7),JobRate Money)"
TCrDB.Execute "Create Table COMPANY_YEAR(CompYear Varchar(7) Primary key)"
TCrDB.Execute "Create Table ACCOUNT_TYPES(AccTypes Varchar(35) Primary key)"
TCrDB.Execute "Create Table COMPANY_DETAIL(CompCode Varchar(7) Primary Key, CompName Varchar(35), CompOpeningDate DateTime,CompClosingDate DateTime,CompYear Varchar(7),CompStatus Varchar(5),NextYearCode Varchar(7))"
TCrDB.Execute "Create Table AGENT_MASTER(AgentCode Varchar(7) Primary Key,AgentName Varchar(35), AgentAdd1 Varchar(40), AgentAdd2 Varchar(30),AgentAdd3 Varchar(30),AgentCityName Varchar(35),AgentCityPin Numeric(6),AgentPhone Varchar(35),AgentMobile Varchar(35),AgentFax Varchar(35),AgentEmail Varchar(50),AgentResident Varchar(35),AgentPANNo Varchar(35),AgentContPerson Varchar(35),AccType Varchar(35),BlackListed Varchar(1))"
TCrDB.Execute "Create Table GROUP_MASTER(GrpCode Varchar(7) Primary Key,GrpName Varchar(35), GrpAdd1 Varchar(40), GrpAdd2 Varchar(30),GrpAdd3 Varchar(30),GrpCityName Varchar(35),GrpCityPin Numeric(6),GrpPhone Varchar(35),GrpMobile Varchar(35),GrpFax Varchar(35),GrpEmail Varchar(50),GrpResident Varchar(35),GrpContPerson Varchar(35),AgentCode Varchar(7) Foreign Key References AGENT_MASTER(AgentCode),DisPerc Money,RDPerPcs Money,RDPerMts Money,Branch Varchar(1))"
TCrDB.Execute "Create Table ACCOUNT_MASTER (AccCode Varchar(7) Primary Key,AccName Varchar(50),AccType Varchar(35),AccAgentCode Varchar(7) Foreign Key References AGENT_MASTER(AgentCode),AccGroupCode Varchar(7) Foreign Key References GROUP_MASTER(GrpCode),AccAdd1 Varchar(40), AccAdd2 Varchar(30),AccAdd3 Varchar(30),AccCityName Varchar(35),AccCityPin Numeric(6),AccPhone Varchar(35),AccMobile Varchar(35),AccFax Varchar(35),AccEmail Varchar(50),AccResident Varchar(35),AccGSTNo Varchar(35),AccCSTNo Varchar(35),AccPANNo Varchar(35),AccVATNo Varchar(35),AccContPerson Varchar(35),AccRef Varchar(255),AccCrLimit Money,AccCrDays Numeric(4),AccTANNo Varchar(35),AccKSTNo Varchar(35),TDSYesNo Varchar(3),SpecialTDSYN Varchar(1),TDSAccount Varchar(35))"
TCrDB.Execute "Create Table LOGIN_DETAIL (UName Varchar(20) Primary Key,PWord Varchar(10),UserPermit Varchar(255))"
TCrDB.Execute "Create Table TRANSPORT_MASTER (TransName Varchar(35) Primary Key,TransPhone Varchar(35),TransEmail Varchar(50),TransAdd1 Varchar(30),TransAdd2 Varchar(30))"
TCrDB.Execute "Create Table TDS_TYPE_MASTER (TDSType Varchar(35) Primary Key,TDSCode Varchar(30),IncomeTax Money,Surcharge Money,EducationCess Money,AccTDSCode Varchar(7),NatOfPay Varchar(255))"
TCrDB.Execute "Create Table USER_PERMISSION(UName Varchar(20),USettingName Varchar(255),USettingValue Varchar(255))"
TCrDB.Execute "Create Table LABEL_DETAIL (StickerName Varchar(50),StickerType Varchar(50),Line1 Varchar(50),Line2 Varchar(50),Line3 Varchar(50),Line4 Varchar(50),Line5 Varchar(50),Line6 Varchar(50),LabSet1 Varchar(50),LabSet2 Varchar(50),LabSet3 Varchar(50),LabSet4 Varchar(50),LabSet5 Varchar(50),LabSet6 Varchar(50),StickerPrint Varchar(50))"
TCrDB.Execute "Create Table HOLYDAYS_LIST (HolyDate DateTime Primary Key,HolyName Varchar(35))"
TCrDB.Execute "Create Table HASTE_MASTER (Haste Varchar(25) Primary key,HasAdd1 Varchar(40), HasAdd2 Varchar(30),HasAdd3 Varchar(30),HasCityName Varchar(35),HasCityPin Numeric(6),HasPhone Varchar(35),HasMobile Varchar(35),HasFax Varchar(35),HasEmail Varchar(50),HasResident Varchar(35),HasGSTNo Varchar(35),HasCSTNo Varchar(35),HasContPerson Varchar(35),HasKSTNo Varchar(35),HasTransport Varchar(35))"
TCrDB.Execute "Create Table REMARK_MASTER (Remark Varchar(255) Primary key)"
TCrDB.Execute "CREATE TABLE REMARK_DETAIL (PartyCode Varchar(7),RemDate DateTime,Remark Varchar(700))"
TCrDB.Execute "Create Table REPORT_SETTING (RptName Varchar(255),FldName Varchar(30),CustSize Numeric(2),ColSrNo Numeric(2))"
TCrDB.Execute "Create Table UPDATE_DETAIL (SoftName Varchar(35),UpdDate DateTime,UpdTime DateTime,UpdNo Money)"
TCrDB.Execute "Create TABLE PRINTING_MASTER (MasterName Varchar(35),Phone Varchar(30),Mobile Varchar(30),EmailID Varchar(30),MillCode Varchar(7))"
TCrDB.Execute "Create TABLE JOB_CHALLAN_MASTER (ChallanName Varchar(35),ChallanSrNo Numeric(6))"
TCrDB.Execute "Create TABLE USER_WORK_DETAIL (UName Varchar(20),UPermit Varchar(255),CurDate DateTime,CurTime DateTime,BillNoSerialNo Varchar(255),BillSerialDate DateTime,EntryForm Varchar(255),ActionTaken Varchar(255),Company Varchar(255),CompYear Varchar(255),Party Varchar(255),BillNo Varchar(255))"
TCrDB.Execute "Create Table ITEM_ON_MASTER (ItemName Varchar(30) Primary Key,ShortName Varchar(7))"
TCrDB.Execute "CREATE TABLE SERIES_MASTER (SeriesName Varchar(30),ItemName Varchar(30),ScreenName Varchar(30),SubScreen Varchar(30),ItemRate Money,Pcs Numeric(6))"
TCrDB.Execute "Create Table MARKET_MASTER (MarketName Varchar(50) Primary key)"
TCrDB.Execute "Create Table THEME_SETTING (ThemeType Varchar(35),ThemeValue Varchar(255))"
TCrDB.Execute "ALTER TABLE ACCOUNT_TYPES ADD FinalType Varchar(25), FinalCrDr Varchar(25),FinalView Numeric(3),FinalApplicable Varchar(25)"
TCrDB.Execute "alter table ACCOUNT_MASTER Add JobType Varchar(35)"
TCrDB.Execute "ALTER TABLE ACCOUNT_MASTER Add DiscPerc Money,RDPerPcs Money,RDPerMts Money"
TCrDB.Execute "alter table ACCOUNT_MASTER Add InsPolicyNo Varchar(35)"
TCrDB.Execute "Alter table HASTE_MASTER Add TINNo Varchar(35),InsPolicyNo Varchar(35)"
TCrDB.Execute "Alter Table ACCOUNT_MASTER Add Dhara Money,DharaDays Numeric(6)"
TCrDB.Execute "Alter Table ACCOUNT_MASTER Add TransName Varchar(35)"
TCrDB.Execute "ALTER TABLE ACCOUNT_MASTER Add AccAdd21 Varchar(40), AccAdd22 Varchar(30),AccAdd23 Varchar(30),AccCityName2 Varchar(35),AccCityPin2 Numeric(6)"
TCrDB.Execute "ALTER TABLE ACCOUNT_MASTER Add BlackListed Varchar(1)"
TCrDB.Execute "ALTER TABLE ACCOUNT_MASTER Add BankDetail Varchar(255)"
TCrDB.Execute "Alter Table ACCOUNT_MASTER ADD ExcessRate Money"
TCrDB.Execute "Alter Table ACCOUNT_MASTER ADD SeriesName Varchar(30)"
TCrDB.Execute "Alter Table ACCOUNT_MASTER ADD CommPerc Money"
TCrDB.Execute "Alter Table ACCOUNT_MASTER ADD TDSLimit Money"
TCrDB.Execute "Alter Table ACCOUNT_MASTER ADD BankName Varchar(35),AccountNo Varchar(35),IFSCCode Varchar(50),Branch Varchar(50)"
TCrDB.Execute "Alter Table ACCOUNT_MASTER Add LessDays Numeric(6)"
TCrDB.Execute "Alter Table ACCOUNT_MASTER Add PackPerPcs Money"
TCrDB.Execute "Alter Table ACCOUNT_MASTER Add BankCommPerc Money"
TCrDB.Execute "Alter Table ACCOUNT_MASTER Add BankCommLessPerc Money"
TCrDB.Execute "Alter Table ACCOUNT_MASTER Add PackPerMts Money"
TCrDB.Execute "Alter Table ACCOUNT_MASTER Add PostageCharge Money"
If TCrDB.State Then TCrDB.Close
Exit Function
HELL:
MsgBox Err.Description
End Function
Public Function IMPORT_EXPORT_COMMON(ImportDB As String, ExportDB As String, ServerName As String)
On Error GoTo HELL:
If TCrDB.State Then TCrDB.Close
TCrDB.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source=" & ServerName & ";Password=swtj2007"
TCrDB.Execute "USE " & ImportDB
TCrDB.Execute "INSERT INTO COUNTRY_MASTER SELECT * FROM " & ExportDB & ".DBO.COUNTRY_MASTER"
TCrDB.Execute "INSERT INTO STATE_MASTER SELECT * FROM " & ExportDB & ".DBO.STATE_MASTER"
TCrDB.Execute "INSERT INTO CATALOG_MASTER SELECT * FROM " & ExportDB & ".DBO.CATALOG_MASTER"
TCrDB.Execute "INSERT INTO CITY_MASTER SELECT * FROM " & ExportDB & ".DBO.CITY_MASTER"
TCrDB.Execute "INSERT INTO BANK_MASTER SELECT * FROM " & ExportDB & ".DBO.BANK_MASTER"
TCrDB.Execute "INSERT INTO SERIES_MASTER SELECT * FROM " & ExportDB & ".DBO.SERIES_MASTER"
TCrDB.Execute "INSERT INTO MARKET_MASTER SELECT * FROM " & ExportDB & ".DBO.MARKET_MASTER"
TCrDB.Execute "INSERT INTO THEME_SETTING SELECT * FROM " & ExportDB & ".DBO.THEME_SETTING"
TCrDB.Execute "INSERT INTO VOUCHER_TYPE_MASTER SELECT * FROM " & ExportDB & ".DBO.VOUCHER_TYPE_MASTER"
TCrDB.Execute "INSERT INTO GENERAL_ITEM_MASTER SELECT * FROM " & ExportDB & ".DBO.GENERAL_ITEM_MASTER"
TCrDB.Execute "INSERT INTO GREY_ITEM_MASTER SELECT * FROM " & ExportDB & ".DBO.GREY_ITEM_MASTER"
TCrDB.Execute "INSERT INTO ITEM_CATEGORIES_MASTER SELECT * FROM " & ExportDB & ".DBO.ITEM_CATEGORIES_MASTER"
TCrDB.Execute "INSERT INTO ITEM_QUALITY_MASTER SELECT * FROM " & ExportDB & ".DBO.ITEM_QUALITY_MASTER"
TCrDB.Execute "INSERT INTO FINISHED_ITEM_MASTER SELECT * FROM " & ExportDB & ".DBO.FINISHED_ITEM_MASTER"
TCrDB.Execute "INSERT INTO SCREEN_ITEM_MASTER SELECT * FROM " & ExportDB & ".DBO.SCREEN_ITEM_MASTER"
TCrDB.Execute "INSERT INTO SUB_SCREEN_ITEM_MASTER SELECT * FROM " & ExportDB & ".DBO.SUB_SCREEN_ITEM_MASTER"
TCrDB.Execute "INSERT INTO RANGE_DESCRIPTION_MASTER SELECT * FROM " & ExportDB & ".DBO.RANGE_DESCRIPTION_MASTER"
TCrDB.Execute "INSERT INTO COMPANY_YEAR SELECT * FROM " & ExportDB & ".DBO.COMPANY_YEAR"
TCrDB.Execute "INSERT INTO ACCOUNT_TYPES SELECT * FROM " & ExportDB & ".DBO.ACCOUNT_TYPES"
TCrDB.Execute "INSERT INTO COMPANY_DETAIL SELECT * FROM " & ExportDB & ".DBO.COMPANY_DETAIL"
TCrDB.Execute "INSERT INTO AGENT_MASTER SELECT * FROM " & ExportDB & ".DBO.AGENT_MASTER"
TCrDB.Execute "INSERT INTO GROUP_MASTER SELECT * FROM " & ExportDB & ".DBO.GROUP_MASTER"
TCrDB.Execute "INSERT INTO ACCOUNT_MASTER SELECT * FROM " & ExportDB & ".DBO.ACCOUNT_MASTER"
TCrDB.Execute "INSERT INTO LOGIN_DETAIL SELECT * FROM " & ExportDB & ".DBO.LOGIN_DETAIL"
TCrDB.Execute "INSERT INTO TRANSPORT_MASTER SELECT * FROM " & ExportDB & ".DBO.TRANSPORT_MASTER"
TCrDB.Execute "INSERT INTO TDS_TYPE_MASTER SELECT * FROM " & ExportDB & ".DBO.TDS_TYPE_MASTER"
TCrDB.Execute "INSERT INTO USER_PERMISSION SELECT * FROM " & ExportDB & ".DBO.USER_PERMISSION"
TCrDB.Execute "INSERT INTO LABEL_DETAIL SELECT * FROM " & ExportDB & ".DBO.LABEL_DETAIL"
TCrDB.Execute "INSERT INTO HOLYDAYS_LIST SELECT * FROM " & ExportDB & ".DBO.HOLYDAYS_LIST"
TCrDB.Execute "INSERT INTO HASTE_MASTER SELECT * FROM " & ExportDB & ".DBO.HASTE_MASTER"
TCrDB.Execute "INSERT INTO REMARK_MASTER SELECT * FROM " & ExportDB & ".DBO.REMARK_MASTER"
TCrDB.Execute "INSERT INTO REMARK_DETAIL SELECT * FROM " & ExportDB & ".DBO.REMARK_DETAIL"
TCrDB.Execute "INSERT INTO REPORT_SETTING SELECT * FROM " & ExportDB & ".DBO.REPORT_SETTING"
TCrDB.Execute "INSERT INTO UPDATE_DETAIL SELECT * FROM " & ExportDB & ".DBO.UPDATE_DETAIL"
TCrDB.Execute "INSERT INTO PRINTING_MASTER SELECT * FROM " & ExportDB & ".DBO.PRINTING_MASTER"
TCrDB.Execute "INSERT INTO JOB_CHALLAN_MASTER SELECT * FROM " & ExportDB & ".DBO.JOB_CHALLAN_MASTER"
TCrDB.Execute "INSERT INTO USER_WORK_DETAIL SELECT * FROM " & ExportDB & ".DBO.USER_WORK_DETAIL"
TCrDB.Execute "INSERT INTO ITEM_ON_MASTER SELECT * FROM " & ExportDB & ".DBO.ITEM_ON_MASTER"
If ExportDB <> "TamannaDB" Then TCrDB.Execute "USE MASTER"
If ExportDB <> "TamannaDB" Then TCrDB.Execute "DROP DATABASE " & ExportDB
If TCrDB.State Then TCrDB.Close
Exit Function
HELL:
MsgBox Err.Description
End Function