Solved

Creating my first DB connection in VBA, Excel 2007

Posted on 2011-09-07
7
262 Views
Last Modified: 2012-05-12
If this code throws no error, does that mean it's opening a connection? If so, to what?

It stops nicely at a breakpoint and exits the function gracefully.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "combo change to WS2"
    Dim Myconnection As Connection
    Dim Myrecordset As Recordset
    Dim MyWorkbook As String

    Set Myconnection = New Connection
    Set Myrecordset = New Recordset
 
    'Identify the workbook you are referencing
    MyWorkbook = Application.ThisWorkbook.FullName

    'Open connection to the workbook
    Myconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & MyWorkbook & ";" & _
                  "Extended Properties=Excel 8.0;" & _
                  "Persist Security Info=False"



End Sub
0
Comment
Question by:newbieweb
  • 4
  • 3
7 Comments
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 500 total points
Comment Utility
It's opening a connection to the workbook in which the code is contained. But it doesn't do anything with the connection.

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
Comment Utility
You might find the following useful.

The following text and sample code illustrates how to:

   -> open a database connection
   -> use a stored procedure to perform a query
   -> execute any SQL command against a database
   -> open a recordset using a custom query
   -> open a recordset using a table name
   -> check for an empty recordset
   -> read all records in a recordset
   -> add a record to a recordset
   -> delete a record from a recordset
   -> copy a recordset with headers to a worksheet
   -> close a recordset and database

This sample code, except for Open database method, can be used with any database such as Access, SQL Server, or Oracle. When using a database, most interaction happens via a recordset. Data is manipulated almost entirely using Recordset objects. Any number of Recordset objects can be created and used at the same time - each representing a different query or the same query. Different Recordset objects can access the same tables, queries, and fields without conflicting.

After opening a Recordset the Recordset can contain zero or more records. One record in the Recordset is always the current record except when the Recordset BOF or EOF property is true in which case no record is the current record. The current record is the record that is affected by any record-specific methods. To move amongst the records in a Recordset use the MoveNext, MovePrevious, MoveLast, and MoveFirst Recordset methods. A specific record can be made the current record by setting the AbsolutePosition property to the index number of the desired record. Fields in the current record are access as illustrated below.

   Value = MyRecordset!Field1
   MyRecordset!Field2 = Value + 1

When the current record is changed use the Update method to apply the changes to the database. Use the Add method to add a new record and the Delete method to delete the current record. The Add method can be used even if the query returns an empty recordset.

Caution about using the RecordCount method: The RecordCount for a serverside recordset may return a -1. This occurs with ActiveX Data Objects (ADO) version 2.0 or later when the CursorType is adOpenForwardonly or adOpenDynamic and when with ADO 1.5 only when the cursortype is adOpenForwardonly. To get around this problem use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors or use a client side cursor. Client side cursors use only adOpenStatic for CursorTypes regardless of which CursorType is selected.

Before writing any ADODB code the data objects library "Microsoft ActiveX Data Objects x.x Library" must be referenced in the VBA project (Tools->References).

For additional information on the ADODB interface see the MSDN pages at:

   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoapireference.asp

Sample code:

   Dim MyDatabase As ADODB.Connection
   Dim MyCommand As ADODB.Command
   Dim MyRecordset As ADODB.RecordSet
   Dim Column As Long

   ' Open database connection
   Set MyDatabase = New ADODB.Connection
   MyDatabase.CursorLocation = adUseClient
   MyDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\full\path\to\database.mdb'; User Id=admin; Password=;"
   ' For more information about Open syntax:
   '   http://msdn.microsoft.com/en-us/library/ms808201.aspx
   ' Additional help constructing connection strings can be found at http://www.connectionstrings.com/

   ' Query database using stored procedure (requires command object)
   Set MyCommand = New ADODB.Command
   Set MyCommand.ActiveConnection = MyDatabase
   MyCommand.CommandText = "qrySomeQuery" ' <- name of procedure
   MyCommand.CommandType = adCmdStoredProc
   With MyCommand
      .Parameters.Refresh
      .Parameters.Append .CreateParameter("QueryTextParam", adVarChar, adParamInput, 10, StringValue)
      .Parameters.Append .CreateParameter("QueryTextParam", adDouble, adParamInput, , DoubleValue)
      .Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LongValue)
      .Parameters.Append .CreateParameter("QueryDateParam", adDate, adParamInput, , DateValue)
      .Parameters.Append .CreateParameter("QueryDateTimeStampParam", adDBTimeStamp, adParamInput, , DateTimeValue)
      .Parameters.Append .CreateParameter("BooleanParam", adBoolean, adParamInput, , BooleanValue)
      ' For more information about CreateParameter syntax:
      '   http://msdn.microsoft.com/en-us/library/ms808298.aspx
   End With
   
   ' Open recordset using command object
   Set MyRecordset = New ADODB.Recordset
   MyRecordset.Open MyCommand, , adOpenDynamic, adLockPessimistic
   ' For more information about Open method syntax:
   '   http://msdn.microsoft.com/en-us/library/ms808656.aspx
   
   ' Build a custom query using command object
   Set MyCommand = New ADODB.Command
   With MyCommand
      Set .ActiveConnection = MyDatabase
      .CommandType = adCmdText
      .CommandText = "SELECT * From tblMyTable WHERE (tblMyTable.MyID = 1)"
   End With
   MyRecordSet.Open MyCommand, , adOpenDynamic, adLockReadOnly

   ' Execute any SQL statement
   MyDatabase.Execute "INSERT INTO TableName (Field1, Field2) VALUES ('" & Range("A1").Value & "','" & Range("A2").Value & "')"

   ' Open a recordset by specifying specific table (no query)
   MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
   
   ' Open a recordset by specifying query without using command object
   MyRecordset.Open "SELECT * FROM MyTable", MyDatabase, adOpenDynamic, adLockPessimistic

   ' Test for no records
   If MyRecordset.BOF And MyRecordset.EOF Then
      MsgBox "No records in table"
   End If

   ' Determine total records (see notes above about inconsistencies with the RecordCount method)
   MsgBox "Total records: " & MyRecordset.RecordCount
   
   ' Look at all records in record set
   While Not MyRecordset.EOF
      MsgBox "Record number: " & MyRecordset.AbsolutePosition
      MyRecordset.MoveNext
   Wend

   ' Find a specific record given a field value
   MyRecordset.MoveFirst
   MyRecordset.Find "ID='ABC123'"
   If Not MyRecordset.BOF And Not MyRecordset.EOF Then
      MyRecordset!Field1 = "Match Found"
      MyRecordset.Update
   End If

   ' Copy the entire recordset to a worksheet (this technique does not copy field names)
   Sheets("Sheet1").[A2].CopyFromRecordset MyRecordset
   
   ' Create headers and copy data
   With Sheets("Sheet1")
      For Column = 0 To MyRecordset.Fields.Count - 1
         .Cells(1, Column + 1).Value = MyRecordset.Fields(Column).Name
      Next
      .Range(.Cells(1, 1), .Cells(1, MyRecordset.Fields.Count)).Font.Bold = True
     .Cells(2, 1).CopyFromRecordset MyRecordset
   End With

   ' Update current record
   MyRecordset!Field1 = "Some data"
   MyRecordset!Field2 = "Some more data"
   MyRecordset.Update

   ' Move specific fields from current record to worksheet
   With Sheets("Sheet1")
      Cells(Row, "A") = MyRecordset!Field1
      Cells(Row, "B") = MyRecordset!Field2
   End With
   
   ' Add new record and set field values
   MyRecordset.AddNew
   MyRecordset!Field1 = "Some data"
   MyRecordset.Update

   ' Update an existing record or add it if it does not exist
   MyRecordset.MoveFirst
   MyRecordset.Find "Field1='"& SourceSheet.Cells(Row, "A") & "'"
   If MyRecordset.BOF Or MyRecordset.EOF Then
      MyRecordset.AddNew
   End If
   MyRecordset!Field1 = SourceSheet.Cells(Row, "A")
   MyRecordset!Field2 = SourceSheet.Cells(Row, "B")
   MyRecordset.Update
   
   ' Delete current record
   MyRecordset.Delete

   ' Close recordset
   MyRecordset.Close

   ' Close database
   MyDatabase.Close

Kevin
0
 

Author Comment

by:newbieweb
Comment Utility
Thanks for the awesome code!

BUT, is there a way I can run a very simple query and NOT require a sproc? I am not too quick with T-SQL.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:newbieweb
Comment Utility
Is this table specific code?

Parameters.Append .CreateParameter("QueryTextParam", adVarChar, adParamInput, 10, StringValue)
      .Parameters.Append .CreateParameter("QueryTextParam", adDouble, adParamInput, , DoubleValue)
      .Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LongValue)
      .Parameters.Append .CreateParameter("QueryDateParam", adDate, adParamInput, , DateValue)
      .Parameters.Append .CreateParameter("QueryDateTimeStampParam", adDBTimeStamp, adParamInput, , DateTimeValue)
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 500 total points
Comment Utility
If you want to avoid SPs then you will need to build the query in your VB code. Something like:

   ' Build a custom query using command object
   Set MyCommand = New ADODB.Command
   With MyCommand
      Set .ActiveConnection = MyDatabase
      .CommandType = adCmdText
      .CommandText = "SELECT * From tblMyTable WHERE (tblMyTable.MyID = 1)"
   End With
   MyRecordSet.Open MyCommand, , adOpenDynamic, adLockReadOnly

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 500 total points
Comment Utility
Building a list of parameters is only useful when calling an SP that accepts parameters.

Kevin
0
 

Author Closing Comment

by:newbieweb
Comment Utility
Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now