How To Transfer Data to ADO Data Source from Excel with ADO

How To Transfer Data to ADO Data Source from Excel with ADO

• Chris Woods

  Yes it is. ADO is a COM object that can be used in any Office application to update a Database. There a bit to it and don't have much time right now so many this post might continue on for a while.

  The first you want to do is create a reference to ADO in Excel.

  Tools->References->Microsoft ActiveX Data Objects Library.

  This will import the ADODB namespace that contains all the objects you need to access a database. There are three main objects, Connection, Command and Recordset. The Connection creates the connection to the database, you'll need a connection string for accessing your database (www.connectionstrings.com). The Command object is for running SQL commands against a database, for example an INSERT or UPDATE SQL command. The Recordset is similar to the Command in that it lets you run SQL commands but it stores the results giving you more programming ability on the data in the database (filters for example).

  Here's an example

  'create an adoRecordset and adoCommand object to conduct a query
  Dim adoRecordset As ADODB.Recordset
  Dim adoCommand As New ADODB.Command
  adoCommand.ActiveConnection = CurrentProject.Connection
  adoCommand.CommandType = adCmdText
  'count all the clamps for the current PVC Number
  adoCommand.CommandText = "SELECT COUNT(*) AS [Count] FROM [tblTable] WHERE [Field] = '" & Value & "'"
  Set adoRecordset = adoCommand.Execute
  'if there are no items then
  If adoRecordset![ClampCount] = 0 Then
  MsgBox "No Records"
  Else
  MsgBox "Records"
  End If
  'tidy up
  adoRecordset.Close
  Set adoRecordset = Nothing
  Set adoCommand = Nothing

  This will get you started
  

  
  
  
• How To Transfer Data to ADO Data Source from Excel with ADO