<%
  Set SQLConnect = Server.CreateObject("ADODB.Connection")
  SQLConnect.Open "Driver={SQL Server};Server=208.18.48.246;Database=houseviewonline;Uid=houseviewonline;Pwd=supernova04;"
  Set Connect = Server.CreateObject("ADODB.Connection")
  Connect.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=f:\webroot\houseview-online.com\database\houseview.mdb;"


'SQLConnect.Execute("DROP TABLE [dbo].[Listings]")
'SQLConnect.Execute("DROP TABLE [dbo].[Locations]")
'SQLConnect.Execute("DROP TABLE [dbo].[Agents]")
'SQLConnect.Execute("DROP TABLE [dbo].[Groupemails]")


'Create Listings Table SQL Server-------------------------------------------
   strSQL = "CREATE TABLE [dbo].[Listings] ("
   strSQL = strSQL & "[ID] [int] IDENTITY (1, 1) NOT NULL ,"
   strSQL = strSQL & "[RealitorAbb] [nvarchar] (50) NULL ,"
   strSQL = strSQL & "[price] [int] NULL ,"
   strSQL = strSQL & "[PriceCat] [int] NULL ,"
   strSQL = strSQL & "[Bthrm] [nvarchar] (50) NULL ,"
   strSQL = strSQL & "[Bdrm] [nvarchar] (50) NULL ,"
   strSQL = strSQL & "[address] [nvarchar] (80) NULL ,"
   strSQL = strSQL & "[Location] [nvarchar] (50) NULL ,"
   strSQL = strSQL & "[homedesc] [text] NULL ,"
   strSQL = strSQL & "[Pano] [nvarchar] (50) NULL ,"
   strSQL = strSQL & "[Grouping] [int] NULL ,"
   strSQL = strSQL & "[Pending] [nvarchar] (5) NULL ,"
   strSQL = strSQL & "[MLS] [int] NULL ,"
   strSQL = strSQL & "[HZ] [nvarchar] (5) NULL ,"
   strSQL = strSQL & "[listingdate] [nvarchar] (50) NULL ,"
   strSQL = strSQL & "[photographer] [nvarchar] (125) NULL ,"
   strSQL = strSQL & "[invoicedate] [nvarchar] (50) NULL ,"
   strSQL = strSQL & "[solddate] [nvarchar] (50) NULL ,"
   strSQL = strSQL & "[listingnotes] [text] NULL ,"
   strSQL = strSQL & "[invoicedate2] [nvarchar] (50) NULL ,"
   strSQL = strSQL & "[shoottype] [nvarchar] (25) NULL ,"
   strSQL = strSQL & ") ON [PRIMARY]"

SQLConnect.Execute(strSQL)
strSQL = "" 


'Create Locations Table in SQL Server-------------------------------------------
   strSQL = "CREATE TABLE [dbo].[Locations] ("
   strSQL = strSQL & "[ID] [int] IDENTITY (1, 1) NOT NULL ,"
   strSQL = strSQL & "[Location] [nvarchar] (125) NULL ,"
   strSQL = strSQL & ") ON [PRIMARY]"

SQLConnect.Execute(strSQL)
strSQL = "" 


'Create Agents Table in SQL Server-------------------------------------------

   strSQL = "CREATE TABLE [dbo].[Agents] ("
   strSQL = strSQL & "[ID] [int] IDENTITY (1, 1) NOT NULL ,"
   strSQL = strSQL & "[RealitorDesc] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[AgentFirst] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[AgentLast] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[Email] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[Pgr] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[Bus] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[Grouping] [nvarchar] (5) NULL,"
   strSQL = strSQL & "[Res] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[RealitorAbb] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[address] [nvarchar] (125) NULL,"
   strSQL = strSQL & "[city] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[state] [nvarchar] (5) NULL,"
   strSQL = strSQL & "[zip] [nvarchar] (10) NULL,"
   strSQL = strSQL & "[splitbilling] [nvarchar] (15) NULL,"
   strSQL = strSQL & "[prebill] [nvarchar] (15) NULL,"
   strSQL = strSQL & "[leadphone] [bit] NOT NULL,"
   strSQL = strSQL & "[leadphoneaddress] [nvarchar] (50) NULL,"
   strSQL = strSQL & ") ON [PRIMARY]"

SQLConnect.Execute(strSQL)
strSQL = "" 


'Create GroupEmails Table in SQL Server-------------------------------------------

   strSQL = "CREATE TABLE [dbo].[Groupemails] ("
   strSQL = strSQL & "[ID] [int] IDENTITY (1, 1) NOT NULL ,"
   strSQL = strSQL & "[grouping] [int] NULL,"
   strSQL = strSQL & "[email] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[name] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[agency] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[fax] [nvarchar] (50) NULL,"
   strSQL = strSQL & "[usemethod] [nvarchar] (15) NULL,"
   strSQL = strSQL & ") ON [PRIMARY]"

SQLConnect.Execute(strSQL)
strSQL = ""


'End Delete/Re-populate SQL Server Listings--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'SQLConnect.Execute("DELETE Listings")
'  Response.Write("<br>Listings Deleted Successfully From SQL Server<br>")

  sqlnew="SELECT * FROM Listings ORDER BY ID ASC" 
  Set RS = Connect.execute(sqlnew)

If Not RS.BOF Then
	RS.MoveFirst
   Do Until RS.EOF

sqlnew="insert into Listings(RealitorAbb,price,PriceCat,Bthrm,Bdrm,address,Location,homedesc,Pano,Grouping,Pending,MLS,HZ,listingdate,photographer,invoicedate,solddate,listingnotes,invoicedate2,shoottype) values ('"&RS.Fields("RealitorAbb")&"','"&RS.Fields("price")&"','"&RS.Fields("PriceCat")&"','"&Replace(RS.Fields("Bthrm"),"'","''")&"','"&Replace(RS.Fields("Bdrm"),"'","''")&"','"&Replace(RS.Fields("address"),"'","''")&"','"&Replace(RS.Fields("Location"),"'","''")&"','"&Replace(RS.Fields("homedesc"),"'","''")&"','"&RS.Fields("Pano")&"','"&RS.Fields("Grouping")&"','"&RS.Fields("Pending")&"','"&RS.Fields("MLS")&"','"&RS.Fields("HZ")&"','"&RS.Fields("listingdate")&"','"&RS.Fields("photographer")&"','"&RS.Fields("invoicedate")&"','"&RS.Fields("solddate")&"','"&RS.Fields("listingnotes")&"','"&RS.Fields("invoicedate2")&"','"&RS.Fields("shoottype")&"')"
SQLConnect.execute(sqlnew)

  RS.MoveNext
   Loop
End if
  RS.Close
  Set RS = Nothing
  sqlnew=""
  Response.Write("Listings Successfully Re-Synced From Access to SQL Server<br>")
'End Delete/Re-populate SQL Server Listings--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


'End Delete/Re-populate SQL Server Agents--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'SQLConnect.Execute("DELETE Agents")
'  Response.Write("<br>Agents Deleted Successfully From SQL Server<br>")

  sqlnew="SELECT * FROM Agents ORDER BY ID ASC" 
  Set RS = Connect.execute(sqlnew)

function GetValuetype(strvalue)
 If strvalue = False Then
  GetValuetype = 0
 Elseif strvalue = True Then
  GetValuetype = 1
 End if
End Function

If Not RS.BOF Then
	RS.MoveFirst
   Do Until RS.EOF

sqlnew="insert into Agents(RealitorDesc,AgentFirst,AgentLast,Email,Pgr,Bus,Grouping,Res,RealitorAbb,address,city,state,zip,splitbilling,prebill,leadphone,leadphoneaddress) values ('"&RS.Fields("RealitorDesc")&"','"&RS.Fields("AgentFirst")&"','"&RS.Fields("AgentLast")&"','"&RS.Fields("Email")&"','"&RS.Fields("Pgr")&"','"&RS.Fields("Bus")&"',"&RS.Fields("Grouping")&",'"&RS.Fields("Res")&"','"&RS.Fields("RealitorAbb")&"','"&RS.Fields("address")&"','"&RS.Fields("city")&"','"&RS.Fields("state")&"','"&RS.Fields("zip")&"','"&RS.Fields("splitbilling")&"','"&RS.Fields("prebill")&"','"&GetValuetype(RS.Fields("leadphone"))&"','"&RS.Fields("leadphoneaddress")&"')"
SQLConnect.execute(sqlnew)

  RS.MoveNext
   Loop
End if
  RS.Close
  Set RS = Nothing
  sqlnew=""
  Response.Write("Agents Successfully Re-Synced From Access to SQL Server<br>")
'End Delete/Re-populate SQL Server Agents--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


'End Delete/Re-populate SQL Server Locations--------------------------------------------------------
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'SQLConnect.Execute("DELETE Locations")
'  Response.Write("<br>Locations Deleted Successfully From SQL Server<br>")

  sqlnew="SELECT * FROM Locations ORDER BY ID ASC" 
  Set RS = Connect.execute(sqlnew)

If Not RS.BOF Then
	RS.MoveFirst
   Do Until RS.EOF

sqlnew="insert into Locations(Location) values ('"&RS.Fields("Location")&"')"
SQLConnect.execute(sqlnew)

  RS.MoveNext
   Loop
End if
  RS.Close
  Set RS = Nothing
  sqlnew=""
  Response.Write("Locations Successfully Re-Synced From Access to SQL Server<br>")
'End Delete/Re-populate SQL Server Locations--------------------------------------------------------
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


'End Delete/Re-populate SQL Server GroupEmails--------------------------------------------------------
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'SQLConnect.Execute("DELETE Groupemails")
'  Response.Write("<br>GroupEmails Deleted Successfully From SQL Server<br>")

  sqlnew="SELECT * FROM Groupemails ORDER BY ID ASC" 
  Set RS = Connect.execute(sqlnew)

If Not RS.BOF Then
	RS.MoveFirst
   Do Until RS.EOF

sqlnew="insert into Groupemails(grouping,email,name,agency,fax,usemethod) values ('"&RS.Fields("grouping")&"','"&RS.Fields("email")&"','"&RS.Fields("name")&"','"&RS.Fields("agency")&"','"&RS.Fields("fax")&"','"&RS.Fields("usemethod")&"')"
SQLConnect.execute(sqlnew)

  RS.MoveNext
   Loop
End if
  RS.Close
  Set RS = Nothing
  sqlnew=""
  Response.Write("GroupEmails Successfully Re-Synced From Access to SQL Server<br>")
'End Delete/Re-populate SQL Server GroupEmails--------------------------------------------------------
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Response.Write("<br><b>HouseViewOnline.com Access/SQL Server Sync Completed on " & now() & "</b><br>")

   Connect.Close
   Set Connect = Nothing
   SQLConnect.Close
   Set SQLConnect = Nothing
%>