Microsoft SQL Server forum - UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO

Author

UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO - SQL

Nono

2005-06-15, 11:24 am

Hello,
I am trying to update records in my database from excel data using vba
editor within excel.
In order to launch a query, I use SQL langage in ADO as follwing:
------------------------------------------------------------
Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim sConn As String
Dim sSql As String
Dim sOutput As String

sConn = "DSN=MS Access Database;" & _
"DBQ=MyDatabasePath;" & _
" DefaultDir=MyPathDir
ectory;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize
=2048;PageTimeout=5;
" &
_
" PWD=xxxxxx;UID=admin
;"

ID, A, B C.. are my table fields
sSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,
`K`, L" & _
" FROM MyTblName" & _
" WHERE (`A`='MyA')" & _
" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"
& _
" ORDER BY `C` DESC"
Set adoConn = New ADODB.Connection
adoConn.Open sConn

Set adoRs = New ADODB.Recordset
adoRs.Open Source:=sSql, _
ActiveConnection:=ad
oConn

adoRs.MoveFirst
Sheets("Sheet1").Range("a2").CopyFromRecordset adoRs
Set adoRs = Nothing
Set adoConn = Nothing

---------------------------------------------------------------
Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statements
in this environement? Copying SQL statements from access does not work
as I would have to reference Access Object in my project which I do not
want if I can avoid. Ideally I would like to use only ADO system and
SQL approach.

Thank you very much

Nono

Simon Hayes

2005-06-15, 1:24 pm

"Nono" <nono_vba_xbz@yahoo.fr> wrote in message
news:1118847608.154396.147790@f14g2000cwb.googlegroups.com...
> Hello,
> I am trying to update records in my database from excel data using vba
> editor within excel.
> In order to launch a query, I use SQL langage in ADO as follwing:
> ------------------------------------------------------------
> Dim adoConn As ADODB.Connection
> Dim adoRs As ADODB.Recordset
> Dim sConn As String
> Dim sSql As String
> Dim sOutput As String
>
> sConn = "DSN=MS Access Database;" & _
> "DBQ=MyDatabasePath;" & _
> " DefaultDir=MyPathDir
ectory;" & _
> "DriverId=25;FIL=MS Access;MaxBufferSize
=2048;PageTimeout=5;
" &
> _
> " PWD=xxxxxx;UID=admin
;"
>
> ID, A, B C.. are my table fields
> sSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,
> `K`, L" & _
> " FROM MyTblName" & _
> " WHERE (`A`='MyA')" & _
> " AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"
> & _
> " ORDER BY `C` DESC"
> Set adoConn = New ADODB.Connection
> adoConn.Open sConn
>
> Set adoRs = New ADODB.Recordset
> adoRs.Open Source:=sSql, _
> ActiveConnection:=ad
oConn
>
> adoRs.MoveFirst
> Sheets("Sheet1").Range("a2").CopyFromRecordset adoRs
> Set adoRs = Nothing
> Set adoConn = Nothing
>
> ---------------------------------------------------------------
> Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statements
> in this environement? Copying SQL statements from access does not work
> as I would have to reference Access Object in my project which I do not
> want if I can avoid. Ideally I would like to use only ADO system and
> SQL approach.
>
> Thank you very much
>
>
> Nono
>

This seems to be an Access question, not an MSSQL one, so you'll probably
get a better response in an Access or ADO group. But if you can build up a
SELECT statement dynamically, then you should be able to build an UPDATE
also - it's not really clear from your post why this isn't working for you.

Simon

Sponsored Links