Hi there,
I'm not sure the kind of vba code you're looking for. If it's about
using vlookup, match and so on to retrieve data, so far, i'm not able
to do much just following you sql code. If you want me to help, copy
paste the tables you want to query on and I see
Comments in the code below may help you. It's a simple Excel-Ms Sql
Server connection to update a table using Vba for Excel.
I don't understand advanced sql meanings such as
[SRT1].RequestToolDev.dbo.ReqAMROOS and .ReqMain. Are these tables,
what kind of data, fields? Provide a sample of your tables on a
worksheet if you can then I'll modify the code below to see if it
works.
Cheers,
Pascal Baro
Sub Databases()
'ADO = activex data objects
'data source (SQL database) <--> OLEDB Provider <--> ADO Data objects
'YOU NEED A REFERENCE TO ADO LIBRARY
Dim sSql As String
Dim rs As ADODB.Recordset 'rs holds the data
Dim cn As ADODB.Connection ' declare connection
Dim cmdObj As ADODB.Command 'declare a command object
Dim qf As Object
Dim numberOfRowsAffected As Integer
Dim IDtoUPdate As Integer
Dim varName As String
Dim varAge As Integer
Sheets("sheet1").Select
Cells.Select
Selection.ClearContents
'''''''''''''''''''''''''''''''''''''''''''SELECT STATEMENT
Set cn = New ADODB.Connection
cn.Open "Provider= SQLOLEDB.1; Integrated Security = SSPI; username =
test; password = test; Initial catalog =MyCompany;Data Source =XXX-PC
\SQLEXPRESS"
sSql = "select id, name, age, date from people"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient 'who manages the cursor ADO
(adUSEclient) or Server (adUseServer)
rs.Open sSql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
'check if rs is empty
If rs.EOF Then
MsgBox ("The record set is empty. rs.EOF = " & rs.EOF)
Else
MsgBox ("The number of rows returned from the select statement is : "
& rs.RecordCount)
Range("a1").Select
For Each qf In rs.Fields
Range("a1").Offset(0, coloffset).Value = qf.Name
coloffset = coloffset + 1
Next qf
Range("A2").CopyFromRecordset rs 'send data to worksheet
rs.MoveLast 'go to the last row of the record set
IDtoUPdate = rs.Fields(0) 'Get the ID of the last row
rs.Close 'close the recordset
Set rs = Nothing 'DESTROY THE Recordset!!!!
End If
varName = "xxx"
varAge = 100
varDate = "1/1/2005"
'''''''''''''''''''''''''''''''''''''''''''UPDATE STATEMENT
sSql = "update people set name = '" & varName & "' where id = " &
IDtoUPdate
cn.Execute sSql, numberOfRowsAffected, adCmdText
MsgBox ("Number of Rows Updated = " & numberOfRowsAffected)
''''''''''''''''''''''''''''''''''''''''''''INSERT STATEMENT
sSql = "insert into people (name, age, date) values ('bob'," & varAge
& ",'" & varDate & "')"
cn.Execute sSql, numberOfRowsAffected, adCmdText
MsgBox ("Number of Rows Inserted = " & numberOfRowsAffected)
'''''''''''''''''''''''''''''''''''''''''''DELETE STATEMENT
'sSql = "delete from people where id >6"
'cn.Execute sSql, numberOfRowsAffected, adCmdText
'MsgBox ("Number of Rows Deleted = " & numberOfRowsAffected)
'''''''''''''''''''''''''''''''''''''''''''''STORED PROCEDURE that
SELECTS DATA
Set cmdObj = New ADODB.Command
cmdObj.ActiveConnection = cn
cmdObj.CommandType = adCmdStoredProc
cmdObj.CommandText = "GetPeopleData"
Set prm = cmdObj.CreateParameter("id", adInteger, adParamInput)
cmdObj.Parameters.Append prm
cmdObj.Parameters("id").Value = 21
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient 'sets cursor location ADO
(adUSeClient) or Server (adUSEserver)
rs.CursorType = adOpenStatic 'sets teh cursor type how can the
recordset be manuvered?
rs.LockType = adLockOptimistic 'sets the lock type
rs.Open cmdObj 'this executes the Stored PROC
coloffset = 0
If rs.EOF Then
MsgBox ("The record set is empty. rs.EOF = " & rs.EOF)
Else
Range("f1").Select
For Each qf In rs.Fields
Range("f1").Offset(0, coloffset).Value = qf.Name
coloffset = coloffset + 1
Next qf
'''''manipulate data in the recordset in memory and in the
Database!!
Do While Not rs.EOF
rs.Fields(1).Value = "changed name" 'this changed the data in
the DB
rs.MoveNext
Loop
rs.MoveFirst 'MOVE BACK TO FIRST RECORD
Range("f2").CopyFromRecordset rs 'send data to worksheet
rs.Close 'close the recordset
Set rs = Nothing 'DESTORY THE record set
End If
cn.Close 'close the connection to the database
Set cn = Nothing 'DESTROY CONNECTION to the database
End Sub
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
