Really simple question about VBA & SQL

[Matt Dunphy]

Having delved into VBA for the first time this week, I've made some
pretty good progress in writing a small app that dumps monthly actuals
for assignments into an Excel spreadsheet, but have come to a
roadblock. Now that I can generate this report for an open project, I
need to run it on every Project on the server. I figure I can do this
by using a couple of simple SQL Statements to count and pull the
project names from the MSP_Projects table.

But I can't get VBA talking to the SQL server. I know I'm missing
something very fundamental and easy, but my background in this
development environment is nonexistant. I've figured enough out so far
between the SDK and some helpful posts here, and if someone could help
me clear this little hurdle, that'd be fantastic.

I've plagiarized most of this little subroutine from this very group:
******************************
Const strConn As String = "Provider=MSDASQL.1; blah blah blah"
Dim strSQL1, strSQL2 As String
Dim cn As New Connection
Dim rs As New Recordset
Dim fld As Field

cn.Open strConn
strSQL1 = "SELECT count(PROJ_ID) AS ProjectCount FROM MSP_Projects"
strSQL2 = "SELECT proj_Name FROM MSP_Projects"
rs.Open strSQL1, cn

For Each fld In rs.Fields
Debug.Print fld.Name, fld.Value
Next
******************
I've set up a tested-and-working ODBC connection, given my account
permissions on the SQL server, created a UDL file (from which I grabbed
the connection string), and I was rearing to go, but VBA doesn't really
understand what Connection, Recordset, and Field are. I figure I must
be missing some kind of 'include' that defines these items. I've
racked my brain (and Google's coffers) for an answer, and I've read
something about Referencing an OLEDB DLL, but haven't found anything
that makes this work.

What, precisely, am I missing? If you can throw me a key word to
further research, I'll happily take that, if you give me straight-up
code, that's cool to. All I need is to fill a VBA array with all the
Project names on the server.

 

[Matt Dunphy]

Naturally, after I post a cry for help, I realize the reason I cannot
click on "references" under "tools" is because I was in debug mode
every time I tried to click on that. FWIW, I now have the OLE DB
Service Component referenced, along with Forms, Excel, Office, OLE
Automation, VBA and Project 11. I'm guessing that referencing this OLE
DB is probably the last step, if not close to the last step, that I
needed.

 

[Maruf]

Check this Code. It might help you.
Thanks,
Maruf

Const sConnPT = "DRIVER={SQL
Server};SERVER=ServerName;DATABASE=ProjectServer;UID=dbuser;PWD=dbuser"

Dim sSQL As String
Dim conn, cmd, rs As Object


Set conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
conn.Open modGlobal.sConnPT
Set cmd.ActiveConnection = conn

sSQL = "select Count(*) as fld1 from msp_projects"

Set rs = cmd.Execute(sSQL)
MsgBox str(rs("fld1"))

rs.Close

Set cmd = Nothing
Set conn = Nothing

 

[Carole]

Hello,

I think i might use this code, but i'll be sure. I want to fill a table with
the list of projects name to open them and save them on a disk. The projects
are in an oracle database and on the computer there is a odbbc source
declared.

Thanks