Querying a SQL Server Database

[Chaplain Doug]

SQL Server 2000. Excel 2003. I know how to set an Excel sheet to query my
SQL Server database table(s) and populate the sheet cells. This works for
some of my needs. But in general I would like to be able to do a query and
have the results go to a record set, that I can then programmatically
manipulate. Can anyone help me with some sample VBA code that populates a
record set from a SQL Server database? Thanks and God bless for any help
provided.

What I am using now to populate the Excel sheet is:

With ThisWorkbook.Sheets("Project
Definitions").QueryTables.Add(Connection:= _

"ODBC;DSN=AFNINI_1;Description=Good_News_FE;UID=FEopen7;;APP=Microsoft Office
2003;WSID=DOUG;DATABASE=Good_News_FE" _
, Destination:=Range("J1"))
.CommandText = "SELECT GL7PROJECTS.PROJECTID,
GL7PROJECTS.DESCRIPTION, GL7PROJECTS.STARTDATE, TABLEENTRIES.DESCRIPTION FROM
(GL7PROJECTS LEFT JOIN GL7PROJECTATTRIBUTES ON GL7PROJECTS.GL7PROJECTSID =
GL7PROJECTATTRIBUTES.PARENTID) LEFT JOIN TABLEENTRIES ON
GL7PROJECTATTRIBUTES.TABLEENTRIESID = TABLEENTRIES.TABLEENTRIESID ORDER BY
GL7PROJECTS.PROJECTID;"
.Name = "Project Attributes Query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

 

[Tom Ogilvy]

http://tinyurl.com/d2yva

 

[Bob Phillips]

Look at the CopyFromRecordset method in VBA help.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Chaplain Doug]

Dear Bob:

It appears that the CopyFromRecordset works on an ADO recordset already
created. What I need is some sample VBA code to create the ADO recordset
from an SQL Server database to begin with. I do not know how to do this.
Can you help?
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Look at the CopyFromRecordset method in VBA help.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DM Unseen]

I'm currently developing code that helps with that, but its not
finished yet. At least I know now there is some demand for it.

Just to be curious, why do you need the ADO recordset at all when you
have a good querytable?

Dm Unseen

 

[Chaplain Doug]

There are some instaces where I just want to get some data from the SQL
database and not populate an Excel sheet.

 

[Bob Phillips]

Here is an example of getting data from an Access table

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub

you will need to connect to SQL server, so the conn ection string will
change (don't have an example to hand I am afraid), of which your
connecttion string of

ODBC;DSN=AFNINI_1;Description=Good_News_FE;UID=FEopen7;;APP=Microsoft Office
2003;WSID=DOUG;DATABASE=Good_News_FE"

might well work, but I would check
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer
for an OLE DB version.

Use your SQL code where I have sSQL

--

HTH

RP
(remove nothere from the email address if mailing direct)

Dear Bob:

It appears that the CopyFromRecordset works on an ADO recordset already
created. What I need is some sample VBA code to create the ADO recordset
from an SQL Server database to begin with. I do not know how to do this.
Can you help?

 

[Tom Ogilvy]

There are some instaces where I just want to get some data from the SQL
database and not populate an Excel sheet.

 

[Tom Ogilvy]

Here is an example of getting data from an Access table

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub

you will need to connect to SQL server, so the conn ection string will
change (don't have an example to hand I am afraid), of which your
connecttion string of

ODBC;DSN=AFNINI_1;Description=Good_News_FE;UID=FEopen7;;APP=Microsoft Office
2003;WSID=DOUG;DATABASE=Good_News_FE"

might well work, but I would check
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer
for an OLE DB version.

Use your SQL code where I have sSQL

--

HTH

RP
(remove nothere from the email address if mailing direct)


works

 

[Tom Ogilvy]

Dear Bob:

It appears that the CopyFromRecordset works on an ADO recordset already
created. What I need is some sample VBA code to create the ADO recordset
from an SQL Server database to begin with. I do not know how to do this.
Can you help?

 

[Tom Ogilvy]

I'm currently developing code that helps with that, but its not
finished yet. At least I know now there is some demand for it.

Just to be curious, why do you need the ADO recordset at all when you
have a good querytable?

Dm Unseen