Re: 'UPDATE' sql statement

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

From: Fred (leavemealone_at_home)
Date: 10/27/04 

Date: Wed, 27 Oct 2004 18:43:42 +1300

Thanks Tim but the ADODB.Recordset object is not recognised and I get an
error when I compile the project "User-defined type not defined.
I did add the reference to "Microsoft ADO Ext 2.7 for DDL and security". I
also tried adding "Microsoft DAO 3.51" but ADODB.Recordset is not
recognised.
I am running Office '97. Do I need a later version?

Fred

"Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
news:eb$pQF%23uEHA.2624@TK2MSFTNGP11.phx.gbl...
> I'm not sure the built-in query table functionality will handle
> updates.
>
> Add a reference to the ADO 2.x library in the VBE and try this
> (untested - you may have to fix the connection string a bit)
>
> Tim.
>
>
> Sub Tester()
> Dim sSQL
>
> sSQL = "UPDATE root.CLNDR CLNDR SET " & _
> "CLNDR.YN='Y' WHERE CLNDR.DATE_=20041001"
>
> MsgBox RunUpdate(sSQL)
> End Sub
>
>
>
> Function RunUpdate(sSQL As String) As Long
>
> Dim lngRecs As Long
> Dim oConn As ADODB.Recordset
>
> Set oConn = CreateObject("ADODB.Recordset")
> oConn.Open "ODBC;DSN=TIMS.udd;"
> oConn.Execute sSQL, lngRecs, adCmdText
> oConn.Close
> Set oConn = Nothing
>
> RunUpdate = lngRecs
>
> End Function
>
>
>
> "Fred" <leavemealone@home> wrote in message
> news:OnMtEi9uEHA.1400@TK2MSFTNGP11.phx.gbl...
> >I have an ODBC driver to access a database but I can't figure out how
> >to run
> > an UPDATE statement from Excel VBA.
> > I can create a query to return data and below is the recorded macro
> > which
> > works fine and so shows that I can connect to the database OK.
> > So, how would I change this to run a simple UPDATE statement like
> > "UPDATE root.CLNDR CLNDR SET CLNDR.YN='Y'
> > WHERE CLNDR.DATE_=20041001;"
> >
> > Sub Macro1()
> > With
> > Active***.QueryTables.Add(Connection:="ODBC;DSN=TIMS.udd;", _
> > Destination:=Range("A1"))
> > .Sql = Array("SELECT CLNDR.DATE_, CLNDR.DAY, " & _
> > "CLNDR.NUMA, CLNDR.NUMB, CLNDR.YN" & vbCrLf & _
> > "FROM root.CLNDR CLNDR")
> > .FieldNames = True
> > .RefreshStyle = xlInsertDeleteCells
> > .RowNumbers = False
> > .FillAdjacentFormulas = False
> > .RefreshOnFileOpen = False
> > .HasAutoFormat = True
> > .BackgroundQuery = True
> > .TablesOnlyFromHTML = True
> > .Refresh BackgroundQuery:=False
> > .SavePassword = True
> > .SaveData = True
> > End With
> > End Sub
> >
> > Thanks
> > Fred
> >
> >
>
>

Quantcast