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

Using Excel VBA to Update/Modify Access records

P: n/a
Hello,
I have an Access Database that I want to update using an Excel
spreadsheet. When it is new reccords, I know how to do it.
Nevertheless when I want to complete the information on a certain row
of records which already exist, or if I would like to update it (ie:
partially change certain records on a row), I do not know how to :
identify the record,
proceed to the update.
Can anybody help me out on that one??
Thanks
Arnaud

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Are you using ADO or DAO to connect to the Access db?

You can use one of 2 methods: 1) use an SQL statement that has the
criteria in the WHERE clause; 2) use a Recordset's FindFirst method.

1) When you open a recordset you can use an SQL statement to identify
the record you want to work on. E.g., work on account id 2235:

SELECT * FROM Accounts WHERE AccountID = 2235

Then use the Recordset's .Edit and .Update (DAO only) methods to change
the record's fields.

2) When you've opened the table with a Recordset use the Recordset's
..FindFirst method. E.g. Find account 2235 and change the Amount to
$200.00:

Dim rs As DAO.Recordset

Set rs = ... etc. ...

rs.FindFirst "AccountID=2235" ' DAO only
' rs.Find "AccountID=2235" 'ADO .Find method
If Not rs.NoMatch Then
rs.Edit ' DAO only. ADO doesn't use Edit
rs!Amount = 200
rs.Update
Else
MsgBox "Record Not Found"
End If

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkG6BIechKqOuFEgEQIWCgCffZy/7etZKmTDQeKqcVFnkvgq+zkAoKyz
V43S4sG6GlxpeFQCBJqhtgpY
=WIaa
-----END PGP SIGNATURE-----
Nono wrote:
Hello,

I have an Access Database that I want to update using an Excel
spreadsheet. When it is new reccords, I know how to do it.

Nevertheless when I want to complete the information on a certain row
of records which already exist, or if I would like to update it (ie:
partially change certain records on a row), I do not know how to :
identify the record,
proceed to the update.

Nov 13 '05 #2

P: n/a
Hi MG,
Thanks you very much for your answer. I am using ADO 2.5.
I am a starter in using SQL statement within VBA, for the moment I am
using the VBA macro recorder in order to help me out with the VBA
threads.
when accessing the database, I have the following code. Where should I
insert the code which indicate the VBA to select and change my database
record?

Sub Macro1()
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\DOCUMENTS AND
SETTINGS\ARNAUD\Desktop\TEST.mdb;DefaultDir=C:\DOC UMENTS AND
SETTINGS\ARNAUD\Deskto" _
), Array( _
"p;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin; ")), _
Destination:=Range("D11"))
.CommandText = Array( _
"SELECT Table1.ID, Table1.TES, Table1.T" & Chr(13) & "" &
Chr(10) & "FROM `C:\DOCUMENTS AND SETTINGS\ARNAUD\Desktop\TEST`.Table1
Table1" & Chr(13) & "" & Chr(10) & "ORDER BY Table1.ID" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
Thanks

Arnaud

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.