By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,738 Members | 1,682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,738 IT Pros & Developers. It's quick & easy.

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

P: n/a
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=MyPathDirectory;" & _
"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:=adoConn

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

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"Nono" <no**********@yahoo.fr> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.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=MyPathDirectory;" & _
"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:=adoConn

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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.