Programming to go between Access and Excel
 

Is it possible to write code (in Excel's Visual Basic)
that accesses an Access database and deletes all current
records? I want all of this to happen when the user hits
a button that says "update".

What I am trying to accomplish is to download the
existing data out of Access, edit the data in Excel,
remove the old records in Access and import the updated
records from Excel. The only part I can't figure out is
how to delete the existing records in Access so that I
don't have duplicate enrties when I upload the refrshed
data from Excel.

Can anyone help? I'd be so grateful!

Programming to go between Access and Excel
 

Is it possible to write code (in Excel's Visual Basic)
that accesses an Access database and deletes all current
records? I want all of this to happen when the user hits
a button that says "update".

Sub macro1()
' Microsoft DAO x.x Object Library at menu
' Tools | References must be checked
Dim rs As Recordset
Dim db As Database

Set db = OpenDatabase("c:\path\filename.mdb")
Set rs = db.OpenRecordset("Table1")
Do
rs.Delete
rs.MoveNext
Loop Until rs.EOF
Set rs = Nothing
Set db = Nothing
End Sub

HTH,
Merjet

Programming to go between Access and Excel
 

Thanks so much for the information. When you wrote
Tools/ References must be checked, is that in Excel? Is
it under Tools? Thanks

Programming to go between Access and Excel
 

Shin,

In the VBA editor, go to the Tools menu, choose References, and in that
dialog check the appropriate item in the list box.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Shin" wrote in message
...
Thanks so much for the information. When you wrote
Tools/ References must be checked, is that in Excel? Is
it under Tools? Thanks