shyamvinjamuri
Board Regular
- Joined
- Aug 2, 2006
- Messages
- 175
I want to write results from excel to database.
Sheet1, column A has unique IDs and column B has calculated values. I am retrieving the IDs and other data from the data base ( so the data base hase the same IDs )
Here is the path and VB for retriving the data. How can I write the value in column B to the database corrosponding to the IDs in Column B
T1.bid is the ID and T1.upb is where I want to write the values in column B
Please help.
Thanks
Public Sub Get_Data_Click()
Dim Conn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim Errs As Errors
Dim rs As New ADODB.Recordset
Dim sqlquery As String
Dim dayNum As Variant
Dim i As Double
'Dim j As Integer
Dim A As Integer
Dim B As String
Dim AccessConnect As String
' Error Handling Variables
Dim errLoop As Error
Dim strTmp As String
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=Dallas.mdb;" & _
"DefaultDir=F:\Residential Acquisition\Dallas;" & _
"Uid=Admin;Pwd=;"
'On Error GoTo ErrorHandler
dayNum = InputBox("How many days do you want to go back for pricing?" & vbCrLf _
& vbCrLf _
& " 0 for today, 1 for 1 day back, etc.", "DAYS Entry Form", "1", 500, 700)
If IsNumeric(dayNum) = False Or (IsNumeric(dayNum) = True And dayNum < 0) Then
MsgBox "Your input is " & dayNum
MsgBox "Please enter a valid integer number.", vbOKOnly, "Error"
Exit Sub
End If
' Recordset Open
Application.StatusBar = "Reading Database..."
sqlquery = " SELECT T.*, T3.buyer_name FROM (SELECT T1.bid, T1.bid_type, T1.bid_date, T1.broker, T1.upb, T1.rate, T1.seasoning, T1.ltv, " _
& " T1.credit_score, T1.state, T1.payment, T1.term, T1.bid_value, T1.zip, T1.occupied_by, T1.term_total, " _
& " T1.payment_1st, T1.payment_next, T1.loan_amt, T1.sales_price, T1.cltv, T1.property_class, T1.interest_only,T1.buyer, T1.comments, T1.city, T1.payment, T2.name, T1.lien_pos " _
& " FROM dbo_bids T1 left join dbo_brokers T2 " _
& " on T1.broker=T2.broker " _
& " where T1.bid_type<>1 and T1.bid_type<>6 and T1.lien_pos<>1 and DateDiff('d',T1.bid_date,date())=" & CInt(dayNum) & " and T1.property_class<>'CM') T left join dbo_buyers T3" _
& " on T.buyer=T3.buyer"
rs.Open sqlquery, AccessConnect, adOpenForwardOnly
i = 0
If rs.BOF And rs.EOF Then
MsgBox ("No Records Retrieved")
Exit Sub
End If
If Not rs.EOF Then rs.MoveFirst
Sheet1, column A has unique IDs and column B has calculated values. I am retrieving the IDs and other data from the data base ( so the data base hase the same IDs )
Here is the path and VB for retriving the data. How can I write the value in column B to the database corrosponding to the IDs in Column B
T1.bid is the ID and T1.upb is where I want to write the values in column B
Please help.
Thanks
Public Sub Get_Data_Click()
Dim Conn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim Errs As Errors
Dim rs As New ADODB.Recordset
Dim sqlquery As String
Dim dayNum As Variant
Dim i As Double
'Dim j As Integer
Dim A As Integer
Dim B As String
Dim AccessConnect As String
' Error Handling Variables
Dim errLoop As Error
Dim strTmp As String
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=Dallas.mdb;" & _
"DefaultDir=F:\Residential Acquisition\Dallas;" & _
"Uid=Admin;Pwd=;"
'On Error GoTo ErrorHandler
dayNum = InputBox("How many days do you want to go back for pricing?" & vbCrLf _
& vbCrLf _
& " 0 for today, 1 for 1 day back, etc.", "DAYS Entry Form", "1", 500, 700)
If IsNumeric(dayNum) = False Or (IsNumeric(dayNum) = True And dayNum < 0) Then
MsgBox "Your input is " & dayNum
MsgBox "Please enter a valid integer number.", vbOKOnly, "Error"
Exit Sub
End If
' Recordset Open
Application.StatusBar = "Reading Database..."
sqlquery = " SELECT T.*, T3.buyer_name FROM (SELECT T1.bid, T1.bid_type, T1.bid_date, T1.broker, T1.upb, T1.rate, T1.seasoning, T1.ltv, " _
& " T1.credit_score, T1.state, T1.payment, T1.term, T1.bid_value, T1.zip, T1.occupied_by, T1.term_total, " _
& " T1.payment_1st, T1.payment_next, T1.loan_amt, T1.sales_price, T1.cltv, T1.property_class, T1.interest_only,T1.buyer, T1.comments, T1.city, T1.payment, T2.name, T1.lien_pos " _
& " FROM dbo_bids T1 left join dbo_brokers T2 " _
& " on T1.broker=T2.broker " _
& " where T1.bid_type<>1 and T1.bid_type<>6 and T1.lien_pos<>1 and DateDiff('d',T1.bid_date,date())=" & CInt(dayNum) & " and T1.property_class<>'CM') T left join dbo_buyers T3" _
& " on T.buyer=T3.buyer"
rs.Open sqlquery, AccessConnect, adOpenForwardOnly
i = 0
If rs.BOF And rs.EOF Then
MsgBox ("No Records Retrieved")
Exit Sub
End If
If Not rs.EOF Then rs.MoveFirst