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

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

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

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") & "'})"
& _
Set adoConn = New ADODB.Connection
adoConn.Open sConn

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

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

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.