SQL incorporation with Excel

  • Thread starter Thread starter gitcypher
  • Start date Start date
G

gitcypher

Each month, I have to create multiple invoices. I have many Exce
workbooks, all based on the same billing template. If all goes well
each month, all I have to do is enter the hours worked by eac
employee, and the formulas and macros do the rest. The hours ar
entered throughout month into our time keeping system.
The transition between our time keeping system, and our accountin
system has been srteamlined. Our accounting system uses an SQ
database. I would like to enable my billing template to pull employee'
hours directly from the SQL database, removing any human interference

I know absolutely nothing about SQL... so you know what you're dealin
with. Is there somewhere I can find a quick tutorial, or can someon
giv e me a quick run down of the process?

-Gitcyphe
 
Michael Brydon provides an excellent (although somewhat dated,) MSAccess
tutorial which can teach you the wonderous basics of DAO and SQL (Data
Access Objects and Standard Query Language,) as it applies to MSAccess. I've
found that despite it's gearing toward Access users, this is the best place
to start learning because Access provides you with a graphical
representation of databases, recordsets, queries, etc., a basis on which you
can rely when you begin thinking only in terms of DAO and SQL code (which is
readily applied to all MS Office Apps.)

It's not very long, and it's presented in an easily readable PDF format.
Spend a little time and effort, and you could be on the road to SQL queries
in a few short hours. And trust me, I probably make it sound a lot harder
than it is.

His page is here:

http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials.html

-Mike Mertes
 
Excel 2002 VBA by Bullen, Green, Bovey and Rosenberg has a fairly good
section on using ADO with Excel, and this would be your best method of
connecting to the SQL server.

As a starting point, get the latest MDAC 2.8 (and the subsequent security
patch) from MS. Set a reference to ADO 2.7 in your vb project. The code
below should give you a starting point but you'll need to work on the
connection string to find the correct way to connect. This can be
complicated but a bit of trial and error should get it.

Sub ConnectToSQL()
'requires an ADO reference in project
Dim rsInput As ADODB.Recordset
Dim cnSQL As ADODB.Connection
Dim strSQL As String

Set cnSQL = New ADODB.Connection
Set rsInput = New ADODB.Recordset

'there are lots of different ways to specify this
'The MZTools utility at www.mztools.com provides a utility that
'could help and that tests the connection as you specify it
cnSQL.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;Data Source=MyServer;" & _
"Initial File Name=c:\My Documents\My Dbs\My Database.mdb"

cnSQL.Open
strSQL = "SELECT * FROM MyView"
rsInput.Open strSQL, cnSQL, adOpenStatic, adLockBatchOptimistic, adCmdText

'optionally close the connection to disconnect the recordset
If cnSQL.State = adStateOpen Then cnSQL.Close

'if you have a valid record, this should show a positive number
MsgBox rsInput.RecordCount
End Sub

Robin Hammond
www.enhanceddatasystems.com
 
Mike Mertes said:
Michael Brydon provides an excellent (although somewhat dated,) MSAccess
tutorial which can teach you the wonderous basics of DAO and SQL (Data
Access Objects and Standard Query Language,) as it applies to MSAccess.
Click to expand...

MS Jet (a.k.a. MS Access) is regarded as a poor implementation of ANSI
standard SQL. You'd do well to learn the standard, for reasons of
portability, before getting too accustomed to Jet's proprietary syntax
(it's said that it takes at least six months to 'unlearn' Jet). The
aforementioned MS Access course looks 'light' on the SQL side of
things to me anyhow. I recommend the following as a starting point:

http://www.sqlcourse.com/

And you'd do well to learn set based programming using SQL before
looking at procedural programming using a data access technology such
as ADO or its forerunner DAO. Set based programming involves a
completely different mindset and if you launch straight in with
recordsets you risk missing the point.

BTW is a popular myth that S.Q.L. stands for 'Standard Query
Language'. Although it is officially pronounced 'ess-que-ell', SQL
actually doesn't stand for anything (do some research on Dr Codd and
the IBM SEQUEL project). Disappointing but true.

--
 
Robin Hammond said:
you'll need to work on the
connection string to find the correct way to connect. This can be
complicated but a bit of trial and error should get it.
Click to expand...

Here's a function which uses the OLE DB 'data links' GUI dialog to
create a connection string:

Public Function GetConString() As String

' Requires reference to
' Microsoft OLE DB Service Component 1.0 Type Library
Dim oDLink As MSDASC.DataLinks
Set oDLink = New MSDASC.DataLinks

On Error Resume Next
GetConString = oDLink.PromptNew

End Function

--
 
Back
Top