Re: SQL incorporation with Excel

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Robin Hammond (rjNOrhSPAM_at_PLEASEnetvigator.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 11:16:56 +0800

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

"gitcypher >" <<gitcypher.13y4kt@excelforum-nospam.com> wrote in message
news:gitcypher.13y4kt@excelforum-nospam.com...
> Each month, I have to create multiple invoices. I have many Excel
> 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 each
> employee, and the formulas and macros do the rest. The hours are
> entered throughout month into our time keeping system.
> The transition between our time keeping system, and our accounting
> system has been srteamlined. Our accounting system uses an SQL
> database. I would like to enable my billing template to pull employee's
> hours directly from the SQL database, removing any human interference.
>
> I know absolutely nothing about SQL... so you know what you're dealing
> with. Is there somewhere I can find a quick tutorial, or can someone
> giv e me a quick run down of the process?
>
> -Gitcypher
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

Relevant Pages

  • Re: Pessimistic locking with approles.
    ... The "fix" is to disable connection pooling in ADO in the connection ... security or SQLS security, and it has nothing whatsoever to do with ... SQL Application Role Errors with OLE DB Resource Pooling ...
    (microsoft.public.sqlserver.security)
  • Re: Error -2147168227 Cannot create new transaction because capacity was exceeded.
    ... Try to run SQL profiler, which ships with SQL Server to see how many ... > object and there is only 1 instance per application of the connection ... Prior to that I've just closed the ADO recordset object. ...
    (microsoft.public.data.ado)
  • Re: ADO Connection.Execute Method leaves Open SQL Process
    ... I am trying to improve a VB6 app that accesses SQL Server data via ADO ... The connection that invokes the Execute method is created, opened, ...
    (microsoft.public.vb.database.ado)
  • Re: CoInitialize failure with many processes
    ... directly communicate with the SQL server. ... _connection object (apparently an ADO object, ... You can use the SQL Server Profiler to examine the number of connections ...
    (microsoft.public.win32.programmer.ole)
  • Re: Automating Access from Excel
    ... This might be a help for getting data to and from Excel and Access: ... includes examples of using variables in SQL queries. ... * delete a database. ... DAO and ADO files available. ...
    (microsoft.public.excel.programming)