By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,471 Members | 722 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,471 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

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.