Database Articles

I sometimes use Excel to extract data. This will be choppy but you

should be able to figure it out or at least search the web for more

detailed instructions for your specific need.

1- Create a button in excel.

2- Rename the button and caption if you like (I renamed the button to

‘UpdateData’).

3- Double-click the button so the vb editor opens.

4- Edit the Sub it creates for you to call another sub you will make

later:

My code looks like this (notice the sub has the name I used):

Private Sub UpdateData_Click()

UpdateTririgaData_Click

End Sub

5- In the Microsoft Visual Basic editor look at the left pane, the

viewer to the left should have a Modules section, right click and create

a new module.

6- In the module paste something like this (notice there are two subs):

Public Sub UpdateTririgaData_Click()

Sheets(“raw”).Select

Rows(“2:10000″).Select

Selection.ClearContents

ImportTririgaProjectData

End Sub

Private Sub ImportTririgaProjectData()

‘ Get the start and end times

Dim startdate As String

Dim enddate As String

startdate = Worksheets(“Pivot”).Range(“B1″).Value

enddate = Worksheets(“Pivot”).Range(“B2″).Value

‘ Create a connection object.

Dim cnTridata As ADODB.Connection

Set cnTridata = New ADODB.Connection

‘ Provide the connection string.

Dim strConn As String

‘Use the SQL Server OLE DB Provider.

strConn = “PROVIDER=SQLOLEDB;”

‘Connect to the Pubs database on the local server.

strConn = strConn & “DATA SOURCE=;”

strConn = strConn & “INITIAL CATALOG=;”

strConn = strConn & “User ID=;”

strConn = strConn & “Password=;”

‘Use an integrated login.

’strConn = strConn & ” INTEGRATED SECURITY=sspi;”

‘Now open the connection.

cnTridata.Open strConn

‘ Create a recordset object.

Dim rsTridata As ADODB.Recordset

Set rsTridata = New ADODB.Recordset

With rsTridata

‘ Assign the Connection object.

.ActiveConnection = cnTridata

‘ Extract the required records.

.Open ” select t2.OrganizationName, t7.Priority, t7.WorkId,

t7.Description, t1.PlannedEndDateTime, t1.ActualEndDateTime ” _

& “, t7.Status ” _

& ” from T_WORKTASK t1 left outer join T_ORGANIZATIONCONTAINER

t2 on t1.AssignedToSysKey2=t2.spec_id left outer join V_WORKCONTAINER

t7 on t1.AssociatedToSysKey2=t7.spec_id ” _

& ” where t1.SYS_OBJECTID > 0 ” _

& ” and t1.PlannedEndDateTime/1000 >= datediff(second,

‘01/01/1970′, ‘” & startdate & “‘) ” _

& ” and t1.PlannedEndDateTime/1000 0 ” _

& ” order by upper(t2.OrganizationName) ,upper(t7.Priority)

,upper(t7.WorkId) ”

‘& “, cast(DATEADD(s,cast(t1.ActualEndDateTime/1000 as

int),’01/01/1970′) as DateTime) as ActualEndDate ” _

‘ Copy the records into cell A1 on Sheet1.

Worksheets(“raw”).Range(“A2″).CopyFromRecordset rsTridata

‘ Tidy up

.Close

End With

cnTridata.Close

Set rsTridata = Nothing

Set cnTridata = Nothing

End Sub

7- You will have to change ” PROVIDER=SQLOLEDB;” if you are connecting

to Oracle. You will have to update all the connection information. You

will have to change any worksheet references that I have to worksheets

that you have.

8- Additionally, before you test your new button you will have to make

sure you have added the correct driver into your vb editor. To do this

click Tools->References

8a- I have ensured that ‘Microsoft ActiveX Data Objects 2.8 Library’ was

checked.

9- Now go back to your new button and click it, ensure that your not in

‘Design Mode’ (the icon with a ruler, pencil and 45′ drafting square in

it – you may have to added it to your menu bar first)

hello sir

i have read many articles regarding clustered index and non clustered index.but i am unable to figure out the difference in real practical scenario.when we should choose clustered or nonclustred for a column.whats the real difference as far as performace is concerned.please help me

Hello webmaster
I would like to share with you a link to your site
write me here preonrelt@mail.ru

sir can you tell me more about Linking Servers or remote servers through IP configuration with remote servers