Programming to go between Access and Excel

  • Thread starter Thread starter Shin
  • Start date Start date
S

Shin

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!
 
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".
Click to expand...

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
 
Thanks so much for the information. When you wrote
Tools/ References must be checked, is that in Excel? Is
it under Tools? Thanks
 
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
 
Back
Top