Nikita6003: SQL Question for you! (Or anyone else!)

[VBAjedi]

Nikita,

Hope things are well there in the Netherlands. I was there for a week each of the last two summers and loved it.

I saw you mention in another thread today that you use Excel as a front end for SQL dbases. That's exactly what I'm trying to learn how to do. How did you learn to do this? Can you point me in the direction of any good material on the subject? Or do you have any good example workbooks that demonstrate this technique?

Many thanks for any input you can offer!

VBAjedi

 

[mscallisto]

The Excel help on SQL offers a whole section on connecting to external databases.

Is this what you are looking for?

Help
SQL
yields:

SQL.REQUEST
See Also

Connects with an external data source, and runs a query from a worksheet. SQL.REQUEST then returns the result as an array without the need for macro programming. If this function is not available, you must install the Microsoft Excel ODBC add-in (XLODBC.XLA).

 

[Nikita6003]

VBIJedi,

thanks for the vote of confidence ;-)

Yup, I can set you up with a few connection examples & data manipulation (tho'we like to call the latter "reporting"...) using both ADO and DAO.

There's quite a lot to say about this subject - check some of the recent threads I've replied to through 'my threads'

I'll come back to you later - need to write down what I want to say in peace & quiet

Meanwhile - if you want an example using ADO (using a Sybase driver but I can let you have the SQL/Oracle version if you like as well - only differce = the Connection string), let me know on Nikita6003@Hotmail.com

Cheers
Nikki (from a very cold & soggy Netherlands)

 

[Nikita6003]

ADO: how to connect to a SQL, Access or Sybase database & how to manipulate objects

DISCLAIMER: this doesn’t pretend to be a complete “how-to” on ADO; it’s meant to give a few pointers in the right direction.
It’s also not the only way to do things, perhaps not even the best way but it’s worked for me in the past ;-)
Finally: is it better than DAO? Dunno. I’m still using both methods, and depending on my/my users’ needs, I choose either one or the other.
Hopefully the examples are of some use to some of you …

Before you start: make sure you add a reference to ADO and ADO Extensions to your project! This adds the ADODB and ADOX object models which you need to manipulate your database ;-)

ADO Connections
With ADO you use a Connection object to gain access to your databases using so-called OLE DB providers. There are 2 types of Providers:
1. Data providers allow you to link up to any database created in the application that the data provider was written for – i.e. they’re written to “talk” to Access, SQL Server, Oracle, Sybase, etc. The functionality of each provider depends mostly on the RMDBS. What works with one mightn’t work with another
This is a list based on the Providers that MS supplies in MDAC 2.6 (as nicked from Microsoft):

Area            	                  Topic
ODBC databases	                       Microsoft OLE DB Provider for ODBC
Microsoft Indexing Service	       Microsoft OLE DB Provider for Microsoft Indexing Service
Microsoft Active Directory Service	Microsoft OLE DB Provider for Microsoft Active Directory Service
Microsoft Jet databases	OLE DB          Provider for Microsoft Jet
Microsoft SQL Server	                  Microsoft OLE DB Provider for SQL Server
Oracle databases	                  Microsoft OLE DB Provider for Oracle
Internet Publishing	                  Microsoft OLE DB Provider for Internet Publishing
Simple data sources	                  Microsoft OLE DB Simple Provider

2. Service providers add extra functionality to the standard functionality of your data provider – this is MS-ese to say “whatever your RMDBS can’t do we’ve done for you”. I haven’t used these very much, so can’t say much about them

A connection to a database is made by creating a new ADODB.Connection object in your code (sounds harder than it is:

Dim l_cnConnection As New ADODB.Connection

will do this for you), and then setting the connection string. The connection string is different for each Provider you want to access. Below you’ll find some examples (all examples assume a Connection Object which I’ve defined on a modular level – i.e. this Connection object can be used throughout all Subs and Functions that are added to this module. As you can’t use NEW on a modular level, so the declaration’s a bit different:

Private m_cnConnection As ADODB.Connection

.):

Connection to an Access database using the Microsoft.Jet.Ole.DB.4.0 provider (similar functionality to the DAO Jet workspace)

    l_sConnect = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\My Documents\MyAccessDB.mdb;" & _
                "User ID=admin;" & _
                ";Password=" 
    
    'Set connection
    Set m_cnConnection = New ADODB.Connection
    With m_cnConnection
        .ConnectionString = l_sConnect
        .Open
        .CursorLocation = adUseClient
    End With

SQL Server connection string:

    'Goes straight to SQL Server; use Servername / DBname / Valid User ID / valid Password combination to connect
    l_sConnect = "PROVIDER=SQLOLEDB;" & _
                "Server=srv;” & _
                "Database=pubs;" & _
                "UID=sa;" & _
                ";PWD=pwd;"
    
    'Set connection
    Set m_cnConnection = New ADODB.Connection
    With m_cnConnection
        .ConnectionString = l_sConnect
        .Open
        .CursorLocation = adUseClient
    End With

Sybase connection string – I tend to use DSNs to connect to our Sybase DB – makes for easier code maintenance. All I need to do to adapt our Excel tools to a new environment = set another DSN

:
    l_sConnect = "PROVIDER=MSDASQL;" & _
                "DSN=UATEnv" & p_sDSN & ";" & _
                "UID=test;" & _
                ";PWD=testing;IS=SET ANSINULL OFF;"
    
    'Set connection
    Set m_cnConnection = New ADODB.Connection
    With m_cnConnection
        .ConnectionString = l_sConnect
        .Open
        .CursorLocation = adUseClient
    End With

We added the

SET ANSINULL OFF

after a mishap where one developer used

 = Null

and another stuck to

 Is Null

. Caused us a few headaches to find out why Nulls weren’t being returned properly!

For other providers please check this link (very useful list of all connection strings you could possibly need):

http://www.able-consulting.com/ADO_Conn.htm

ADO recordsets.
Once the connection has been established, there are 2 ways of querying data in your database: by using stored procedures (queries in Access language) or by having your code pass the db a bit of SQL. Code example of both (the examples assume a recordset which has been declared on the modular level (

Public m_rsRecordset as ADODB.Recordset

):
Executing a stored procedure/query

Public Function ExecStoredProcedure(p_sProcedureName As String, p_sParameterString As String) As Boolean
    Dim l_cmdTmp As New ADODB.Command
    Dim l_sCMDString As String
    Dim l_rsTmp As New ADODB.Recordset
    
    ExecStoredProcedure= False

    On Error GoTo ErrorHandler

    'Clear recordset so we pick up only latest stored proc result
    Set m_rsRecordset = Nothing
    l_sCMDString = "exec " & p_sProcedureName & " " & p_sParameterString
    With l_cmdTmp
        .ActiveConnection = m_cnConnection
        .CommandText = l_sCMDString
        .CommandType = adCmdText
        .CommandTimeout = 0
        Set l_rsTmp = .Execute
    End With
    
    'Set recordset to modular variable
    Set m_rsRecordset = l_rsTmp

    ExecStoredProcedure= True
    Exit Function
ErrorHandler:
    MsgBox Err.Description & “ (“ & Err.Number& “)”
    
    Exit Function
    Resume 
End Function

Call this function as follows:
If Not ExecStoredProcedure("qryLargeCreditClients", "&quot Then Err.Raise 99, , "ERROR: could not run query qryLargeCreditClients"
Use the parameter string to add any parameters your stored procedure might have. The example query qryLargeCreditClients has no parameters, so an empty string is passed.

Executing a bit of SQL

Public Function RunSQL(p_sSQL As String) As Boolean
    Dim l_cmdTmp As New ADODB.Command
    Dim l_rsTmp As New ADODB.Recordset
    
    On Error GoTo ErrorHandler
    
    'Clear recordset so we pick up only latest stored proc result
    Set m_rsRecordset = Nothing
    
    'Run SQL
    With l_cmdTmp
        .ActiveConnection = m_cnConnection
        .CommandText = p_sSQL
        .CommandType = adCmdText
        .CommandTimeout = 0
        Set l_rsTmp = .Execute
    End With
    
    'Set recordset to modular variable
    Set m_rsRecordset = l_rsTmp
    
    Exit Function
ErrorHandler:
    MsgBox Err.Description & “ (“ & Err.Number& “)”
    
    Exit Function
    Resume
End Function

Call this function as follows:
If Not RunSQL("SELECT * From tblClientOne WHERE ClientCredit > 50&quot Then Err.Raise 99, , "ERROR: could not run SQL"

Table and Index objects:
Finally, here’s an exmaple that’ll add a new table + Primary Key + Index to your database:

    Dim l_tblTable As New ADOX.Table
    Dim l_catCatalog As New ADOX.Catalog
    Dim l_idxIndexPrimary As New ADOX.Index
    Dim l_idxIndex As New ADOX.Index
    
    On Error GoTo ErrorHandler
    
    'Set catalog - this object = the container for tables, queries, indices amd other database objects
    l_catCatalog.ActiveConnection = m_cnConnection
        
    With l_tblTable
        'Name the table
        .Name = "tblClient"
        
        'Add fields
        .Columns.Append "ClientID", adInteger
        .Columns.Append "ClientName", adVarWChar, 30
        .Columns.Append "ClientAddress", adVarWChar, 40
        .Columns.Append "ClientTown", adVarWChar, 20
        .Columns.Append "ClientCredit", adCurrency
        .Columns.Append "ClientAddDate", adDate
        
        'Add index - e.g. primary key
        l_idxIndexPrimary.Name = "xPrimaryKey"
        l_idxIndexPrimary.Columns.Append "ClientID"
        l_idxIndexPrimary.PrimaryKey = True
        l_idxIndexPrimary.Unique = True
        .Indexes.Append l_idxIndexPrimary
        
        'Add ordinary 1-field index
        .Indexes.Append "xName", "ClientName"
    End With
    
    'Add the new table to the catalog by using the APPEND method
    l_catCatalog.Tables.Append l_tblTable

I’ll follow-up with an example of using DAO to access your database – later, dude ;-)

Meanwhile, hope this helps as a first step

Cheers
Nikki

 

[xenofoob]

Tnx needed some pieces of this code

My extremly simple login method:

Option Compare Database

Private Sub Knop4_Click()
On Error GoTo Err_Knop4_Click
    Dim strSQL As String
    Dim l_cmdTmp As New ADODB.Command
    Dim l_rsTmp As ADODB.Recordset

    l_sConnect = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=D:\School\Heinz Bier\access\Bier final 1.0.mdb;" & _
                "User ID=admin;" & _
                ";Password="
    
    'Set connection
    Set m_cnConnection = New ADODB.Connection
    With m_cnConnection
        .ConnectionString = l_sConnect
        .Open
        .CursorLocation = adUseClient
    End With
    
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = CurrentProject.Connection
     
    strSQL = "SELECT * " & _
             "FROM Users " & _
             "WHERE (([username]='" & Me.username.Value & "') AND ([password] = '" & Me.password.Value & "'))"
                 
    'Run SQL
    With l_cmdTmp
        .ActiveConnection = m_cnConnection
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 0
        Set l_rsTmp = .Execute
    End With
    
    'Set recordset to modular variable
    Set m_rsRecordset = l_rsTmp
   If l_rsTmp.RecordCount = 1 Then
        MsgBox "Access granted!!!", vbInformation, "Heinz Bierservice"
   Else
        MsgBox "Access denied!!!", vbCritical, "Heinz Bierservice"
   End If

Exit_Knop4_Click:
    Exit Sub

Err_Knop4_Click:
    MsgBox Err.Description
    Resume Exit_Knop4_Click
    
End Sub